April 20, 2011 at 9:16 am
Hi
I've got a large mdf database file that i need to shrink.
I've got 400gigs free space, due to objects that was dropped.
Server is SQL 2008 R2 and database is SQL 2005 compatability mode.
Database is in simple mode.
I've tried:
1)alter database <> modify file
i get the error:
MODIFY FILE failed. Specified size is less than or equal to current size.
2)dbcc shrinkfile
it shrinks, but 100mb takes 3 hours
This is a production database, so i can not take it offline.
Any ideas on how i can get this space back quicker?
Thanks
April 20, 2011 at 9:32 am
DBCC SHRINKDATABASE(N'TEST', 5 )
GO
TEST is a database name
5 is a percentage
Check attachments
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 20, 2011 at 10:17 am
The reason it's slow is that it's literally moving from the end of the file and stuffing data into the earliest gap that it finds, regardless of trying to preserve contiguous data, so as well as taking a long time, you'll end up with an awful lot of fragmentation.
Depending on what caused the data to grow, you may be able to retrieve some of the space using the truncateonly option of DBCC SHRINKFILE:
http://technet.microsoft.com/en-us/library/ms189493.aspx
which will only de-allocate any unused space at the end of the file without moving any existing data, however this will likely not free all the space.
If truncateonly doesn't work and the operation that caused the 400gb of extra space is rare (e.g. you don't expect the DB to grow back to that size any time soon) and you can't afford the space, your only real option is to wait for shrinkfile to complete - after this point, you should rebuild/reorganise indexes to undo the damage of the fragmentation.
Shrinking database files is an operation to be avoided whenever possible
April 20, 2011 at 10:50 am
What I'd recommend is that you shrink it a little, perhaps 25GB, then the do an index rebuild. So schedule those operations together, in that order.
Once that's done, check free space, then consider repeating it again.
However, be sure that you are keeping free space in your database files. Do you really need that space back? Is it going to be used somehow? If not, why not just leave the MDF alone. It's not hurting you, and if you have IFI, it doesn't impact DR.
April 20, 2011 at 10:58 am
I would just go with the index rebuild and keep the free space in the file. This will help overall performance down the road by decreasing the likelihood of a file growth.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply