Purging Very Large SQL Tables in NS
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.