snippetMinor
How do I reclaim reserved space in the sysjobstepslogs table
Viewed 0 times
spacethereclaimsysjobstepslogshowreservedtable
Problem
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?
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?
Solution
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 (
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.Code Snippets
ALTER TABLE dbo.sysjobstepslogs REBUILD;ALTER TABLE dbo.sysjobstepslogs REBUILD WITH (ONLINE = ON);Context
StackExchange Database Administrators Q#47813, answer score: 2
Revisions (0)
No revisions yet.