Sql-server – Shrinking a SQL Server Transaction Log

backuprestoresql serversql-server-2012

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’.

Best Answer

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.

Related Question