How to Fix Virtual Log Files in SQL Server Transactional Log
Aug 20, 2012
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'
Step 2: - Execute DBCC SHRINKFILE Command to Truncate Log File to the Smallest Size Possible
DBCC SHRINKFILE (N'BPO_log' , TRUNCATEONLY)
Step 3: - Modify the Transaction Log File Growth to a Larger Size Based on Anticipated Growth
ALTER DATABASE [BPO]
MODIFY FILE ( NAME = N'BPO_log', FILEGROWTH = 512MB )
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.
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.
Continue Free Learning...
Geeks who read this article also read…
- How to Attach Database Without a Transaction Log File in SQL Server
- Troubleshooting SYSPOLICY_PURGE_HISTORY Job Failure in Stand Alone Instance SQL Server 2008
- Troubleshooting OLE DB Provider Microsoft.ACE.OLEDB.12.0 is not registered Error
- Troubleshooting SQL Server blocked access to procedure sp_send_dbmail
- Performance Dashboard Reports in SQL Server 2012
- Tips to Avoid Account Lockout Issues
- Encrypt Database Backups in SQL Server Using MEDIAPASSWORD Option
- Using SP_SERVER_DIAGNOSTICS Stored Procedure Quickly Gather Diagnostic Data and Health Information in SQL Server 2012
- New Features in Microsoft SQL Server 2012 for Database Administrators
- New Features in SQL Server 2012 for Database Developers
- Why an SQL Server Database from a higher version cannot be restored onto a lower version?
- How to identify if the database was upgraded from a previous version of SQL Server
- Installing SQL Server 2008 R2 on Windows Server 2008 R2
- Using Transparent Data Encryption Feature of SQL Server 2008
- New Date and Time Data Types in SQL Server 2008
- Date and Time Functions in SQL Server 2008
- Configuring Database Instant File Initialization Feature of SQL Server
- Changing the default location of SQL Server Data and Log files
- Changing Default SQL Server Backup Folder in SQL Server 2008
- How to repair a Suspect Database in SQL Server
- Steps to Rebuild System Databases in SQL Server
- How to Get Exclusive Access to SQL Server Database
- Read More SQL Server Articles…