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:
Post a Comment