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-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
Be careful when shrinking. If the database grows again (after being shrunk), you might as well be playing with disk I/O like it was a Yo-Yo :)
ReplyDeleteThank you Bill!!!!!!!!! That's True do not Shrink database always. But the case I try to introduce on above article is if DB size grown like 90 GB, in that case we can Shrink first time....
Deletemy SSIS job has been disabled since a few months ago by my developer,
ReplyDeleteand now i shocked with the SSIS DB growing to more than 200GB , and i finally decide to enable it, and change the periods to be 30, and i guess in your advice, step 2 is same as statement in default SSIS Server maintenance job, so is it actually same statement? if yes, am i need to do step 3 and 4 after my SSIS Server Maintenance Job running on this Sunday?
Thank You.
Update the retention values from 365 to 3 days or whatever old history you want to keep.
ReplyDeleteNo script for this step ?
EXEC [SSISDB].[catalog].[configure_catalog] @property_name=N'RETENTION_WINDOW', @property_value=180
ReplyDeleteGO