Sql-server – How to reclaim reserved space in the sysjobstepslogs table

msdbsql-server-2008-r2

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:

ALTER TABLE dbo.sysjobstepslogs REBUILD;

If you're on Enterprise (or Evaluation or Developer) you can do:

ALTER TABLE dbo.sysjobstepslogs REBUILD WITH (ONLINE = ON);

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.