This article demonstrates How to Fix Virtual Log Files in SQL Server Transaction Log file. The steps mentioned in this article are applicable on SQL Server 2005 and higher versions. To know more about how to Identify Virtual Log File in SQL Server Transaction Log File read “How to Detect Virtual Log Files in SQL Server Transaction Log File”.
How to Fix Virtual Log Files in SQL Server Transaction Log File
It is recommended to take a Full Backup of the User Database and Restore it on to a Development or a QA Server before trying out the below mentioned steps. Once you are sure about the results then only try out the steps in a Production Environment.
Steps to reduce the number of Virtual Log Files (VLFs) to a reasonable number preferably less than 50
Step 1: – Perform Transaction Log Backup of the Database
BACKUP LOG BPO TO DISK = 'C:\Backups\BPO.TRN' GO
Step 2: – Execute DBCC SHRINKFILE Command to Truncate Log File to the Smallest Size Possible
USE BPO GO DBCC SHRINKFILE (N'BPO_log' , TRUNCATEONLY) GO
Step 3: – Modify the Transaction Log File Growth to a Larger Size Based on Anticipated Growth
USE [master] GO ALTER DATABASE [BPO] MODIFY FILE ( NAME = N'BPO_log', FILEGROWTH = 512MB ) GO
Once you modify the Transaction Log File Growth to a large size based on the anticipated growth for the time to come and then rerun DBCC LOGINFO command you will see that the number of Virtual Log Files would have come down.
Use BPO GO DBCC LOGINFO GO
Once you are satisfied with the outcome in the Development or Test Environment, schedule a downtime and implement these changes to your Production Environment.
In this article you have seen how you can fix virtual log file issue in SQL Server Transaction Log file.