Reclaiming Reserved space for tables
Question
Wednesday, January 23, 2008 10:35 AM
Dear Friends,
I'm running a SQL 2005 database. Lately (in the last 4 months) my database size has grown exponentially and now totals 2.5 gig (it used to be about 100mb). When I run sp_spaceused i get the following info:
Reserved Space 2522MB
Data 450MB
Indexes 215MB
Unused 1856MB
<!--[if !supportLineBreakNewLine]-->
The unused space is reserved by my two largest tables, one being 542mb (which is a log I create and the size seems fine for the amount of data) reserving 1033mb and the other being 121mb (which is another log and also seems fine) reserving most of the rest.
I have tried running DBCC SHRINKFILE and DBCC CLEANTABLE commands on a backup copy of the database and cannot seem to free the unused space.
I have also tried copying the database in an attempt to reduce the unused reserved space and have tried creating a new database and importing the tables with no luck
Any help would be greatly appreciated, noting that I'm honestly not that technical and would probably need step by step instructions, but if you only have time to point me in the right direction that would also be of great help!
Jano
All replies (8)
Wednesday, January 23, 2008 3:40 PM âś…Answered
If you are just trying to return the disk space to the OS, use DBCC SHRINKDATABASE, or you can right click on the database in SSMS and do Tasks --> Shrink --> Database, and check the Reorganize files before releasing unused space. This will move the data forward in the files, and put the free space at the end of the files where it will be truncated off. Of couse the database will likely grow back to size in the future. Why do you want to release the space? You want to be careful with growing/shrinking files repeatedly as it can lead to external disk fragmentation.
Wednesday, January 23, 2008 2:42 PM
What is the fill factor on your indexes set to?
Wednesday, January 23, 2008 3:31 PM
The fill factor is set to 0.
Wednesday, January 23, 2008 3:50 PM
Thanks for your quick response Jonathan. I've given your instructions to the DBA's where I'm hosting my DB and will let you know once they've tried your solution. I'm trying to free the space because the database is currently at my maximum allowed capacity even though I'm just using 700mb and I want to duplicate the database for my developers to have a testing environment. Will keep you posted on any development.
Wednesday, January 23, 2008 3:54 PM
Did the database autogrow to this point, or did you build it at this size? If it grew to this point on its own, I would start looking into why, unless you did some data archiving/deleting. Index rebuilding can grow databases out and then free up space, so you may want to change how you are doing routine maintenance if database size is an ongoing concern. Is this SQL 2000 or 2005?
Monday, January 28, 2008 8:02 AM
Hi Jonathan,
After giving the DBA the instructions you gave me he replied with the following:
"I have managed to reduce your database from 2.6GB to 600MB by rebuilding the clustered indexes with the following commands:
-- Clears Unused space of CLUSTERED indexes
EXEC sp_MSforeachtable @command1="print '?' DBCC DBREINDEX ('?', ' ', 80)"
--Use this to get rid of unallocated space
DBCC SHRINKDATABASE (DB,10)"
Thank you kindly for your quick response and willingness to help, you definately helped solve my problem,
All the best,
Jano
Monday, July 20, 2009 9:25 AM
We have had a problem recently where we found that some sql servers were set to grow at 9600%. This menas that when the max file size approaches (say 500MB) the file grows by 9600%. This seems to be related to some bug in an sql upgrade. It is too much of a coincidence to find 3/4 sites with exactly the same figure. The first one we put down to operator error.
Tuesday, January 31, 2012 12:48 PM
It appears that DBCC DBREINDEX is now discouraged and ALTER INDEX is now the preferred command according to MSDN