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.
No comments:
Post a Comment