HiveBrain v1.2.0
Get Started
← Back to all entries
snippetMinor

How do I reclaim reserved space in the sysjobstepslogs table

Submitted by: @import:stackexchange-dba··
0
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?

Solution

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.

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.