Deployed package History From SSISDB

Querying SSISDB

 

How to know which version of my package deployed in  SSISDB?

                How to know when and who has deployed the package in my SSISDB? When you want to know the information about your deployed packages (in SSISDB) like what all version of my package has deployed in SSISDB, when it was deployed and who has deployed it, then you have to query the SSISDB.

                Below is the query that will give information about the packages deployed in the SSISDB catalog and also give the information when that folder was created and what all project and packages are deployed by which credential.

USE [SSISDB]
GO
/***** Script for SelectTopNRows command from SSMS ******/
SELECT fld.Name as FolderName

,fld.created_By_Name as folderCreatdBy

,fld.Created_time as folderCreateddate
,proj.name projectName
,proj.created_time
,proj.last_deployed_time
,proj.deployed_by_name
,proj.folder_id
,pkg.[project_version_lsn]
,pkg.[name] as Pakagename
,pkg.[description]
,pkg.[package_format_version]
,pkg.[version_major]
,pkg.[version_minor]
,pkg.[version_build]
,pkg.[version_comments]

FROM [SSISDB].[internal].folders fld

left outer join [SSISDB].[internal].[projects] proj on proj.folder_id=fld.folder_id

left outer join [SSISDB].[internal].[packages] pkg on pkg.project_id=pkg.project_id

 

 

 

 

 

 

 

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