Shrink SQL Database?

[Migration User]

Hey all,

I have been trying to free up some space on my SQL server and have been unsuccessful. Basically, I have changed all my purging rules, etc., but still need to reclaim the actual drive space previously consumed. I am out of drive space (200Gb drive and 2Gb left free). I have been using the shrink db and shrink file options, but to no avail. Can someone tell me if I need to have more free space available in order to shrink and reclaim all the unused space in the db? Or, is there something else I need to do to get the unused space reclaimed for the OS....???

Thanks,
Kevin

[Migration User]

Have you tried creating a SQL maintenance plan?

[Migration User]

Yes, I have a maintenance plan in place, but it does not appear to do anything. I just cannot figure out how to reclaim all the unused space in my tables. Normally, I would truncate the data from the tables that is no longer needed (for example, Monitor Solution captured all sorts of data and we don't need it.....) Once the tables were truncated, I could shrink the db and all the space would be allotted back to the OS. Now that I have less than 2gb of free space, the shrink is not working and in the next few hours, I am going to run out of space... :-(

Any other ideas???

[Migration User]

What have you done for the maintenance plan?

[Migration User]

I'm asking because it should be the rebuild indexs that claim the space. A google check shows some known issues with SQL2005 SP2 and reindexing.

[Migration User]

My maintenance plans currently consist of the following:

One that backs up the Incident Db nightly (not releated)
One that backs up the other Dbs weekly (Altiris, eXpress)
One to Reindex on Altiris Db
One to Reorg Index on Altiris Db

I don't have SQL 2005 SP2 installed either....

I am going to try to kick off the Rebuild Index job to see what happens. If I remember correctly. I will not have enough space to run it. I think it needs drive space in order to run...?

Thanks again!!!

[Migration User]

I think you are right in the drive space. What size is the temp db? Altiris uses that extensively. Stopping and starting the SQL service *should* reduce that and hopefully gain you some space.

[Migration User]

Well, my logs are on a different drive than the actual data files. I am going to try to free up as much space as possible on that drive and see if I can get the maintenance plans to complete. Also, I think I have a script that I can run that will determine whether to rebuild or reorganized based on size. Maybe that will work... Grrrr. Since when are Altiris Admins considered SQL DBAs???

BTW, Don't SQL DBAs make a lot more money? LOL

Feel free to let me know if you have any other ideas.. Looks like this is gonna be a long night...

[Migration User]

Just an update....

Turns out the NSMessageQueue table was massive resulting in timeouts during the scheduled purging maintenance tasks. Manually ran the sp to remove the "closed" events and now the scheduled task is able to complete without timeouts... Also, my SQL maintenance plans are working and the db size is back under control.

Thanks,
Kevin

[Migration User]

Hm. Unfortunately, that isn't my case. . .the main DB has reach the 4GB limit of 2005 Express. I try to shrink and it finishes instantly (so it isn't working I assume). I tried to rebuild the indexes, but it can't because it has reached its size limit. Is there anything I can safely drop from the DB to get more room? Is there any way to get out of this problem with file size being too big?

[Migration User]

If you can get into the SQL admin tools, run the query mentioned here: https://kb.altiris.com/article.asp?article=21310&p=1 to identify the larger tables.

You can safely truncate (usually, but make sure you have backups), most of the tables the begin with Evt (event tables). You'll lose some historical data, but those are usually the biggest tables.

[Migration User]

Thanks. I will try that. . .fortunately, the DB was not all the way up to the 4096 size, so I was able to expand it enough to run reindex on the bigger tables (I had found going one by one through the list). Is there supposed to be something that keeps it trimmed down and cleaner? I did find that my space management hadn't run for 3 months. . .Thanks.

[Migration User]

Purging maintenance for the Notification Server and from some of the solutions, such as App Metering and Software Delivery, do help keep some of the tables under control.

Running a SQ maintenance plan on a weekly schedule should also help.