SQL Server Articles, SQL Server Tips, SQL Server Tutorials, SQL Server Tuning, SQL Server DBA, SQL Server Basics, Training, etc - MyTechMantra.com

How to Fix Virtual Log Files in SQL Server Transaction Log

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
DBCC SHRINKFILE SQL Server DBCC Command

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
Fix Virtual Log Files in Transaction 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.

Chetna Bhalla

LESS ME MORE WE

Chetna Bhalla, the founder of MyTechMantra.com, believes that by sharing knowledge and building communities, we can make this world a better place to live in. Chetna is a Graduate in Social Sciences and a Masters in Human Resources and International Business. She is an alumnus of Vignana Jyothi Institute of Management, Hyderabad, India. After graduation, Chetna founded this website, which has since then become quite a favorite in the tech world. Her vision is to make this website the favorite place for seeking information on Databases and other Information Technology areas. She believes that companies which can organize and deploy their data to frame strategies are going to have a competitive edge over others. Her interest areas include Microsoft SQL Server and overall Database Management. Apart from her work, Chetna enjoys spending time with her friends, painting, gardening, playing the violin, and spending time with her son.

Newsletter Signup! Join 15,000+ Professionals




Be Social! Like & Follow Us

Follow us

Don't be shy, get in touch. We love meeting interesting people and making new friends.

Advertisement