T-SQL Tuesday #33 - Monitoring Log File Size Without Logging On To The Server

This month’s T-SQL Tuesday is hosted by Mike Fal (Blog | Twitter). The topic is TrickShots and the purpose of this topic is to highlight a cool trick or process and explained what you learned from this trick.

I encountered my first log file issue in my early days as a DBA using SQL Server 2005. While I was on vacation, I received a phone call from the company's system administrator because he noticed the reduced free space on one of the drives of the database server. When I logged on to the server I immediately noticed the large log files that were not present a few days ago. After fixing the issue one of the main questions that was on my mind was "How can I know the log file size without logging on to the server to check the logs folder?".

I did some research and came across the T-SQL Statement:

DBCC SQLPERF (LOGSPACE)

This provides transaction logs usage statistics for all the databases on the instance (for more information see MSDN: DBCC SQLPERF)

I found the information very interesting and thought it would be a good idea to store the data everyday and look for some trend so that I can predict the size of the log files.  The following code was used to store it in a table:

INSERT INTO Information(DatabaseName,LogSize,LogSpaceUsed,Status)

EXEC ('DBCC SQLPERF (LOGSPACE)')

Around the time I was working on this issue, I was also now getting familiar with SQL Server 2005 Reporting Services and anything that was being stored in a table was suitable test information for a report. This resulted in a report with the data from the 'Information' table but I had to run the script and the report manually.

Using the little knowledge that I had of SQL Server 2005 as well as the excitement to try out new things, I decided to use a stored procedure as well as the subscription capability of Reporting Services to automate the process. The stored procedure was created to execute the insert script and also returned the inserted data every time the report was executed. The subscription capability allowed me to schedule when the report would be executed, how it would be delivered and the specific format of the report. The end result of this process enabled me to get my log size information in an Excel file via email. A great advantage of this was the fact that I could receive the email on my phone and check the file even if I was out of office or away from my machine.

Subset of the Log File Information in the Excel File

While this process could have been done more easily back then and much more easily now, it was a great way for me to learn and test the new features of SQL Server 2005. Another benefit of this process was the fact that the information that I received from the DBCC statement encouraged me to learn and understand more about log files.