Correct a log file with too many VLFs

The what and why of this post is explained here Transaction Log VLFs – too many or too few?. Presented here is a quick practical example of how you might correct this issue in a database log file.

As a first step you’ll need to get the logical name of your log file for the database in question.

USE your_database;

SELECT *
FROM sys.database_files;
GO

You’ll want to make sure there is no activity in the database so it doesn’t interfere with our efforts. Note this does kick everyone out of the database.

ALTER DATABASE your_database SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;

Next shrink your log file;

DBCC SHRINKFILE(log_name, 0, TRUNCATEONLY);

Now we can resize the log. Here I am growing the log in 8GB increments (ok, slightly over) so we end up with VLFs 512MB in size.

USE master
GO

ALTER DATABASE your_database
MODIFY FILE
(
	NAME = log_name,
	SIZE = 8200MB
);
GO

ALTER DATABASE your_database
MODIFY FILE
(
	NAME = log_name,
	SIZE = 16400MB
);
GO

ALTER DATABASE your_database
MODIFY FILE
(
	NAME = log_name,
	SIZE = 24600MB
);
GO

ALTER DATABASE your_database
MODIFY FILE
(
	NAME = log_name,
	SIZE = 32800MB
);
GO

Now set the database back to multi user.

ALTER DATABASE your_database SET MULTI_USER;

Inspect your log VLF structure.

DBCC LOGINFO;

You may also be interested in the following post about Auditing VLFs on your SQL Server.