I have a 36GB msdb database in my SQL Server 2008 R2 database server. From what I have read this is past the point of being gigantic. When I look at the tables in the msdb database the sysjobstepslogs table is using 97% of the space in the database. I've done enough research to find out that the cause was several jobs had steps that had the Log to table option set + long running transactions + snapshot isolation on the msdb database. This option has been disabled on those jobs.
I have tried to reduce its size by doing a truncate table, reindex, reorganize, check table, shrink file, shrink database (pretty much in that order) but with 0 rows, the sysjobstepslogs table still indicates that is has 35+GB reserved and used.
It has been suggested that I disable then reenable snapshot isolation to try to free up the space but according to this MSDN forum discussion
you cannot disable snapshot isolation on the msdb database.
I have contemplated dropping and recreating the table but how do I recreate the table as a system table and even if I did, is such a thing wise?
Any ideas how to reclaim this space?
Best Answer
You could try just rebuilding the table:
If you're on Enterprise (or Evaluation or Developer) you can do:
The table only has a single clustered index (
PK_SomeAwfulGUID-basedName
) and this rebuild operation should deallocate all of the pages that might still be associated with the table.