October 11, 2006 at 8:32 am
Hello all.
In past hour I have researched on net and there are mixed opinions on Shrinking Databases. Some say its a performance boost while others state that it fragments indexes and is a performance hit.
Now we recently went live on a new production server. The dynamic databases are set to Full recovery mode with frequent t-log backups while the rest are set to simple mode. Some of the databases are extremely large and I was thinking of shrinking them. We have 6 clustered drives and one of them which holds mdf files is falling short of space. Would shrinking the databases be a good idea.
I look forward to your replies.
Regards,
Madiha.
October 11, 2006 at 9:02 am
http://msdn2.microsoft.com/en-us/library/ms189080.aspx
Consider following information when you plan to shrink a database:
· A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.
· Most databases require some space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.
A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database
October 11, 2006 at 10:20 am
A pro opinion for shrinking: Your backups will run faster.
A con opinion: If the database will grow again, and entering data to your online system is causing the growth, some user will have a long wait when the database needs to expand. Also, repeted expansion will cause fragmentation of the physical database file. Tour indexes may not be fragmented, but if the raw file is, it dosent matter....lol'
October 12, 2006 at 5:59 am
The only time I shrink a db file is for these reasons:
Dangerously low on space to buy time until a permanent resolution is found.
A huge dbccreindex takes place that causes the mdf file to be double the db size.. (unless this is scheduled on a regular basis).
Shrink .mdf and .ldf files right before moving databases from one server to another. This causes the restore to not run as long due to not having to allocate larger than needed files that hold no data.
A huge logged operation that is onetime for the transaction log.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply