May 24, 2010 at 5:19 am
Hello, helpful people.
I'm looking for a safe, sane way of clearing down the sysdtslog90 table in the MSDB database, ideally one which won't impact performance too much or cause the transaction log to balloon up. Does anyone have any suggestions?
Thanks.
May 24, 2010 at 5:34 am
rikk (5/24/2010)
ideally one which won't impact performance too much or cause the transaction log to balloon up.
here you are talking about MSDB backup but i dont think this system DB can create any performance issue/space issue.
And why do you think that only this sys table is main culrprit ? i dont feel it can have so much of data, that can create lof space issue.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 24, 2010 at 5:36 am
Oh, it's definitely that table.
We set an SSIS package to run on this server a few years ago, and then basically ignored it. The MSDB database is now 45GB in size, 33 of which is the sysdtslog90 table.
May 24, 2010 at 5:56 am
my guess is simple Sql Agent restart can work here , but that a GUESS so be careful
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 24, 2010 at 6:25 am
Restarting the agent or the SQL Server service itself has no impact on the MSDB database. Its size is due to it containing a record of every backup and SSIS event run on the SQL Server instance. There's a stored procedure for clearing down the backup history (which I can't run for other reasons), but no such procedure for clearing down the sysdtslog90 table, which is the biggest problem. That's what I'm looking for; a way of clearing down this table which won't have any adverse effects.
May 24, 2010 at 10:27 am
Rikk,
If you are not concerned about loosing log history, see if you could truncate it one time and shrink the database. ( I am sorry I don't have this table on my database to try truncate )
If truncating is not an option, you can start deleting records in batches using some selection criteria with indexed column values, If you are using starttime/endtime like columns, make sure you create an index on the column you are going to use in your where clause before start deleting in batches. ( I really doubt that there will be existing index on the date columns. You are lucky if there is one existing already. )
As soon as you are done cleaning up this time, make sure you have simple purge job running to keep the tables size low. The table structure is pretty straightforward. So, just write a SQL Command to selectively delete old rows, based on the dates and/or other columns that is suitable for your environment.
-N
May 25, 2010 at 12:19 am
Neepa (5/24/2010)
you can start deleting records in batches using some selection criteria with indexed column values, If you are using starttime/endtime like columns, make sure you create an index on the column you are going to use in your where clause before start deleting in batches. ( I really doubt that there will be existing index on the date columns. You are lucky if there is one existing already. )
Are u sure that these sys tables or views can be truncated ot deleted ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply