8

I’m currently having to deal with a SQL Server transaction log that has got out of control. Disclaimer: I’m not a dba and this isn’t my area of expertise so please bear with me.

Currently I have a 115GB Transaction log file for a 500MB database which has (obviously) been poorly managed for some time for it to get in this state.

The top priority is to reclaim the space on the disk taken up by this file before we run out! I’ve been told increasing the size of the drive is not an option, even temporarily, and based on past growth, we need to act pretty soon.

As I understand it, the best approach is to keep the db in full recovery mode but take regular backups of the log file, monitor this over a period of time and adjust initial size and increment to suit. All okay.

Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above?

My thinking is that if something happened around this time, we could simply restore the full backup without using the logs.

UPDATE

A few extra details in reply to some of the answers and comments:

  • We do want to retain the ability to do a point-in-time restore so the database should remain in full recovery mode.

  • The reason that the t-log file has grown so large is that it has never been backed up. Verified as log_reuse_wait_desc returns ‘LOG_BACKUP’.

Improve this question
5
  • Did you check for any open transactions or any sort of ad-hoc or scheduled task that would have caused the transaction log to grow that large? Identifying the reason can help you plan the growth accordingly. Commented Jan 25, 2016 at 11:43
  • As soon as you switch it to Simple mode, you can no longer do transaction log recovery past that point, until your next full backup. Even if you switch it back to Full Recovery Mode, transaction log backup/restore-recover won't start working again until you take another full backup. So be sure to take a full backup immediately after you switch it back to full recovery. Commented Jan 25, 2016 at 21:03
  • The usual reason for out-of-control log files like this are 1) failure to take regular full backups, 2) failure to take regular transaction log backups, or 3) some setting in your full or log backups (such as copy-only) that is preventing the normal backup marks from being set. Commented Jan 25, 2016 at 21:06
  • @RBarryYoung We’ve verified that it’s because no t-log backups have been taken. Do you recommend doing as I suggested originally but taking a full backup manually straight after returning the db to full recovery in order to avoid the problems you mentioned? It’s imperative that we get some of the disk space reclaimed asap. Commented Jan 26, 2016 at 9:36
  • AFAIK, there is not point to being in full recovery mode if you are not doing log backups. If you are not planning to do log backups then just leave it in simple mode. Your full backups will still be restorable either way, you just won;t be able to do a recovery/roll-forward, but you cannot do that anyway without log backups. Commented Jan 26, 2016 at 18:34

2 Answers 2

Reset to default
4

The transaction log for that database contains all transactions since the last transaction log backup, or the last time it was switched from simple recovery mode. Execute the following to get the definitive answer as to why SQL Server can not truncate the log and subsequently why the log is growing. 

SELECT d.Name ,d.log_reuse_wait_desc FROM sys.databases d ORDER BY d.name

If you want point in time recovery then leave the DB in the full recovery model and take frequent log backups. Each log backup will contain all transactions since the last log backup. The log backup process is also responsible for clearing the log and marking the space for reuse i.e. the next transaction made in the DB will be written to the start of the truncated log in a circular fashion. This backup and reuse of the log is what prevents the log file from growing.

If you are not interested in point in time recovery and want to simplify the administration of the database. Then set the database to the simple recovery model and do not take t-log backups. SQL Server will automatically truncate the transaction log after each transaction is committed. Meaning that once the transaction has been committed to the log the record is overwritten by the next transaction etc.

Either way, once you've made one of these two decisions you can then shrink the log file down to a more reasonable size. Note ideally you want to make it large enough so it doesn't grow but not so large that you'll need to shrink it again. Also note that you can not shrink the active part of the log.

Download and deploy https://ola.hallengren.com/ database administration solution to cover backups, index fragmentation, statistics and CHECKDB.

You might also find the 'disk usage' report returned by right clicking the DB in Object Explorer > Reports > Standard reports > 'disk usage' useful for returning the free space in the t-log.

I also recommend that you Google why it's so important to keep the log chain intact from a DR point of view, and how switching from full to simple breaks the chain leaving you exposed to data loss.

Improve this answer
3

Seeing as we take regular full db backups at midnight, would it be safe for me to temporarily put the database into Simple Recovery Mode (after one of these backups has run), shrink the log file to reclaim (virtually all of) the space and then put it back in to Full Recovery with the backup strategy mentioned above?

Yes, it would be safe provided that you interfere with no transactions when you do this, such as a late night load. In general, if a database should be in full recovery mode, you want regular T-Log backups. This will reduce the problem you're facing. I write "in general" because in some cases, I've seen people set a database to full without knowing why they did it. Let's assume this isn't that case.

You may want to consider why the log is this size relative to the database size, though. A 500MB database with a 116GB log seems very out of proportion for a one time event. I would suggest monitoring what's happening on the database to see how it arrived at that size in the first place.

Improve this answer
4
  • From what I understand, this has been ticking along for six months or more now with zero maintenance, hence the size. I'll do as you suggest though and keep an eye on the growth of the file once I sort the immediate problem out. Commented Jan 25, 2016 at 13:46
  • To be honest, I want to vote down this answer. Commented Jan 25, 2016 at 21:47
  • 1
    Actually it is NOT safe to switch from FULL to Simple recovery and then start doing the shrinking. From the description of Kraig Walker, I guess this db has not any t-log backup for sometime. So the first thing to do is to check the db recovery mode and what is the last tlog backup done (if it is NOT simple recovery mode). If there is a tlog backup, then check why tlog backup does not clear the log by checking [log_reuse_wait_desc] from sys.databases view. My experience is that if majority of the log file is empty, you can do shrink directly. Commented Jan 25, 2016 at 21:55
  • @jyao Your guess is right, there are no log backups and that is the reason the file is so large. Commented Jan 26, 2016 at 9:38

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.