Microsoft SQL Server transaction logs tend to grow over time and can sometimes fill all the free space on the server drive. To avoid this, you should take regular transaction log backups or use transaction log truncate and shrink operations in SQL Server.
Table of Contents
Difference Between SQL Server Log File Truncate and Shrink
You need to be familiar with the recovery models in SQL Server before you can understand the difference between truncate and shrink operations:
- Simple recovery model — transaction log files (LDF) are automatically truncated after every transaction. This recovery mode is for development or test environments only and is not recommended for production;
- Full recovery model — the transaction logs won’t be cleared until a backup of the transaction log has been completed (no automatic log truncation). The transaction log will be truncated only after backup transaction log. This mode is the best way to recover data after a failure and is used when point-in-time recovery is required.
- Bulk logged — this mode allows to reduce the log space usage by using minimal logging settings. The SQL Server truncates the transaction log only after a successful transaction log backup.
Full SQL Server database backup does not truncate the transaction log. Configuring regular transaction log backup is the only valid way to truncate log files. The truncate operation makes the space available again, but does not reduce the size of the transaction log file on the disk.
Shrinking should be used to reduce the size of the SQL Server transaction log on the drive. During the shrink operation, MSSQL moves the date from the end of the file to the free space at the beginning of the file. The free space is then deallocated and returned to the file system.
You can check the transaction log space usage for all databases using the following T-SQL command:
DBCC SQLPERF (LOGSPACE); GO
- Log Size (MB) — shows the current size of transaction log for the DB;
- Log Space Used (%) — shows the percentage occupied by the transaction in the log file.
How to Truncate Transaction Logs on MS SQL Server
If the size of the transaction log files (*.ldf) is fixed, or if there is insufficient disk space, all operations to modify the database will be unavailable. You may encounter the following errors when connecting to an MS SQL database:
The transaction log for database is full due…
or
ODBC error: (42000) – [Microsoft][ODBC SQL Server Driver][SQL Server]The log file for database ‘database’ is full. Back up the transaction log for the database to free up some log space.
This is the situation that typically occurs when a full recovery model is used. To free up space, you should back up the transaction logs (the safest method), or you can delete the logs by changing the DB recovery mode to Simple.
It is possible to change the recovery model of MS SQL Server on the fly, but to reduce the risks it is desirable to switch the database to read-only mode.
Open the SQL Server Management Studio (SSMS), select the database (with large transaction logs), right-click and select Properties. Go to Options and switch the database Recovery model to Simple.
In this case, the transaction logs are automatically truncated, but still take up a lot of space as the truncated space will not be deallocated. To reduce the size of the logs, you can also shrink the files.
Then right-click DB, select Tasks > Shrink > Files. In File type select Log, in File name field specify the name of the log file. In Shrink action choose Reorganize pages before releasing unused space, set the desired size of the file, and click OK.
You can find three shrink options here:
- Release unused space — this option will reclaim unused space in the transaction log file and shrink the file to the last allocated extent. Allows to reduce the file size without moving data;
- Reorganize pages before releasing unused space — reclaims unused space and tries to relocate rows to unallocated pages;
- Empty file by migrating the data to other files in the same filegroup — is used to move all data from the specified file to other files in the same filegroup. The empty file will be removed later.
After completing an operation, change the database Restore mode back to Full or Bulk-Logged.
WARNING! This allows you to quickly reduce the file size of transaction logs. But it results in the loss of transaction records since the last backup. This is why you should perform a full DB backup as soon as possible.
For small DBs in Full Recovery mode, you can enable the “Auto Shrink” option in the MS SQL database properties. Go to the DB Options > and set the Auto Shrink parameter value in the Automatic section to True. After you enable auto shrink, MS SQL will only perform automatic compression if the unused space is more than 25% of the total volume size. However, shrinking transaction files is better than shrinking data files.
Using Transact-SQL Truncate Transaction Log
You can also switch the MS SQL DB to simple recovery mode and shrink the log file using T-SQL script:
USE ″YourDBName″ ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE; GO DBCC SHRINKFILE (″YourDBName_log″, ″Desired_size_in_MB″); --
For example, to shrink a log file to 4 GiB (4096 MiB), use the command:
DBCC SHRINKFILE(MyDatabase_Log, 4096) ALTER DATABASE ″YourDBName″ SET RECOVERY FULL GO
Be sure to back up your database in the Full recovery model.
Another way to shrink the SQL transaction log is to backup the database logs with the command:
BACKUP LOG YourDBName TO BackupDevice
To automatically switch all databases (except system ones) into the Simple recovery mode and execute transaction logs shrinking, you can use the following Transact SQL script:
declare @db_name nvarchar(100)
declare cursor_size_srv cursor for
SELECT name AS DBName
FROM sys.databases
where name not in ('tempdb','master','msdb','model)
ORDER BY Name;
OPEN cursor_size_srv
FETCH NEXT FROM cursor_size_srv INTO @db_name
WHILE (@@FETCH_STATUS=0)
BEGIN
exec ('declare @logname nvarchar(100)
USE [' + @db_name + ']
SELECT @logname = name FROM sys.database_files where type = 1
ALTER DATABASE ' + @db_name + ' SET RECOVERY SIMPLE
DBCC SHRINKFILE (@logname , 10, TRUNCATEONLY)')
ALTER DATABASE ' + @db_name + ' SET RECOVERY FULL
FETCH NEXT FROM cursor_size_srv INTO @db_name
END
CLOSE cursor_size_srv
DEALLOCATE cursor_size_srv
How to Move Transaction Log Files to Another Drive?
If you cannot expand the drive where the transaction log (LDF) is stored, you can move it to another drive that has enough free space. Unfortunately, log will require you to detach and attach a database when moving (your SQL database will be unavailable for some time).
Run the following command to get the current location of the transaction log file and its maximum size:
select file_id, type, type_desc, name, physical_name, state, state_desc, size from sys.database_files
In our example, the database has one transaction log file with the path E:\msdb\mysqldb.ldf.
To get information about the current size of the transaction log file and its usage percentage, run the T-SQL command:
declare @logSpace table (
dbName varchar(100),
logSizeMB float,
logSpaceUsed float,
status int
)
insert into @logSpace
execute('dbcc sqlperf(''LogSpace'')')
select * from @logSpace where dbName = 'mysqldb'
Run the following script from MS SQL Management Studio to switch the DB to single user mode and disconnect the DB:
# Change the database to single user mode Use MASTER GO ALTER DATABASE mysqldb SET SINGLE_USER GO # Detach the DB sp_detach_db 'AdventureWorks' GO
Move the transaction log file to a new drive using File Explorer or any file manager. If the transaction log size is very large, the move task may take a long time.
# Attach the database sp_attach_DB 'AdventureWorks', 'F:\msdb\mysqldb.mdf','M:\msdb\mysqldb.ldf' GO
If a long downtime of the SQL Server database is not acceptable, you can add a new log file to the database on another drive with enough free space. To add an additional log file, use the ALTER DATABASE [dbname]ADD LOG FILE command.
For example, we are going to add an additional log file on a different disk for the mysqldb database:
ALTER DATABASE mysqldb ADD LOG FILE ( NAME = mysqldb, FILENAME = 'E:\mssql\data\mysqldb2.ldf', SIZE = 1000MB, MAXSIZE = 2000MB, FILEGROWTH = 5% ); GO
Note. Microsoft doesn’t recommend using multiple log files for a single database as a long-term solution. This solution allows you to quickly start the database when you run out of space on the transaction logs drive. After you investigated the reason why the transaction log is full and cannot be truncated, you should disable such a file.
We have looked at a number of ways to quickly reduce the size of the transaction logs on the Microsoft SQL Server. Not the greatest, but very simple and effective.
