How to Fix Virtual Log Files in SQL Server Transactional Log


Aug 20, 2012

Introduction

This article demonstrates How to Fix Virtual Log Files in SQL Server Transactional 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 refer the following article “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.

Follow the below mentioned steps to reduce the number of log files 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

How to Fix Virtual Log Files in SQL Server Transactional Log File

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

Performance Improvement Tips: - Configuring Database Instant File Initialization Feature of SQL Server to improve the time taken to grow data and log files.

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

How to Fix Virtual Log Files in SQL Server Transactional Log File

Once you are satisfied with the outcome in the Development or Test Environment, schedule a downtime and implement these changes to your Production Environment.

Conclusion

In this article you have seen how you can fix virtual log file issue in SQL Server Transaction Log file.

Continue Free Learning...

  • Please leave below your valuable feedback for this article.
  • Feel Free to refer this article to your friends and colleagues using the below “Share this Article” option.
  • Do subscriber to our News Letter to continue your free learning.
  • Don’t forget to Like Us on Facebook and do follow us on Twitter for latest updates.

Share this Article

Geeks who read this article also read…



Follow @MyTechMantra on Twitter
We're on Facebook
Bookmark and Share

"Receive newsletters and special offers about SQL Server, BizTalk and SharePoint from MyTechMantra. We respect your privacy and you can unsubscribe at any time."