3

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?

  • 2
    Even if you could, no, that is not wise. – Aaron Bertrand Aug 8 '13 at 18:44
2

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.

  • I'm going to give this a try, I'll let you know how it goes. – Chuck Herrington Aug 16 '13 at 18:58
  • @Chuck So how did it go? – Aaron Bertrand Feb 6 '17 at 19:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.