July 1, 2009 at 5:44 am
Hi Guys,
I have a database that I have just truncated the huge tables on the database and the size has now gone down to about 10GB, initial size was around 90GB.
Problem now is that after running series of shrink statements, the SQL command seems to run to quick and the size does not reduce at all. Is there anything I can do here.
dbcc shrinkfile(1,10000)
GO
dbcc shrinkdatabase('dbName',1)
What can i do here ?
July 1, 2009 at 6:18 am
Hi Dean,
If your database is running in FULL recover mode the you probably need to back the transaction log as below to free your extra space, try:
BACKUP LOG WITH TRUNCATE_ONLY
GO
...and then try to shrink your database again, if you can provide the results, may be able to provide some more info.
Cheers,
Dave
July 1, 2009 at 6:26 am
DB is in simple recovery mode.
July 1, 2009 at 6:35 am
Have some doubts here. You have cleared 80 GB of data.
What is your physical file size now?
BOL: DBCC SHRINKFILE does not shrink a file past the size needed to store the data in the file
use
exec sp_spaceused
what does the above give?
July 1, 2009 at 6:40 am
DB Size 99984.25 MB
Unallocated space 89150.41 MB
I need to be able to reclaim all that unallocated space as these was space allocated to tables before, which I have now deleted.
Cheers
July 1, 2009 at 6:43 am
I'm guessing a little, but did you literally truncate the tables or just delete a large percentage of the rows? If there are some rows left, maybe there is a lot of free space on the pages and rebuilding the indexes would compact them and allow the shrink to take some more space? If you literally did a truncate, then I don't think that would help.
I seem to remember something about not being able to shrink a file below it's original size, but I can't seem to find the reference, nor how to fix it. Does that sound familiar to anyone?
Good Luck,
Chad
July 1, 2009 at 6:44 am
Sorry - if you actually deleted the tables, it won't be the indexes.
Chad
July 1, 2009 at 6:47 am
I did a truncate and that was all I done. Didnt run a delete.
I know SQL will not allow you to reduce size from GUI, but there must be a way around this.
July 1, 2009 at 6:48 am
Dean,
See whether this applies to you
http://support.microsoft.com/kb/324432
There is a script in the article to see whether the shrink fails because of BLOBs in your DB
July 1, 2009 at 6:54 am
Did you confirm that it is the .mdf that has all the space in it (I believe sp_spaceused returns info for both log and data)? Is there only one .mdf and one .ldf file for the database? How is the 87GB split up between the .mdf and .ldf files? Even though you are in simple mode, there might be an uncommitted transaction way back that is keeping the log from freeing up space.
I also found the reference I was looking for in the BOL for DBCC ShrinkFile:
Use DBCC SHRINKFILE to shrink a file to a size that is less than the size specified when it was created. This resets the minimum file size to the new value.
But you already tried this, so I doubt it will help.
Chad
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply