Wednesday, 26 July 2017

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 proper place, then there are a lot 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 error. To check the active transactions in SQL server, use the below command:

sp_whoisactive

However, the log file could be shrinked 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: