mssqltips logo

SQL Server Database Activity Based on Transaction Log Backup Size

By:   |   Updated: 2019-08-01   |   Comments   |   Related: More > Monitoring

Problem

There are many ways to monitor a SQL Server including using SQL performance counters, watching file growth or using 3rd party tools.  Have you ever considered what transaction logs can tell you about the activity on your SQL Server? Another way to think of this is, like looking at traffic patterns or trying to find your database rush hour.  On one system that I supported I was aware of performance problems that occurred on Fridays, specifically around weekly billing processes.  However, when I analyzed log growths, I found something unexpected.  The system had significantly more log activity earlier in the week, primarily on Monday and Tuesdays.  When I dug deeper, I found that the log activity earlier in the week was based on Order Entry processes.  The results of my analysis were that the Order Entry process was more optimized and consumed less reads.  Whereas the Invoice Processing at the end of the week was made up of a less optimal higher read process.  The analysis of the transaction log was interesting because it helped me visualize the weekly activity characteristics of my database and showed me something unexpected.

Solution

How to analyze SQL Server Activity based transaction log backup size to get a visual characteristic of your database or SQL server.

Analyzing SQL Server Database Transaction Log Activity

To analyze the SQL activity based on transaction size run the following queries. In the examples we will look at the entire SQL Server and individual database examples.  This is good to do when you have multiple database with high activity. Note that this solution is only effective on databases that are in the Full Recovery model and have regular transaction log backups.  The grain that you analyze is dependent on the frequency of the transaction log backups.  Generally, full recovery databases have transaction log backups scheduled every hour or more frequent than hourly.  For this exercise the databases I will analyze have transaction log backups every hour or more.

Solution 1 – Activity Based on Transaction Log Size – Database Level

In this example I am using the SUM aggregate go get total transaction log backup sizes in MB rolled up by hour for each day of the week.  This has 2 parameters: (1) @NumWeeks which I generally set to 8 weeks; (2) @Database which can be set to analyze a single database or set to NULL to analyze all the database on a server with transaction log backups.

--Database LEVEL: SUM
--SUM: May be large for a large date range for analysis. this is my preferred over an 8 week period.
DECLARE @NumWeeks INT = 8 --<< SET the number of weeks to analyze.
DECLARE @Database nvarchar(100) = 'YourDBName'
SELECT 
   @@servername as 'Server', --For all full recovery model DBs 
    a.database_name,
   datepart(Hour,a.backup_start_date) as 'Hour',
   convert(int,SUM(CASE WHEN charindex('Sunday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Sun_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Monday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End))  as 'Mon_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Tuesday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Tue_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Wednesday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Wed_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Thursday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Thu_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Friday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Fri_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Saturday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)) as 'Sat_size(MB)'
   ,MIN(a.backup_start_date ) as 'From:'
   ,MAX(a.backup_start_date ) as 'To:'
   ,COUNT(*) as 'NumberLogBackups'
FROM msdb.dbo.backupset a (NOLOCK)
WHERE a.type = 'L' --log backups
AND (a.database_name = @Database --Target Database 
      OR @Database IS NULL)
AND a.backup_start_date >= DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE())-1*@NumWeeks,-1)  --Note: -1 Starts us at Sunday.
AND a.backup_start_date < DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE()),-1)  
GROUP BY a.database_name, 
  datepart(Hour,a.backup_start_date)
ORDER BY a.database_name, 
  datepart(Hour,a.backup_start_date)
GO

Activity Based on Transaction Log Size – Database Level Results

In this sample the result show steady database transaction activity Monday through Friday between 9 am and 5 pm with a potential maintenance or batch job running Friday evening 11 pm and Saturday morning at 5 am.

Database Results

Solution 2 – Activity Based on Transaction Log Size – Server Level

In this example the roll up is at the Server Level and includes all databases in Full Recovery model with transaction log backups. Here I have 1 parameter, @NumWeeks which I generally set to 8 weeks.  Also, in this example I add a twist, doing the SUM of all database transaction log backup sizes in MB divided by the number of weeks in the analysis yielding more of an average rolled up by hour for each day of the week. With the smaller values it is easier to see the patterns.

--Server Level: 
--Uses Sum / Number of week 
DECLARE @NumWeeks INT = 8
SELECT  
   @@servername as 'Server', --For all full recovery model DBs 
   datepart(Hour,a.backup_start_date) as 'Hour',
   convert(int,SUM(CASE WHEN charindex('Sunday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Sun_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Monday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks)  as 'Mon_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Tuesday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Tue_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Wednesday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Wed_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Thursday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Thu_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Friday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Fri_size(MB)',
   convert(int,SUM(CASE WHEN charindex('Saturday',datename(weekday,a.backup_start_date)) > 0 then ceiling(a.backup_size/1048576.00) End)/@NumWeeks) as 'Sat_size(MB)'
   ,MIN(a.backup_start_date ) as 'From:'
   ,MAX(a.backup_start_date ) as 'To:'
   ,COUNT(*) as 'NumberLogBackups'
FROM msdb.dbo.backupset a
WHERE a.type = 'L' --log backups
AND a.backup_start_date >= DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE())-1*@NumWeeks,-1) 
AND a.backup_start_date < DATEADD(WEEK, DATEDIFF(WEEK,-1,GETDATE()),-1)  
GROUP BY datepart(Hour,a.backup_start_date)
ORDER BY datepart(Hour,a.backup_start_date)
GO

Activity Based on Transaction Log Size – Server Level Results

In this sample’s result we show minimal steady activity during business hours with increasing database transaction activity from Monday at 2 am increasing through Friday from 2 am to 6 am with potential maintenance or batch job activity on Saturday evening from 7 pm through 9 pm. To determine what is happening during the early morning activity I may look at SQL jobs to see what is running at this time and if any jobs tend to increase in runtime as the week progresses. 

Server Results

Wrap Up

I hope you find this demo useful in uncovering your database traffic patterns.  Most databases that I have looked at show a different visual of their activity patterns.  You may discover that your Index Maintenance window turns out to be your high activity based on transaction log entry.  One application of this analysis is when you are looking for a window of inactivity to schedule new Jobs that may be resource intensive. Your results may be skewed if the database is changed to Bulk Log mode during index operations. Hopefully you find this interesting and helpful in uncovering information about your database activity that you were not fully aware of.

Next Steps


Last Updated: 2019-08-01


get scripts

next tip button



About the author
MSSQLTips author Jim Evans Jim Evans is an IT Manager with Stericycle who has managed DBAs, BI Developer, and Data Management teams over the past 10 years.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools