Steven Wang - 汪上洲

Steven is a data scientist and a Microsoft Certified Solution Expert on Data Platform, MCSA on SQL server 2008/12 and MCITP on BI Developer, Database Developer and DBA.

He is very passionate about data insights, machine learning and MS SQL server technology and business intelligence and has designed and implemented a number of large scale enterprise BI solutions in a variety of industries.

Steven is an active SQL server community participant. He answers questions at MSDN SQL server forum and speaks at events like TechEd, CodeCamp, SQL User Group etc.

Steven is currently head of data science at QuintilesIMS at Sydney Office.

In SQL server, there are often different ways to achieve the same goal. This is also true for monitoring the database log size and its usage.

1. DBCC SQLPERF(LOGSPACE)

Typically, we use DBCC SQLPERF(LOGSPACE) to monitor the log size and percent of space used for all databases. This script is very simple and straightforward to use. However, if you want to progmatically use the information output by the DBCC command, then there is a little bit work to do. The script below is one example which can give you the log space information programtically by using DBCC SQLPERF(LOGSPACE):

The second and less commonly used technique to monitor the log space usage is to use the sys.dm_os_performance_counters dynamic management view. This view keeps the records of the log file size and also data file size as counter name for all databases. As I only concern the log file size for this purpose, I use the script below to monitor the log space usage:

If you only concern one database then you can limit the database name in instance_name. Although I calculated the log usage percent, it has indeed a count name 'Percent Log Used' for the sys.dm_os_performance_counters view, but it has no decimal points for the percent value.

3. sys.database_files

The sys.database_files catalog view has information for data files and log file of the current refferenced database. This is different with the preceding 2 ways that can return back the log information for all databases. As there is no space used information in this view, we need to use the fileproperty function to get the log spaced used value as below:

Although this script is for log space usage only, it can also be used to monitoring data files size by taking off the where clause.

4. sys.dm_db_log_space_usage (SQL 2012 only)

This dynamic management view is not even documented in the BOL, but this is very simple view. As the view return back the log size in bytes, it may be more useful if converting the bytes to MBs as below: