• Skip to main content
  • Skip to primary sidebar

DBA Diaries

Thoughts and experiences of a DBA working with SQL Server and MySQL

Monitoring SQL Server Transaction Log Growth Using DBCC SQLPERF(logspace)

October 13, 2013 by Andy Hayes 5 Comments

monitor transaction log growth with dbcc sqlperf(logspace)For this article, I am using DBCC SQLPERF(logspace) to get size information about the transaction logs. I hope you will find this post useful in helping to monitor the growth of your transaction logs.

The idea here is to capture and store the growth information at set intervals. This can provide a way to report on how much the logs are growing by and when. Reports are then delivered by an automated process.

If you issue DBCC SQLPERF(logspace), you will see output similar to the following:

dbcc sqlperf(logspace)

The output is self explanatory where for each database you can see the size of the transaction log and how much of it is in use currently.

So this is useful information on its own and even more useful if it can be captured on a regular basis and reported on.

So I wrote some T-SQL to do this and wrapped it up inside a stored procedure.

Firstly I will create a table to store the output of DBCC SQLPERF(logspace).

[sourcecode language=’sql’]
CREATE TABLE dbo.TransLogMonitor
(
LogID INT PRIMARY KEY IDENTITY(1,1),
LogDate DATETIME NOT NULL DEFAULT(GETDATE()),
DatabaseName VARCHAR(100) NOT NULL,
LogSizeMB DECIMAL(18, 2) NOT NULL,
LogSpaceUsed DECIMAL(18, 2) NOT NULL,
[Status] INT NOT NULL
)

CREATE INDEX IX_LogDate ON TransLogMonitor(LogDate);
[/sourcecode]
Now this is the bit where we capture the output.
[sourcecode language=’sql’]
INSERT INTO TransLogMonitor(DatabaseName, LogSizeMB, LogSpaceUsed, [Status])
EXEC (‘DBCC SQLPERF(logspace)’)
[/sourcecode]

DBCC execution completed. If DBCC printed error messages, contact your system administrator.
(5 row(s) affected)

Now run the code and then have a look at the data in your new table and it will look very similar to the output from running DBCC SQLPERF(logspace) to screen but with a couple of extra columns for the primary key values and the date that the data was inserted. The LogDate values are obviously very valuable enabling you to report on the data within any date range.

So lets say I wanted a report made available each day which gave me a comparison between yesterday’s and today’s growth, you could write something like this having the data collector running once a day.
[sourcecode language=’sql’]
DECLARE @Yesterday DATE = CONVERT(DATE, GETDATE()-1);
DECLARE @Today DATE = CONVERT(DATE, GETDATE());

SELECT COALESCE(Y.DatabaseName,T.DatabaseName) AS DatabaseName
, T.LogSizeMB AS TodayLogSizeMB
, Y.LogSizeMB AS YestLogSizeMB
, T.LogSizeMB – Y.LogSizeMB AS SizeMBDiff
, T.LogSpaceUsed AS TodayLSpaceUsed
, Y.LogSpaceUsed AS YestLSpaceUsed
, T.LogSpaceUsed – Y.LogSpaceUsed AS UsedDiff
FROM dbo.TransLogMonitor Y
FULL OUTER JOIN dbo.TransLogMonitor T ON T.DatabaseName = Y.DatabaseName
WHERE CONVERT(DATE, Y.LogDate) = @Yesterday AND CONVERT(DATE, T.LogDate) = @Today
ORDER BY TodayLogSizeMB DESC
[/sourcecode]

Returning this output:

dbcc sqlperf(logspace)

Delivering the DBCC SQLPERF(logspace) report

Providing that you are running a version of SQL Server that is able to send email then you could deliver the report via this method. This example has attached the output as a text file but you can also format it up nicely as HTML if you want to.
[sourcecode language=’sql’]
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘DB1’
, @recipients = ‘your smtp email address goes in here’
, @subject=’Transaction Log Space Consumption Report’
, @query = ‘EXEC DBName.dbo.usp_stored_procedure_name’
, @attach_query_result_as_file = 1
[/sourcecode]

