June 16, 2009 at 1:40 am
Hi all
I need to try to free up some space on one of our servers and thought perhaps shrinking one of our larger dbs was the answer.
So, I had a look at our 'mailing' database and was told it was using 67,000 Mb with 21,000 free )approx). So I told it to shrink it down to 0% free space and from what I can see all it did was shrink the log file. Now it's telling me there is 58,926 used and 13,808 free. Why can I not free up at least some of that 13,808?
Regards
Farren
June 16, 2009 at 2:23 am
What command are you using to shrink the database?
I suspect you are using DBCC SHRINKDB, which will not shrink the database file smaller than the original allocation size, which I would guess was 58,926.
If you want to shrink it any smaller than that, have a look at DBCC SHRINKFILE in BOL.
Before you shrink it down to 0% free space, are you sure it won't need to grow again?
You don't want it expanding when users are trying to access it.
June 16, 2009 at 2:25 am
Hi
Well sadly I was using the GUI as I'm really new to SQL server.
So, I am now running the command instead..
DBCC SHRINKDATABASE (mailing, 10)
GO
I'll see if that does the trick.
Regards
Farren
June 16, 2009 at 3:45 am
Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.
Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.
See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply