locked
Database and Log Shrinking Question RRS feed

  • Question

  • I've done a fair amount of digging around on the Net to determine if shrinking my SQL databases and logs is a 'good idea'.
    Many sites say that it's a really really BAD idea, however - I've got an issue that I cannot seem to resolve.

    We have numerous large databases (90GB+) and their logs (another 70-90GB+).
    The databases and logs have kept growing, despite the fact we do a FULL database backup and Transaction Log backup every 30 minutes, the files are still massive.

    It's now got to a point where the disk-space on the server is almost totally used up and it's starting to interfere with normal operations on the server.

    I didn't want to simply 'shrink' the databases and logs without being sure that it was the correct course of action for the situation I am in.

    The bottom line is that we CANNOT add more storage space to the server, the databases and logs have NEVER been shrunk, the databases are backed up once nightly and transaction logs are backed up every 30 minutes.

    Aside from the shrink operation itself giving the server plenty to do - is there anything I should watch for or is there another way to reclaim space as looking at the files suggest that we have a lot of 'free space' but Windows doesn't see it as free.

    What would be the best idea in my situation?

    I was thinking of doing the following:

    1) Take full DB and log backups. (Of course, this would HAVE to succeed 100% before I'd consider moving on to step #2)
    2) Shrink all databases and logs.

    I realise that shrinking is NOT a 'do once a week' or even once a month kind of task, but I'm almost up against a wall and bigger disks (or moving the databases to another server) are simply not an option.

    Does anyone have any thoughts or ideas or words of wisdom?
    Thursday, March 22, 2012 3:21 PM

Answers

  • Hello,

    You have a good amount of different things going on it seems, let's try to break this down to be more manageable and take an approach.

    The first part of the post, the landscape is laid out, numerous databases. They happen to be roughly the same in size but their log files are very large (for this environment?). Disk can't be added to displace the growth and disk space is becoming a threatening issue.

    My first action would be to figure out why the log files are large (root cause). Is that normal for the application/amount of users? If not, are there large batch loads, refreshes, etc? Is there a single open transaction from 30 days ago not allowing the vlfs to be marked inactive, replication was setup and not working properly, database mirroring backlog? Are the active vlfs at the end of the log file - if so, you won't be able to shrink it much. If the root cause isn't found, anythign else would just be temporary workarounds, nothing would be a permanent fix.

    DBCC OPENTRAN: http://msdn.microsoft.com/en-us/library/ms182792.aspx

    DBCC LOGINFO: http://blogs.msdn.com/b/suhde/archive/2009/07/18/revealing-the-secrets-truncating-a-transaction-log-file.aspx

    SQLSKILLS: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

    Next, steps have to be taken in order for space to be available to the databases. I wouldn't do this part until a root cause could be found for each database with a large transaction log (i.e. do it after you find the root cause). There are a couple of different ways that the log file space can be reclaimed, but they all end with using DBCC SHRINKFILE. Whichever way it is done, the active vlf should be near the start of the log so the most amount of space can be reclaimed. Most people opt for switching the database into the simple recovery model, then shrinking the log while others make a dummy table and insert data until the vlf rolls around (if possible) to an inactive vlf at the beginning of the log file. Whichever way you are comfortable with.

    DBCC SHRINKFILE: http://msdn.microsoft.com/en-us/library/ms189493.aspx

    Finally, after the root cause has been found and steps taken to fix the issue (vendor application patch, different recovery model, transaction log backup frequency increased), document it and prepare to start trending your database growth. If adding disk isn't possible, eventually you will run out of space. How long until that happens? This is where you look at the future and say "will this happen to me again and how can I avoid it?".

    • Proposed as answer by sqlrockz Friday, March 23, 2012 12:23 AM
    • Marked as answer by Stephanie Lv Monday, April 2, 2012 8:30 AM
    Thursday, March 22, 2012 6:28 PM
    Answerer

All replies

  • Hello,

    You have a good amount of different things going on it seems, let's try to break this down to be more manageable and take an approach.

    The first part of the post, the landscape is laid out, numerous databases. They happen to be roughly the same in size but their log files are very large (for this environment?). Disk can't be added to displace the growth and disk space is becoming a threatening issue.

    My first action would be to figure out why the log files are large (root cause). Is that normal for the application/amount of users? If not, are there large batch loads, refreshes, etc? Is there a single open transaction from 30 days ago not allowing the vlfs to be marked inactive, replication was setup and not working properly, database mirroring backlog? Are the active vlfs at the end of the log file - if so, you won't be able to shrink it much. If the root cause isn't found, anythign else would just be temporary workarounds, nothing would be a permanent fix.

    DBCC OPENTRAN: http://msdn.microsoft.com/en-us/library/ms182792.aspx

    DBCC LOGINFO: http://blogs.msdn.com/b/suhde/archive/2009/07/18/revealing-the-secrets-truncating-a-transaction-log-file.aspx

    SQLSKILLS: http://www.sqlskills.com/BLOGS/KIMBERLY/category/Transaction-Log.aspx

    Next, steps have to be taken in order for space to be available to the databases. I wouldn't do this part until a root cause could be found for each database with a large transaction log (i.e. do it after you find the root cause). There are a couple of different ways that the log file space can be reclaimed, but they all end with using DBCC SHRINKFILE. Whichever way it is done, the active vlf should be near the start of the log so the most amount of space can be reclaimed. Most people opt for switching the database into the simple recovery model, then shrinking the log while others make a dummy table and insert data until the vlf rolls around (if possible) to an inactive vlf at the beginning of the log file. Whichever way you are comfortable with.

    DBCC SHRINKFILE: http://msdn.microsoft.com/en-us/library/ms189493.aspx

    Finally, after the root cause has been found and steps taken to fix the issue (vendor application patch, different recovery model, transaction log backup frequency increased), document it and prepare to start trending your database growth. If adding disk isn't possible, eventually you will run out of space. How long until that happens? This is where you look at the future and say "will this happen to me again and how can I avoid it?".

    • Proposed as answer by sqlrockz Friday, March 23, 2012 12:23 AM
    • Marked as answer by Stephanie Lv Monday, April 2, 2012 8:30 AM
    Thursday, March 22, 2012 6:28 PM
    Answerer
  • As Sean suggested, you need to find the root cause why the logs are getting that large. I would look at the log_reuse_wait in sys.databases for all databases to find out why the logs are not truncating (and available for reuse). Once you find out what is holding the log file from getting truncated you could use the methods as suggested by Sean above.

    Friday, March 23, 2012 12:38 AM