Should I be regularly shrinking my DB or at least my log file?

Question

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.

We backup transaction logs every hour, diff backup 5 nights a week, full backup the other 2 nights of the week.

Answer 1

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.

Answer 2

There is an autoshrink option.

Answer 3

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.

Answer 4

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.