To wrap this up….

Once you have the code written, it could be scheduled to your preference. As I mentioned, this example is to run daily and compares yesterday and today but you could run it hourly if you wanted to and then write some T-SQL to analyze the hours of the day when you get the most growth. You could write daily reports for the whole week, something to report on the month etc. It could be a great report to help with capacity planning.

In order to deliver this, you could send the report by scheduling with SQL Server Agent, an SSIS package, Reporting Services or SQLCMD called from a batch file using windows task scheduler.

It’s really up to you 🙂

References

DBCC SQLPERF – link to documentation is here
sp_send_dbmail – link to documentation is here

Related Posts:

  • How to fix "conversion failed when converting date and/or time from character string"
    How to fix "conversion failed when converting date…
  • sql grouping sets
    Using SQL GROUPING SETS for Multiple GROUP BY…

Filed Under: All Articles, SQL Server Administration Tagged With: dbcc, sql server, transaction logs

About Andy Hayes

Andy Hayes is a DBA working with SQL Server since version 7.0. He has a wonderful wife and two beautiful children. He loves database technology, playing cricket, and blogging. He is passionate about sharing his experiences as a DBA and learning more to further his understanding and knowledge. You can follow me on Twitter, check out my Facebook page or follow me on Google+

Reader Interactions

Comments

  1. Magnus says

    February 1, 2014 at 9:15 pm

    Hi, isn´t this necessary to run exactly before the translog backup to get accurate data?

    Reply
    • Andy Hayes says

      February 2, 2014 at 9:26 am

      Hi Magnus

      Of course, instead of a daily summary, you can also run this more frequently and around your backup schedule to obtain statistics more aligned with changes to the size of and amount of free space in the log file. The numbers collected could be used to aggregate the growth over the course of the day for example.

      Thanks for your comment, all the best.

      Andy

      Reply
  2. Vimal Kr Prajapati says

    August 6, 2014 at 6:49 am

    Hi Andy,

    Great article on DBCC SQLPERF(logspace). Just want to ask something as I’m new to SQL server so I don’t have much idea about same. When we execute DBCC SQLPERF(logspace) the last column in result is “Status”. What is this “status” all about? Thanks in advance!!!

    Reply
  3. Johnson says

    May 19, 2016 at 10:29 am

    Great Post DBCC SQLPERF seems very useful after reading this article, i have found another helpful post. see here: atdhebuja.wordpress.com/2015/12/01/how-to-view-sql-server-transaction-log-file/

    Reply
  4. mickael says

    February 22, 2017 at 2:04 pm

    Usefull thanks

    Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Categories

  • All Articles (84)
  • Career Development (8)
  • MySQL Administration (18)
  • MySQL Performance (2)
  • SQL Server Administration (24)
  • SQL Server News (3)
  • SQL Server Performance (14)
  • SQL Server Security (3)
  • SQL Tips and Tricks (21)

Top 10 Popular Posts

  • Using sp_change_users_login to fix SQL Server orphaned users
  • MySQL SHOW USERS? – How to List All MySQL Users and Privileges
  • How to shrink tempdb
  • How to Transfer Logins to Another SQL Server or Instance
  • How to Delete Millions of Rows using T-SQL with Reduced Impact
  • T-SQL – How to Select Top N Rows for Each Group Using ROW_NUMBER()
  • New T-SQL features in SQL Server 2012 – OFFSET and FETCH
  • How to Kill All MySQL Processes For a Specific User
  • Using exec sp_who2 to help with SQL Server troubleshooting
  • How to fix “conversion failed when converting date and/or time from character string”

Recent Posts

  • How to fix “conversion failed when converting date and/or time from character string”
  • Using SQL GROUPING SETS for Multiple GROUP BY Queries in a Single Query
  • How to Setup MySQL Master Master Replication
  • How To Use SQL to Convert a STRING to an INT
  • How to set up MySQL Replication Tutorial

Search

Connect

  • Twitter
  • Facebook
  • RSS

About

  • Cookie Policy
  • Disclaimer
  • About
Copyright ©