Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question

migrated from stackoverflow.com Jan 22 '12 at 15:53

This question came from our site for professional and enthusiast programmers.

    
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 '10 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 '10 at 16:42

3 Answers 3

up vote 3 down vote accepted

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.

share|improve this answer
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 '10 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 '13 at 15:06
    
Paul Randalll is wrong. WE gained for example speed by shrinking. –  Offler May 28 '13 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 '13 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 Randal May 29 '13 at 21:40

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.

share|improve this answer

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.