How to Reduce SSISDB Size

How to Reduce SSISDB Size

        Problem: SSISDB is growing and it  reached size 94 GB and started causing package deadlock, and heating package performance .

       Solution: SSISDB is used to store the package log and configuration information, so whenever package will get deploy or execute it will store the version information and also the details of every execution of package. So if your package execution frequency is very high then this DB will grow very quickly.

   To clean the old log information from this database SQL server provides job called “SSIS Server Maintenance Job” to clean the SSISDB old records. Default schedule of this Job is to execute every night at 12 O’clock  once. This Job will call the procedure “EXEC SSISDB.[internal].[cleanup_server_retention_window]”  and this procedure will delete 10-10 records as batch and as per retention values that is specified in the “select * from [SSISDB].[catalog].[catalog_properties]” table. RETENTION value will define how much older data we have to keep in the SSISDB. Default values is 365 i.e.1 year.

Let’s back to the issue, if your packages is running very frequently and then this DB will get filled.  To overcome this issue we can do  below steps.

·         Step-1: Update the retention values from 365 to 3 days or whatever old history you want to keep.

Step-2 Alter procedure “SSISDB.[internal].[cleanup_server_retention_window]”  SET @delete_batch_size = 1000 instead of 10, in case if your DB size grown more than 10 GB.

·        Step-3: Run the below query to Compress the tables data.

USE [SSISDB]

ALTER TABLE [internal].[event_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

ALTER TABLE [internal].[operation_messages] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

ALTER TABLE [internal].[execution_component_phases] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

ALTER TABLE [internal].[execution_data_statistics] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

·         Step-4: Then using below queries Shrink the database if size is more than 10 GB.

USE [SSISDB]

GO

DBCC SHRINKDATABASE(N'SSISDB' )

GO

Stored Procedure Deferred Name Resolutions

Stored Procedure Deferred Name Resolutions 

 When you create stored procedures the code of the procedure is parsed to ensure that no syntax error exists. If the syntax of the command are correct then the text of the procedure will stored in syscomments  table in the database in which the procedure is being created. 

  The first time the SP is executed the SQL server query processor read the data from the syscomment table and checks whether all the objects referenced in the SP are contained in the database , this process is known as deferred name resolution. because of this deferred name resolution, any object referenced in the SP is not required at the creation time. The object must be there only when SP execute first time. This process allow user to drop and recreate table without having to recompile all the USP.

Drop and recreate all Foreign Key

Drop and recreate all Foreign Key 

         Truncate table will fail if table is referenced by child tables. So its big challenge for a developer when he want to reload table which has huge data. Lets take one example, we have a table with 10 millions of records and I have to reload this table,i.e. delete all the existing data and reload the data. If I perform the delete operation it may take more than 20 minutes as table is big it has 30 columns, and the second problem is fast growing log. The best way to clean the data is to truncate the table this will not take more than 2 or 3 sec. But table will not allow you to truncate as it is referenced by other table , even the child table don't have data it will throw error. 

  So I have written below query that will generate script for the table to drop all the foreign key constraint and recreate constraint. So I used to execute the first part that drop all the FK constraint and then truncate table, then run the create constraint script and then reload the table , its more better if you recreate constraint after data load.

 You can find this script on my MSDN contribution page.

Generate script to drop and recreate all referential constraints

http://gallery.technet.microsoft.com/scriptcenter/Generate-script-to-drop-edd9e9c1

Caution: Please generate create constraint SQL first and save and keep it safe then generate drop constraints.