September 18, 2014 at 7:25 am
Hi all
Help would be hugely appreciated as I'm all out of ideas on this one. I have a production server that's running our Sage SQL environment and I'm getting desperately low on space due to the sysmaintplan_logdetail table in the MSDB database. I've tried deleting the backup history, maintenance plan history and SQL server agent job history. I delete a week at a time due to the time it take, it makes the log file grow by 4GB every time, I then shrink the log file, get the 4GB back, but the DB file goes unchanged. The DB file is not at 70GB leaving me with about 11GB of free space, so getting scary. I've run a shrink on the DB file, that makes no difference. I'm pretty sure the issue is caused because the maintenance plan fails each week. When I look at the table in a report, it shows the following:
Table Name records reserved (kb) data (kb) Indexes (kb) Unused (kb)
dbo.sysmaintplan_logdetail 120 69,096,936 69,093,816 88 3,032
I think I need to manually purge out the table data, but I don't know how to do this, not sure why it's "reserved" 69GB either.
Can someone please help as I'm starting to get panicky about it.. thanks in advance
Mark
September 18, 2014 at 8:14 am
Hi
Not seeming to get any help with my post, I have found the following post http://johnsterrett.com/tag/sysmaintplan_logdetail/ that suggests I truncate the table.
Can anyone at least take a look and see if this is something I should try and if it's ok to do in office hours please? Again, help would be hugely appreciated.
Thanks
Mark
September 18, 2014 at 12:23 pm
First of all, deleting data does not release the space back to the OS or shrink the database. Shrinking a database is a manual process and is not recommended as it causes index fragmentation and the database will likely grow back to the same size again (maybe not in the case of well maintained msdb).
I haven't done the process that John Sterret outlines in his blog post, but if it worked for him I'm pretty confident it will work for you. I know John and he is very good at what he does. I'd be less inclined to do the truncate than I would be to continue the process you are doing by deleting a week at a time. The only thing I'd change is that I'd leave the TX Log at the size it grows to (I might even pre-grow it to that size) until I got the data sprawl under control, then you could shrink the data and log files down to the working size you need. Either process will work as long as you also add the msdb maintenance jobs to your server.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2014 at 6:09 am
Hi Jack
I'm down to 3 weeks remaining history so I can't really go any further, the issue is I'm not getting any space back, the DB is continuing to grow, regardless of a shrink operation or not. The issue is the 69GB reserved space that has nothing in it, just reserved. I don't know how else to claim it back...think you misread my post.
Thanks
September 22, 2014 at 7:01 am
I don't think I misread your post, I felt like I answered specifically what you asked. The closes thing to a question in your first post is this:
I think I need to manually purge out the table data, but I don't know how to do this
And your second post the question is clearly about what method to use to purge the data from the tables.
Not seeming to get any help with my post, I have found the following post http://johnsterrett.com/tag/sysmaintplan_logdetail/ that suggests I truncate the table.
Can anyone at least take a look and see if this is something I should try and if it's ok to do in office hours please? Again, help would be hugely appreciated.
No where, in either post is the question, why isn't the database file shrinking?
What are the messages returned when you run DBCC ShrinkFile?
Have you read the BOL article, http://msdn.microsoft.com/en-us/library/ms189493.aspx. It says:
...
Troubleshooting
This section describes how to diagnose and correct issues that can occur when running the DBCC SHRINKFILE command.
The File Does Not Shrink
If the shrink operation runs without error, but the file does not appear to have changed in size, verify that the file has adequate free space to remove by performing one of the following operations:
Run the following query.
SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;
Run the DBCC SQLPERF command to return the space used in the transaction log.
If insufficient free space is available, the shrink operation cannot reduce the file size any further.
Typically it is the log file that appears not to shrink. This is usually the result of a log file that has not been truncated. You can truncate the log by setting the database recovery model to SIMPLE, or by backing up the log and then running the DBCC SHRINKFILE operation again.
The Shrink Operation Is Blocked
It is possible for shrink operations to be blocked by a transaction that is running under a row versioning-based isolation level. For example, if a large delete operation running under a row versioning-based isolation level is in progress when a DBCC SHRINK DATABASE operation is executed, the shrink operation will wait for the delete operation to complete before shrinking the files. When this happens, DBCC SHRINKFILE and DBCC SHRINKDATABASE operations print out an informational message (5202 for SHRINKDATABASE and 5203 for SHRINKFILE) to the SQL Server error log every five minutes in the first hour and then every hour after that. For example, if the error log contains the following error message then the following error will occur:
DBCC SHRINKFILE for file ID 1 is waiting for the snapshot
transaction with timestamp 15 and other snapshot transactions linked to
timestamp 15 or with timestamps older than 109 to finish.
This means that the shrink operation is blocked by snapshot transactions that have timestamps older than 109, which is the last transaction that the shrink operation completed. It also indicates that the transaction_sequence_num, or first_snapshot_sequence_num columns in the sys.dm_tran_active_snapshot_database_transactions dynamic management view contains a value of 15. If either the transaction_sequence_num, or first_snapshot_sequence_num columns in the view contains a number that is less than the last transaction completed by a shrink operation (109), the shrink operation will wait for those transactions to finish.
To resolve the issue, you can do one of the following tasks:
Terminate the transaction that is blocking the shrink operation.
Terminate the shrink operation. If the shrink operation is terminated, any completed work is retained.
Do nothing and allow the shrink operation to wait until the blocking transaction completes.
...
You may need to specify the TRUNCATEONLY parameter to get it to release space.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 22, 2014 at 11:15 am
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply