spkvijay
asked on
Diskspace For Index Rebuild
Hi ,
How to reduce .mdf file size after Index Rebuild (Offline) in SQL Server 2005
Before doing index rebuild i changed recovery mode to bulk.
How to reduce .mdf file size after Index Rebuild (Offline) in SQL Server 2005
Before doing index rebuild i changed recovery mode to bulk.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
However, just because you CAN do it, doesn't mean you SHOULD do it.
It is generally accepted that running SHRINKFILE on a datafile is a bad idea. It causes internal file fragmentation and can cause bad performance.
If the file grew to the size it currently is, it obviously needed the space and the next time you perform index rebuilds (if that was the reason the file grew), then it's only going to grow again.
A quick search produced any number of articles on why you shouldn't shrink your datafiles:
http://www.karaszi.com/sqlserver/info_dont_shrink.asp
http://dba.stackexchange.com/questions/17277/when-is-it-ok-to-shrink-a-database
http://ask.sqlservercentral.com/questions/1872/is-using-shrink-bad-for-your-database.html
http://www.brentozar.com/archive/2009/08/stop-shrinking-your-database-files-seriously-now/
(Brent Ozar's post also links to another half a dozen articles from people like Paul Randall, Kimberly Tripp and Tom LaRock saying the same thing).