June 7, 2009 at 4:13 pm
Hi
Is it advisable to shrink .mdf file of a database? What are the advantage and disadvantage of this?
June 7, 2009 at 4:16 pm
It is not.
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
June 8, 2009 at 4:15 pm
Gail, what happens when, after monitoring your data growth for years, you discover that it only fills a little percentage of the datafile, and you are getting short of disk space? In that case, isn't it better to release some space to the file system?
Alberto
Alberto De Rossi
Microsoft Certified Solutions Associate - SQL Server
Microsoft MVP - Data Platform
Power BI User Group Lima - Community Leader
June 8, 2009 at 4:25 pm
Gails advice here is correct most of the time. In NORMAL use a database stabalises at a particular size.
HOWEVER if you know your database is normally 50% fulll at 1GB and you took exceptional action that took it to 10GB, and you now have 50% of 1GB again you should shrink back to 1GB.
This should remove any O/S fragmentation created above 1GB.
Tim
.
June 8, 2009 at 4:42 pm
If I may jump in and deprive Gail of a point...............(She's got plenty)
If your database data file is obviously way over sized and you you know you will never need the file that size and besides you need the disk space, then yes, as Tim says, that is an occasion when you can shrink the file. Of course if you do, don't shrink the file down to its minimum possible and immediately follow it with a reindex.
An option is to use the truncateonly option of dbcc shrinkfile (don't use shrink database). This does not move data about and just trims the file to the last used extent. Depending where this extent is it might recover no space, about what you wanted, or more than you wanted. If more than you wanted increase the file again manually to the desired size. For a large database this method can be much quicker and less intrusive.
---------------------------------------------------------------------
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply