SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Quick tip: Speeding up deletes from SSIS execution log

If you have SQL Server Integration Services installed on your server, and you left the default configurations a table named sysssislog is created on MSDB database, it contains logging entries for packages executed on that instance.

If you are not careful enough, this table can grow uncontrollably over time and can make subsequent insertions very slow.

A proper deletion process must be put in place, so you not get into situations like this one in your msdb database:



If you are already on this situation, you can the following T-SQL Script to delete records by batches:


DECLARE @date_del datetime,
@batch_size int = 1000, -- will delete on batches of 1000 records
@RowsAffected int =1

-- Time to keep in the history, in our case 1 month
SET @date_del= DATEADD(mm,-1,getdate());

SET NOCOUNT ON;

WHILE (@RowsAffected >0)
BEGIN
DELETE TOP(@batch_size)
FROM [dbo].[sysssislog]
WHERE starttime < @date_del;

SET @RowsAffected = @@ROWCOUNT;

-- If you want to know rows affected, uncomment this:
-- PRINT @RowsAffected;
END

SET NOCOUNT OFF;


After that you can implement the same query to your msdb maintenance job to have all in one place.

SQL Guatemala

SQL Server Database Administrator/Developer with experience in large environments. I have worked on IT for nearly 15 years in different areas. Microsoft Certified Solutions Associate: SQL 2016 Database Administration since 2018. Microsoft Certified Solutions Associate: SQL Server 2012/2014 since 2017. Microsoft Certified Professional since 2014.

Comments

Leave a comment on the original post [www.sqlguatemala.com, opens in a new window]

Loading comments...