5

My question is, should I be running one or both of the shrink command regularly,

DBCC SHRINKDATABASE

OR

DBCC SHRINKFILE

=============================

background

Sql Server: Database is 200 gigs, logs are 150 gigs.

running this command

SELECT name ,size/128.0 - 
CAST(FILEPROPERTY(name, 'SpaceUsed') AS int) / 128.0 
AS AvailableSpaceInMB FROM sys.database_files;`

produces this output..

MyDB: 159.812500 MB free

MyDB_Log: 149476.390625 MB free

So it seems there is some free space.

Our backup schedule is as follows:

  1. Transaction Logs Once an hour
  2. Full Backups twice a week
  3. Differential Backups 5 times a week
Improve this question
2
  • the data file is almost completely full, but the log appears almost empty - do you have full logging turned on, or just simple? What's the backup schedule for the log file?
    – SqlRyan
    Apr 23, 2010 at 16:24
  • I believe it may have grown when transaction log backup was inadvertently turned off for a period of time, since it has so much free space I would like to shrink it. Will shrinking it produce any performance gains? Is it safe to run DBCC ShrinkFile at any time? Will running DBCC ShrinkFile cause performance issues to users querying DB at same time? Thanks all,
    – Tom DeMille
    Apr 23, 2010 at 16:42

3 Answers 3

Reset to default
5

I strongly recommend you read Paul Randal's article on why you should NOT shrink data files (log files yes, data files no).

I won't quote or try to summarise the article as I really wouldn't do it justice! Just something I think you should at the very least be aware of.

Improve this answer
8
  • 1
    good article, thnks.... still feel like I want to do a one time shrink of my almost entirely empty transaction log table, agree that since we never bulk delete and are only growing we don't need to shrink the DB files
    – Tom DeMille
    Apr 23, 2010 at 16:54
  • Too bad that Randal neither mentions anything about fragmentation on a LUN carved out of a disk pool or of the affect of frag in the bpool. His example is totally trivial to the point of being useless.
    – ooutwire
    May 24, 2013 at 15:06
  • Paul Randalll is wrong. WE gained for example speed by shrinking.
    – Offler
    May 28, 2013 at 12:06
  • To add further resources to this, as to why shrinking your data files is harmful, there's a good set of further links here: brentozar.com/archive/2009/08/…
    – AdaTheDev
    May 29, 2013 at 21:09
  • @ooutwire - that's an old article. Check out the buffer pool category on my blog which explains about wasted bpool space. And why is that example useless? It's a simple example that people can understand easily.
    – Paul S. Randal
    May 29, 2013 at 21:40
4

The only advantage of shrinking your files is reclaiming the disk space, but here's the caveat - If your database is just going to grow to fill that space again, shrinking it can actually be detrimental in the long term. This is because, after a shrink, SQL Server will have to reclaim the disk space as it grows (which takes time, though not much), and it may lead to fragmentation on the physical disk (more of a problem).

If the files grew to be much larger than they normally will be and you want the hard drive space back, then do a shrink. If you're just wondering whether a shrink should be part of your regular maintenance, it shouldn't.

Improve this answer
1

As rwmnau says, a SHRINK isn't normal maintenance - so you shouldn't be doing it regularly. However, given that you're backing up the logs every hour and have ~150GB of free space - I'd be tempted to guess that you're never filling that log.

I'd probably SHRINK it to a reasonable size, and let it autogrow until you find your balance. You don't want it to autogrow in normal use, but I personally don't like my log files to be 99% empty either.

To guesstimate a reasonable starting point, you can either estimate the maximum number of changes in an hour (your backup log schedule) or just check the used size before a log backup for a few representative cycles.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.