Is there any benefit in shrinking database and log files?

Question

Post reply

Is there any benefit in shrinking database and log files?

Sam Sheikh

Say Hey Kid

Points: 678
More actions
May 4, 2006 at 2:49 am

#114523

Hello,
Sorry if this subject has already been covered and it probably has. As part of my weekly DB maintenance plans, I perform DBCC shrinkfile on our major systems. If space is not a concern, is there any advantage in doing this? I don't believe my dump files, DB or log, are any bigger and weighed up against the hit my systems take when the data or log files are expanded, I don't know if it's of any benefit? Thanks for any input.
Sam

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 1

you'd gain most benefit by not applying shrinks.

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

Torsten Katthöfer Newbie Points: 1 More actions · Answer 2

And whats about fragmentation? I'am not sure, but i think shrinking files with reorganisation decreases the amount of I/Os...

Sam Sheikh Say Hey Kid Points: 678 More actions · Answer 3

Thanks for the input. My weekly maintenance tasks are:

1. Run update statistics on DB

2. Run DBCC on DB

3 Shrink DB

Is this the way to go about weekly DB maintenance? I presumed the shrink database would not affect the results of the update stats. The reason I started running shrink database was mainly because of the growth in the databases during update stats.

Sam

Antares686 SSC Guru Points: 125444 More actions · Answer 4

Actually if space i really not a concern then you don't want to perform a shirnk. Odds are your file will grow again for the very same reasons it did before and each growth causes performance to be degraded during the growth. You really should look at whether ot not the database keeps growing back to the same size and what factors may be in play. Shrink does not improve perfomance in any way.

Sam Sheikh Say Hey Kid Points: 678 More actions · Answer 5

Thank you all for taking the time to respond, much appreciated!

Steve Jones - SSC Editor SSC Guru Points: 736494 More actions · Answer 6

You don't want growth or shrinking unless really necessary. Both slow the system, though arguably growth is worse.

You want to size appropriately and grow when needed to a size that will handle your needs.

That being said, if you are shrinking weekly, you might just want to grow the files larger and allocate what you think you need. I've usually only shrunk when a rare or one-time event caused something to grow large (apart from needing space).