Monitoring Database VLF’s

The transaction log file records all transactions and the database modifications made by each connection. Heavy application processing against the database can make the transaction log grow quickly. Each physical log file is divided into smaller logical units called virtual log files (VLFs).

If there is no auto-growth setting on the log file, the database will stop working, be marked as suspect, when you run out of space. Therefore, it is important to monitor database file space, both data and log, so this does not happen. I will present a solution to this problem later tonight.

My goal is to present the database administrator (DBA) at a small company on a limited budget a stored procedure that can detect VLFs and optionally email you the results.

I start off all my coding efforts using pseudo code. This makes sure I have a plan of attack even before I type a single stroke.

1

2

3

4

5

6

7

8

9

10

11

12

13

<span style="color: #008000;">1-User parameters

&nbsp;&nbsp;&nbsp;&nbsp;A-What action toperform(print/email/monitor)?

&nbsp;&nbsp;&nbsp;&nbsp;B-Max VLF's allowed.

&nbsp;&nbsp;&nbsp;&nbsp;C - Email distribution list.

&nbsp;&nbsp;&nbsp;&nbsp;D - Optional email profile name.

2 - Get a list of databases that are on-line.

3 - For each database, grab the log information.

4 - Database exceeds the max VLF's,mark row asY.

5-Action='print',show data on output screen.

6-Action<>'print'andcount(rows markedY)>0,email VLF html report.

7-Action='monitor',save raw data to1yr history table.

</span>

Here are some interesting things to notice. I built upon the script that David Levy created. His script had a bug in which it errors out when a database is off-line. Also, it does not work for SQL Server 2012 since the DBCC LOGINFO undocumented command changed the results of it’s output.

I chose to use temporary variables instead of tables since they go away without dropping. Error checking has been added so that any unplanned errors will be detected.

I did not check that database mail is installed or a valid default email profile is setup. This is your responsibility to make sure those requirements are meet.

Last but not least, this is a complete stored procedure that you can schedule on each SQL Server instance. Call the stored procedure with the monitoring mode option to retain one years worth of history. I suggest running it once a week to keep an eye on VLF’s.

A sample select from history table with the results displayed in the SQL Server Management Studio (SSMS) output window.

1

2

3

4

<span style="color: #008000;">--Show me the history data

SELECT *FROM[msdb].[dbo].[tbl_Monitor_Vlfs]

</span>

This is a sample call to display the results to a SSMS output window. It shows all databases, their virtual log file settings, and which ones exceeded the threshold.

1

2

3

4

5

6

<span style="color: #008000;">--Just show me the results

EXEC[msdb].[dbo].[usp_monitor_vlfs]

@var_option='print',

@var_max_vlfs=50

</span>

In summary, VLF’s can really slow down a system since data has to be written ahead to the log file before writing to the data file.

By making sure that you have a correctly sized log file that does not grow between transaction log backups, this problem can be avoided. Any data or log file growth is expensive in terms of processing and blocking. Growing the initial transaction log file ahead of time in larger blocks allows for a uniform block size.

See Brad’s slides for more details. I had the privilege to see this presentation at Pass Summit 2011. I hope you enjoy the enclosed code. If you find any issues with the script, please email me.