October 16, 2013 at 9:02 am
I noticed an msdb database that was filling up a drive and queried to find that the dbo.sysmaintplan_logdetail table was the culprit.
If I try to run a history cleanup all the space on the drive is used and the job fails.
What is the best way of clearing out this table?
Thanks
October 16, 2013 at 9:15 am
Is it the log file that grows to fill the disk? If so, clear out the entries one day at a time (or whatever interval best suits your disk capacity). Then set up something that will do this regularly, otherwise this won't be the last time you have this problem.
John
October 17, 2013 at 8:32 am
Not knowing anything about your environment, here are a few suggestions, most of which assume this machine isn't required to be up 100% of the time.
I would do a one-off shrinking the log and then try my deletes, re-size the log, then set up a schedule to maintain the table moving forward.
Maybe consider a one-off shrinking of the data file and see if that frees up any space, then re-size, and schedule history clean up.
Maybe there is another database you can shrink up temporarily to give you some breathing room.
If tempdb is on that disk and it sufficiently large, you can cycle the SQL services on that box which will reset tempdb back to a smaller size and give you room to work.
If those doesn't work, perhaps there are files or database you could temporarily move off of that disk to give you room to clean up MSDB and then move the displaces files/databases back once you're done?
-Greg Goss
Database/BI Administrator
October 17, 2013 at 8:48 am
Some space you need to reclaim from the drive where mSDB resides ( and i suspect that all sys databases will on same drive) try to gain space there
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
October 17, 2013 at 9:40 am
Greg A Goss (10/17/2013)
Not knowing anything about your environment, here are a few suggestions, most of which assume this machine isn't required to be up 100% of the time.I would do a one-off shrinking the log and then try my deletes, re-size the log, then set up a schedule to maintain the table moving forward.
Maybe consider a one-off shrinking of the data file and see if that frees up any space, then re-size, and schedule history clean up.
Maybe there is another database you can shrink up temporarily to give you some breathing room.
If tempdb is on that disk and it sufficiently large, you can cycle the SQL services on that box which will reset tempdb back to a smaller size and give you room to work.
If those doesn't work, perhaps there are files or database you could temporarily move off of that disk to give you room to clean up MSDB and then move the displaces files/databases back once you're done?
-Greg Goss
Database/BI Administrator
+1
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply