February 20, 2007 at 5:49 pm
Hi,
I'm attempting to shrink a 45gig database that am having no success. I've freed up about 20gig (by truncating tables) and through the database properties it does say there is 20gig available.
However, attempting to shrink the database, only shrank the database to 42gig. But doesn't seem to shrink any further either through the wizards or the dbcc shrinkfile/shrinkdatabase commands.
I've been reading around that some binary, text, ntext and image types may cause the database to not shrink due to the way SQL Server 2005 stores such data. So as a test, I truncated/dropped any table that had such column type, and then, attempted to reshrink the database, but didn't budge lower than 42gig.
I've also tried incremental shrinking, and altering the database size through the wizards, with also no success.
Anyone have any ideas?
Kind Regards,
Paul
February 20, 2007 at 6:04 pm
In 2005 you can shrink the log file to specific size so if most of the 20gig is log file you can either truncate it or shrink it to size. Hope this helps.
http://support.microsoft.com/kb/907511
Kind regards,
Gift Peddie
February 20, 2007 at 8:58 pm
Hi Gift Peddie,
It's not a log file problem. My log file size is only 3 gig. The 20gig is recently freed space done be truncating tables, but for some reason the database still 'reserves' that diskspace - and therefore, I'm unable to shrink it to free up the diskspace.
Kind Regards,
Paul
February 21, 2007 at 12:04 am
Using Import / Export, you can transfer all the data into new Database. This method may solve your problem.
Just Try it.
February 21, 2007 at 12:23 am
I believe you need to shrink data file not log file so you have to use the DBCC SHRINKFILE command to shrink data file also...
Use <dbname>
go
DBCC SHRINKFILE (fileid, FileSize)
See BOL for details...
MohammedU
Microsoft SQL Server MVP
February 21, 2007 at 7:46 am
Try dropping the indexes before dropping the tables if you don't have plans to use the tables again.
Kind regards,
Gift Peddie
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply