Microsoft SQL Server Log Truncation

SUMMARY

Information if backups are scheduled and running on the database but the logs are growing and eventually fill up the disk space.

ISSUE

Purpose

Keep the SQL Server logs from consuming all the server disk space.

Applies To

Microsoft SQL Server 2005 through Microsoft SQL Server 2014

Symptoms / Description

Backups are scheduled and running on the database but the logs are growing and eventually fill up the disk space.

Cause

Unitrends backup agent does not control the size of the log file on disk.  The Unitrends agent after a successful full backup or transactional log backup will tell Microsoft that the backup has been completed and Microsoft will go in and truncate the logs but this does not affect the size consumed by the log file on disk. 

Resolution / Workaround / Execution Process

To get the database log size back under control a shrink operation should be run on the database and another Transaction Log backup will need to be performed.

One way to keep the log files from growing too much is to run more frequent Transaction Log backups.  With more frequent Transaction Log backups the log is being truncated more frequently and the log doesn’t have time to grow and fill up the disk.

A second option is to run a shrink operation via a maintenance plan before a full backup is scheduled to run.

Third-Party Sources

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkdatabase-transact-sql

https://docs.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Contact us