Sql-server – Transaction log still full after backup

backupshrinksql servertransaction-log

A database in an availibility group on MS SQL 2014 with a decent log file which at some occasions has been shrinked because of some major jobs filling it up. Usually don't shrink logfiles but as there's a lot of other databases that don't grow at the same time it has been done instead of extending disk, it's a VMWare environment. Now the logfile has grown a lot again, filling up the disk. The log disk was extended, transaction logs backed up and trying to shrink the file about 10-20 GB but there's still only 200 MB free space in the logfile.

Best Answer

Explanation to this problem was that replication halted on one database in Availability group. Transaction log backup was successful but log file could not be emptied because replication to secondary server was paused. I did not notice this looking at the databases initially. And didn't notice that log_reuse_wait_desc was showing AVAILABILITY_REPLICA on the primary server (looking at the wrong line in result). But when checking again after Shankys reply I discovered that and Availability group Dashboard showed that replication had halted. Resumed replication, backed up log again and then I was able to shrink logfile to a more accurate size.

Related Solutions

Sql-server – Shrinking a SQL Server Transaction Log

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.

Sql-server – What could be the reason for shrinking the transaction log file before and after the database backup

Most likely the one who set up that configuration wasn't very experienced with SQL Server.

Or, disk space was extremely valuable, and even though you only reclaim some disk space before it (soon) grow again, the costs and disadvantages of doing shrink was considered lower priority than cost for disk space. Not likely, but a theoretical possibility.

Why a shrink both before and after the full backup? Beats me. A full backup does a checkpoint, which will empty the log (being in simple recovery), so doing the shrink after the full backup would be the reasonable thing to do. If one could use the word "reasonable" when we're talking about regular log shrinking.

I would remove the shrink and add some monitoring. I.e., poll the size of the log every 5 minutes and see if/when it grow.

And, of course, as you state, grow it to some reasonable size. That should probably be your high watermark file size, if you have that information available. Possibly the size before that first shrink - exceptional happenings in your database not counted...