Video Screencast Help
Endpoint Management Community Blog

Purging Very Large SQL Tables in NS

Created: 14 Jun 2010 • Updated: 03 Dec 2010
Ludovic Ferre's picture
0 0 Votes
Login to vote

In some cases the NS 6.0 SP3 purging procedure fails because the data tables to purge are much too big and purge never completes in the given DBTimeout setting (in CoreSettings.config, set to 600 seconds default).

You can see some hints on this in the AKB,  (articles #33443, #31988, #1068 and possibly more).

So, in order to reduce run-away database sizes for my customer I crafted the SQL query listed below.

But before you check in the query, please let me details the differences between this query and the SP3 purging procedure, as well as some potential issues with it.

  • We first purge the dataclass data table using this base statement: "set @PurgeQuery = N'delete from [' + @CurrentClassTable + N'] where [_eventTime] < @Time'"
  • Second we purge the dataclass data table using the defined MaxRowCount in the purging policy
  • On very large tables and large database the first section may actually never complete within the DbTimeout value because the query runs a sort on a non-index column.
  • The SQL query below only targets the data table row count value as this is often the most urgent problems to solve (to reduce table and DB size)
  • To do this as efficiently as possible we use the Identity column and the built in function IDENT_CURRENT() as the Max row and remove 1 million entries from it (or another value passed to the stored procedure), which is the main caveat with this proceeding: if for any reasons you have gaps in the identity column then the count of entries left in the data table could be lower than the desired count. However this is a minor risk and the overall cost versus benefit still edges in favour of this solution.
  • The query is built in 4 stages: Query I runs the backup for the desired table. Query II does the truncation. Query III insert the data back from the backup table. Query IV removes the backup table.
  • We check that each query runs successfully before committing the transaction. If anything goes wrong we do roll-back.
  • Output messages are written using print statement and list the procude SQL as well as commit/rollback messages

Finally, before you go into the SQL code itself, the usual word of warning: this script is meant to be run on datatables that are safe for truncation (i.e. NS + you and your customers can live without any data being present in the table). If you doubt whether the purge will have a positive impact on your production systems, if you suspect some potential negative impact or if you haven't found any clear information on the AKB regarding table you want to purge, don't do it!

You have been warned ^_^. So here is the SQL Code to create the stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE spPurgeDataTable_byname
	@table_name nvarchar(4000),
	@max_rows int = 1000000
AS
BEGIN
SET NOCOUNT ON;

	-- Return success if the row count is already below threshold
	if ( @max_rows > (select rows FROM sysindexes WHERE id = OBJECT_ID(@table_name) AND indid < 2))
		RETURN 0

	declare @tsql_i   as nvarchar(4000)
	declare @tsql_ii  as nvarchar(4000)
	declare @tsql_iii as nvarchar(4000)
	declare @tsql_iv  as nvarchar(4000)

	DECLARE @table_columns nvarchar(4000)

	set @tsql_i = 'select * into KEEP_' + @table_name + ' from ' + @table_name + ' where _id > IDENT_CURRENT(''' + @table_name + ''') - ' + cast(@max_rows as nvarchar)

	set @tsql_ii = 'truncate table ' + @table_name

	set @table_columns = '['

	SELECT @table_columns = COALESCE(@table_columns + '],[', '') +  sc.name
	from sys.objects so
	join sys.columns sc
	on sc.object_id = so.object_id
	where so.name = @table_name  and sc.name != '_id'

	set @table_columns = @table_columns + ']'
	set @table_columns = replace (@table_columns, '[],', '')

	set @tsql_iii = 'insert ' + @table_name + ' (' + @table_columns + ')' + '(select ' + @table_columns + ' from KEEP_' + @table_name + ')'

	set @tsql_iv = 'drop table KEEP_' + @table_name

	-- Wrap the operation within a transaction for safety
	BEGIN TRANSACTION

	declare @commit_tran as int
	set @commit_tran = 1

	print @tsql_i

	exec sp_executesql @tsql_i
	if @@error <> 0
	begin
		print 'Execution of Query I returned non-zero code! (' + cast (@@error as nvarchar(32))+ ')'
		set @commit_tran = 0
	end

	print @tsql_ii

	exec sp_executesql @tsql_ii
	if @@error <> 0
	begin
		print 'Execution of Query I returned non-zero code! (' + cast (@@error as nvarchar(32))+ ')'
		set @commit_tran = 0
	end

	print @tsql_iii

	exec sp_executesql @tsql_iii
	if @@error <> 0
	begin
		print 'Execution of Query I returned non-zero code! (' + cast (@@error as nvarchar(32))+ ')'
		set @commit_tran = 0
	end

	print @tsql_iv

	exec sp_executesql @tsql_iv
	if @@error <> 0
	begin
		print 'Execution of Query I returned non-zero code! (' + cast (@@error as nvarchar(32))+ ')'
		set @commit_tran = 0 
	end

	if @commit_tran = 0
	begin
		print 'Rolling back transaction!'
		ROLLBACK TRANSACTION
		return  -1
	end
	else
	begin
		print 'Commiting transaction'
		COMMIT TRANSACTION
	end
END
GO

And the SQL code to use the stored procedure (using event tables that are often growing large, plus a extra clean up procedure that can also help out):

exec spPurgeDataTable_byname 'Evt_NS_Client_Config_Generation', 1000000
exec spPurgeDataTable_byname 'Evt_NS_Client_Config_Request', 1000000
exec spPurgeDataTable_byname 'Evt_NS_Event_History', 1000000
exec spPurgeDataTable_byname 'Evt_Inventory_Rule_Summary', 1000000
exec spPurgeDataTable_byname 'Evt_AeX_SWD_Status', 1000000
exec spPurgeDataTable_byname 'Evt_NS_Client_Pkg_Info_Request', 1000000
exec spPurgeDataTable_byname 'Evt_NS_Item_Management', 1000000

exec SPPURGENSMESSAGEQUEUE @Status=N'Failed'

This SQL helped my customer reduce the size of 14 NS database from as much as 79.3 GiB down to 19.2 GiB (over 75% in reduction) in an extreme case.