use sybsecurity
go

-----------------------------------------------------------
-- DROP Procedure: sp_thresholdaction
-----------------------------------------------------------
if exists (select * from sysobjects where type = 'P' and name = 'sp_thresholdaction')
begin
	print ' *** Drop   proc: sp_thresholdaction'
	drop procedure sp_thresholdaction
end
go


-----------------------------------------------------------
-- CREATE Procedure: sp_thresholdaction
-----------------------------------------------------------
print ' *** Create proc: sp_thresholdaction'
go
/*=====================================================================** 
** PROCEDURE: sp_thresholdaction
**---------------------------------------------------------------------** 
** Description: 
** 
** Last Chanche Threshold (on the transaction log)
**   - Log this to the errorlog
**   - Disable auditing
**   - truncate all 'audit tables'
**
** Any user defined thresholds
**   - Log this to the errorlog
**   - truncate all 'audit tables'
**
*/
create proc sp_thresholdaction
(
	@dbname        varchar(30),
	@segmentname   varchar(30),
	@space_left    int,
	@status        int             -- 1=LCT (Last Chance Threshold), 0=Other thresholds
) 
as
begin
	declare @msg varchar(512)

	/*
	** Check input
	*/
	if (@dbname != 'sybsecurity')
	begin
		print 'ERROR: This threshold action is only for database ''sybsecurity'', the current database is ''%1'' exiting.', @dbname
		return 0
	end

	/*
	** Last Chance Threshold (on the transaction log)
	*/
	if (@status = 1)
	begin
		-- DISABLE auditing
		set @msg = 'WARNING: DISABLE AUDITING by Last Chance Threshold on segment ''' + @segmentname + '''. NO MORE Auding... To enable auditing again do: exec sp_configure ''auditing'', 1'
		raiserror 99999 @msg

		exec sp_configure 'auditing', 0

		-- Truncate: the system table
		truncate table sybsecurity.dbo.sysaudits_01

--		-- Truncate: the Customer Specific "staging" table
--		truncate table sybsecurity.dbo.audit_trail
--
--		-- Truncate: SIP "staging" table (if it exists)
--		if exists (select * from sybsecurity.dbo.sysobjects where type = 'U' and name = 'sip_audit_trail')
--		begin
--			execute('truncate table sybsecurity.dbo.sip_audit_trail')
--			execute('update sybsecurity.dbo.sip_audit_collector_config set last_th_truncate_ts = getdate()')
--		end

		-- Make a note that we did truncate
		update sybsecurity.dbo.audit_collector_config 
		   set last_th_truncate_ts = getdate()

		-- Print information to Sybase errorlog
		dbcc printolog(@msg)

		-- TODO
		-- Add information to some log table, or similar
		-- sp_addauditrecord @msg, @dbname

		-- exit
		return 0
	end

	/*
	** Other threshold(s) on other segments, or similar
	** 
	*/
	-- Print some info messages
	set @msg = 'WARNING: Truncate AUDIT tables (audit data will be lost)... INFO: segment=''' + @segmentname + ''', space_left_pages=''' + convert(varchar(30), @space_left) + '''.'
	raiserror 99999 @msg

	-- Truncate: the system table
	truncate table sybsecurity.dbo.sysaudits_01

--	-- Truncate: the Customer Specific "staging" table
--	truncate table sybsecurity.dbo.audit_trail
--
--	-- Truncate: SIP "staging" table (if it exists)
--	if exists (select * from sybsecurity.dbo.sysobjects where type = 'U' and name = 'sip_audit_trail')
--	begin
--		execute('truncate table sybsecurity.dbo.sip_audit_trail')
--		execute('update sybsecurity.dbo.sip_audit_collector_config set last_th_truncate_ts = getdate()')
--	end

	-- Make a note that we did truncate
	update sybsecurity.dbo.audit_collector_config 
	   set last_th_truncate_ts = getdate()

	-- Print information to Sybase errorlog
	dbcc printolog(@msg)

	-- TODO
	-- Add information to some log table
	-- sp_addauditrecord @msg, @dbname

	-- exit
	return 0
end
go

-- Add 20 MB threshold to the 'logsegment' (where the transaction log is placed)
declare @threshold_in_pgs int  set @threshold_in_pgs = /** threshold in MB here --->>>*/ 20 /*<<<---*/ * (1024.0*1024.0/@@maxpagesize)
exec sp_addthreshold 'sybsecurity', 'logsegment', @threshold_in_pgs, 'sp_thresholdaction'
go

-- Add 40 MB threshold to the 'system' segment (where tables like 'sysaudits_01' are stored
declare @threshold_in_pgs int  set @threshold_in_pgs = /** threshold in MB here --->>>*/ 40 /*<<<---*/ * (1024.0*1024.0/@@maxpagesize)
exec sp_addthreshold 'sybsecurity', 'system', @threshold_in_pgs, 'sp_thresholdaction'
go

-- Add 60 MB threshold to the default segment (where tables like 'audit_trail' are stored
declare @threshold_in_pgs int  set @threshold_in_pgs = /** threshold in MB here --->>>*/ 60 /*<<<---*/ * (1024.0*1024.0/@@maxpagesize)
exec sp_addthreshold sybsecurity, 'default', @threshold_in_pgs, 'sp_thresholdaction'
go

-- List installed thresholds
sp_helpthreshold
go

-- to drop a threshold do: exec sp_dropthreshold dbnameName, segmentName, numOfPages
-- exec sp_dropthreshold 'sybsecurity', 'default', 10240
-- exec sp_dropthreshold 'sybsecurity', 'logsegment', 5120
