Simple Way to Shrink Transaction Log File in SQL Server

A lot of times we come across log issues when a lot of transaction happens in the database with respect to .ldf file. At times, if the log records are not in the proper place, then there are a lot of chances to come across log file growth. Commonly seen backend transactions when the log growth could be either long-running queries, bulk data insertions, index reorganization or update statistics. Also, there are chances of log growth when a scheduled log backup runs simultaneously with insertions or updates.

Before shrinking the log file, we should keep in mind to check the log sizes by using the below command:

dbcc sqlperf(logspace)

Additionally, if a log backup is already running due to a scheduled job, then we may get the error. To check the active transactions in SQL server, use the below command:

sp_whoisactive

However, the logfile could be shrunk with the below TSQL command:

USE <Database_name>
GO
DBCC SHRINKFILE (N'<database_name>_Log' , 0, TRUNCATEONLY)

If a single time shrink doesn’t work, then take log backup and again shrink the log file. TSQL command is shown below:

BACKUP LOG <Database_Name> TO  DISK = N'<Drive_Path>.trn'
WITH TRUNCATE_ONLY, NOINIT NOFORMAT, SKIP

0 comments: