Managing Database Transaction Log File

Hey guys, In this blog I am going to explain you about “How to manage Database Transaction Log File”

This is the very common task which any DBA does regularly.

Reason behind doing Transaction-Log file shrink...

1. Some time because of low disk space on T-log disk.
2. Log-backup job issue. Sometime what happen because of log backup job frequency is not proper because of that DB log file keep growing to accommodate new transaction.
3. High Availability DB issues.

Method to shrink log file...
1. Identify the DBs which are having huge T-log file size.
2. Check the reason behind huge log file. Here you can check VLF status. Use below script...

select the DB which is having huge T-log file

use DB_Name

--Find the VLF status. If all are having status 0 then directly, we can shrink the log file. But if we are seeing there are VLF which are status 2 its mean that it waiting for log backup.
Then trigger a log backup for the DB. Once it gets complete again check VLF status.
--Check VLF status

DBCC loginfo

3. If you are seeing, in the DB there are all the VLFs having status 0 then run shrink command.

--select the DB which is having huge T-log file
use DB_Name

--Shrink command

DBCC SHRINKFILE('Logical_Name_of_DB_LogFile',target_size_of_log_file_in_MB);