The server becomes slow, as many log files occupy it? Here are the best details about the “SQL Server truncate log” to help you find the best solution!
From all SQL Server versions, transaction logs are becoming more advanced over time; and they can take up all the free space from the server’s disk. Fortunately, there is a truncate operation of the transaction log to deal with this problem.
On the SQL Server transaction logs, this truncation process helps delete the inactive virtual log files (VLFs) to free up space. For more information about SQL server truncate log, please read our post carefully till the end.
A Complete Package Of Information About SQL Server Truncate Log And How To Truncate
The Recovery Model (DB) And SQL Server Truncate Transaction Log
We can use truncate transaction logs for recording all the transactions before transferring the data, as the transaction log files are necessary to recover the former state of the database during this stage.
Normally, there are operations, change types, sequence numbers, etc., performing and working in your computer system, and the diary is responsible for storing them all the time.
Hence, although you don’t need the transaction log to use the SQL Server, you may lose all your database when there’s a failure in the system without this tool.
The transaction log has another name, Virtual Log File (or VLF), and it contains many small logical units. You can perform the query “DBCC LOGINFO” in the SQL Server database’s context to search for the units’ numbers.
Truncate Transaction Log SQL Server 2012
Moreover, you can count the number of lines showing up on your screen to identify the amount of divided virtual files from the log. In the “Status” section, the segment’s current status is available with its value to help users find out what they should do next.
With “2”, you can’t use the segment as it is in use, while the segment is available and free with the value “0”.
In some cases, the computer system allows the growth of transaction logs in the SQL server database’s settings, and the segments are available. As a result, the log will develop with more brand-new VLFs.
On the other hand, when the disk can’t offer enough space or the system fixes the transaction log’s size, it will be impossible to adjust the database content and structure. You are also likely to encounter one of the errors below:
– 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.
– The transaction log for the database is full due to ‘OLDEST_PAGE.’
Typically, if the recovery model’s status is “FULL” or “BULK LOGGED” and the system can’t perform a transaction log’s regular backup, the administrators have to deal with the overflow of transaction logs SQL server.
From your SQL Server settings, the log files identify the recovery model and truncate automatically in different ways:
– Simple model: In this recovery model, the log files will reach the checkpoints before truncating automatically. Although this option requires database administration, it’s still the simplest.
The process of truncation is automatic in this mode. Moreover, when the system successfully completes the transaction, it will clear the log immediately. Interestingly, you can restore the database to the full database backup time of creation.
– Full model: As long as the transaction log’s backup is complete, the log will not disappear. After the transaction log’s backup, the system can truncate the log if there isn’t any automatic truncation.
BACKUP LOG dbname TO DISK = ‘dblog.bak’.
After each failure, this model is the best option in offering data recovery’s possibility. Specifically, the system gathers all the database changes into the log in this mode, so the LDF or the transaction log can increase over time.
With the full model, the computer will write all SQL transactions to the disk’s log files and store them until the system successfully creates the backup.
Amazingly, your device can restore the database at almost every point in time. You can go back to the former database’s copy and restore every transaction when it’s necessary by storing the logs. If you perform these actions in this case, the computer will restore a full backup to help you search the time within the logs.
– Bulk logged: In this mode, the system can use minimal settings in logging to reduce the usage space of the log. With bulk-logged, the automatic log truncation will not start until there is a backup, and the files from logs no longer stay in your computer system.
It is quite important to regularly perform a backup of your transaction log when your device uses the full model for the database. Otherwise, the data will increase without control until there’s no space left on the disk. The SQL Server will report this error to you.
You can use the T-SQL command below to see the statistics of the transaction log’s usage space for any databases:
DBCC SQLPERF (LOGSPACE);
– Log Space Used (%): this number shows you how much the log file’s transaction takes up in percentage.
– Log Size (MB): this result reveals the trDB’s transaction log’s current size.
Simultaneously, according to the best database practice, users should allocate space for the log files first. This step can help deter unexpected auto-growth errors. However, in some cases, the logs can take up all free disk space as the automatic SQL truncate log can’t work for some reason.
This error tends to unexpectedly occur when users need available space on disks, especially the log files “*.ldf” with their extremely large size.
Ways To Truncate Transaction Logs When Using MS SQL Server
“Microsoft OLE Provider for SQL Server: The transaction log for database “YourDBName” is full. To find out why you can’t reuse the space in the log, see the log_reuse_wait_desc column is sys.database
HRESULT=80040E14, SQLSTATE=4 2000, native=9002”
The error above starts when users are trying to connect to the database on MS SQL. The computer system always stores the SQL transaction log store in the drive, and in this situation, SQL cannot write new data as this drive doesn’t have space for the transaction.
Therefore, users need to use the Management Studio GUI or the SQL query to manually perform the truncation job on SQL log files.
If you use a full recovery model, you are likely to encounter this situation regularly. With this mod, your devices can’t clear the log files as long as every transaction is available in the backup. Besides, please remember to use a continuous LSN/ log sequence number in the records.
This is the fast and easy way to temporarily change to the simple recovery mode or fully backup the DB for the truncate. Moreover, you can also change the MS SQL Server’s recovery model during the process.
But you must change the database to the read-only mode for best results, and if you can, please backup the transaction log. By following this step, you can reduce many risks from the error.
Now, we will guide each step to operate the truncation of SQL Server transaction logs:
- Right-click on your desired database after opening “SQL Server Management Studio.” Please make sure that the large transaction log is coming with the database you selected.
- In the context menu, choose “Properties.”
- Change the recovery model of the database to “Simple” in the “Option” section.
- Next, open the section “tasks,” choose “shrink,” then “File” in the context menu we mentioned.
- Select “Reorganize pages before releasing unused space” from the section “Shrink action.”
- Now, you can set your preferred file’s size.
- Choose “OK.”
There are three shrink options below for you:
– Migrate the data from the specified file to another place within the same group to empty the file. Then, the computer system will remove the empty file later.
– After rearranging pages, you can release unused space and locate rows one more time to the pages your device assigned.
– Recover transaction log file’s unused space, then shrink that file to the final extent your computer allocates. With this option, you don’t have to move any data to shrink the file’s size.
At last, you can switch your database restore mode to “FULL” after successfully performing one operation above.
Instruction To Use Transact
With SQL Query Analyzer and a simple script, you can quickly finish the task. The script we showed below can work perfectly with any version of Microsoft SQL Server from 2008.
ALTER DATABASE ″YourDBName″ SET RECOVERY SIMPLE;
DBCC SHRINKFILE (″YourDBName_log″, ″Desired_size_in_MB″);
Please type down this command: DBCC SHRINKFILE(MyDatabase_Log, 4096) if you want to reduce one log file’s size to 4Gb (equals 4096 Mb).
ALTER DATABASE ″YourDBName″ SET RECOVERY FULL
Extra tip for you: Using the SQL query below to find the log file’s logical name: SELECT name FROM sys.master_files WHERE type_desc = ‘LOG’.
You can also write the command below to do a backup of the database logs for reducing the SQL transaction log:
BACKUP LOG YourDBName TO BackupDevice
Please make sure to perform a full-recovery-model backup of the database.
Now, please read carefully and use the Transact SQL script below to shrink the transaction logs and make the computer automatically turn the databases (apart from systems) to the “Easy” mode.
declare @db_name nvarchar(100)
declare cursor_size_srv cursor for
SELECT name AS DBName
where name not in (‘tempdb’,’master’,’msdb’,’model)
ORDER BY Name;
FETCH NEXT FROM cursor_size_srv INTO @db_name
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
Also, from the MS SQL properties, you can search for the “Auto Shrink” option. After enabling it, the SQL Server will check unused space periodically and shrink the log files and database’s size.
However, when it comes to typical databases, Microsoft doesn’t recommend users to use this option. Please make sure to run the process in the “Full” mode if you need to use the Auto Shrink.
Go to the Automatic section; you can turn on this option from the database parameters. All you have to do is to change the value on the parameter to “True.” After that, if more than 25% of the total size is from the unused space, there will be an automatic compression from MS SQL.
You can apply this method to any SQL Server’s supported versions (from 2005 to 2019). Among many techniques, it can quickly and efficiently shrink the logs’ size.
Nonetheless, please remember that if you follow this instruction to truncate SQL transaction logs, your device can discard every former “Full-Recovery-model” backup.
This strategy is only highly recommended in important situations, like when it’s impossible to empty the disk space via other methods.
And this is the end of our explanation for SQL server truncate log and how to shrink SQL Server Transaction Logs. Hopefully, after spending time on our article, you can find a way to easily and quickly clean disk space from the SQL Server.