SPROC: Backup History Report

A while back, I wrote a script to show backup throughput over time. It was intended to diagnose deteriorating disk IO performance for the backup destination. I figured it could be useful for other things as well, so I combined it with my backup history report into a single stored procedure. I hope you find this as useful as I do. Please let me know if you have any questions on it, or suggestions for improvements.

Thanks for reading,
-David.

USE [SQLAdmin];
GO

IF (SELECT OBJECT_ID('dbo.ShowCurrentBackupHistory')) IS NULL
BEGIN
    EXEC
('CREATE PROCEDURE dbo.ShowCurrentBackupHistory
            AS
            PRINT ''STUB VERSION - Replace with actual procedure.'';
            '
)
END
GO

ALTER PROCEDURE dbo.ShowCurrentBackupHistory
/*******************************************************************************
AUTHOR:
  David M Maxwell
  
DATE:
  Nov 6 2013

DESCRIPTION:
  
  Shows the last @days of backup history for all databases.

  Shows name, current recovery model, backup type (full, diff or log), finish time,
  duration in seconds, backup size in MB, throughput in MB per second, user name
  that took the backup, and the backup file destination.

DEPENDENCIES:
  None.
  
TEST EXEC:

EXEC dbo.ShowCurrentBackupHistory
   @days = 30 -- Last 30 days of history
GO

TO DO LIST:
  1.

NOTES:  
  Please send any comments or suggestions to dmmaxwell@gmail.com.

CHANGE HISTORY:
Date - Author - Change
Nov 6 2013 - David M Maxwell - Initial version. Pending feedback.

*******************************************************************************/
  
@days int = 30
AS

SET NOCOUNT ON
;

/* Convert the @days to a datetime to keep things simple. */
DECLARE @dt datetime
SELECT
@dt = dateadd(dd,-@days,getdate())

SELECT
  
DBName = bs.database_name
,CurrentRecModel = db.recovery_model_desc /* IF NULL, then DB no longer exists. */
,BackupType =
  
CASE bs.type
    WHEN
'D' THEN 'Full'
    
WHEN 'I' THEN 'Diff'
    
WHEN 'L' THEN 'TLog'
    
ELSE 'Unknown'
  
END
,DateCompleted = bs.backup_finish_date
,Duration = DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
,
DataSizeMB = CAST(ROUND(bs.backup_size / 1048576.0,2) AS numeric(10,2))
,
[MB/sec] = CAST(ROUND((bs.backup_size / 1048576.0) /
      
CASE
        
WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
        
THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
        
ELSE 1
      
END,2) AS numeric(10,2))
,
UserName = bs.[user_name]
,BackupFile = bmf.physical_device_name
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
  
ON bs.media_set_id = bmf.media_set_id
LEFT OUTER JOIN master.sys.databases db
  
ON bs.database_name = db.name
WHERE bs.backup_finish_date > @dt
ORDER BY
  
bs.backup_finish_date DESC;
GO

sp_update_job and Editing System Tables.

The initial problem: We have a particular situation that involves some scheduling. At the end of the month, we need to do some additional ETL processing for financial reporting. At that time, we want to avoid competing for resources with the other, scheduled jobs that would normally run during that time of day.

The first solution: So that we don’t compete for server resources with other jobs, we create a job that disables jobs at the end of the month, and re-enables them after the month-end processing is done.

IF DATEPART(day,
    DATEADD(day,1,CURRENT_TIMESTAMP)
   ) = 1 /* If tomorrow is the first of the month */
BEGIN
  UPDATE msdb.dbo.sysjobs
  SET enabled = 0
  WHERE name = 'Daily ETL Master'
END

The second problem: After scheduling the above code to run on the last day of the month, we open up the Job Activity Monitor and see that the jobs are disabled. However, we also notice that the jobs have run on schedule, despite being disabled. What happened?

The diagnosis: Two things went wrong here.

First, note that when the SQL Server Agent starts up, it reads the scheduled task information in from the sysjobs table, and caches it. In order to know that the system tables have changed, we need to notify the Agent that the cache needs to be refreshed. By now, we should be thinking stored procedure, instead of single statement. This is good, because SQL Server already supplies a stored procedure to do just that.

sp_update_job will take several parameters, one of which is @enabled. This will either enable or disable the job, when set to 1 or 0, respectively. The procedure will also cause the Agent to refresh it’s cache, if a cached parameter has been updated. The enabled flag is cached, so in this case, the code should read like this:

IF DATEPART(day,
    DATEADD(day,1,CURRENT_TIMESTAMP)
   ) = 1 /* If tomorrow is the first of the month */
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 0
END

Now the job’s status will be updated, and the agent’s cache will be refreshed appropriately.

Second, and this is even more important, since we’ve scheduled this code to run on the last day of the month, is there a time when the IF statement would ever evaluate false? Nope. We’ve written an IF statement that will only ever evaluate one way. Which means, this IF statement isn’t even necessary in this case.

The final solution: This job only needs to run twice; on the last and first days of the month. The SQL Server Agent lets us define both of those schedules. So, we can create two job schedules, “Last Day of Month”, and “First Day of Month”, and assign those to a job that runs the following:

IF DATEPART(day,CURRENT_TIMESTAMP)= 1 /* First of the month */
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 1
END
ELSE 
BEGIN
  EXECUTE msdb.dbo.sp_update_job
    @job_name = 'Daily ETL Master',
    @enabled = 0
END

Thanks for reading.

-David.

Script: Backup Throughput

Recently on one particular server, I noticed that backups were taking much longer than they should have. The databases or backups weren’t getting significantly larger,  so I wanted to know if there was a problem with the backup storage, and to do that I wanted to look at backup throughput over time. Here’s the query I wrote to do that.

The query makes use of two tables in MSDB, namely [backupset] and [backupmediafamily]. From [backupset], we can get the database name, start and finish times, and the size of the backup. To get the file name, we need to go to [backupmediafamily], and join on the media_set_id.  By calculating the difference in seconds between start and finish times, and converting the backup size from bytes to MB, we can get the backup throughput in MB/s.

Also, in this case, I’m filtering for backups that are either full or differential, and are taking longer than 10 minutes. This eliminates backups that only take a few seconds, since those backups will give atrificially low throughput numbers. (If I only backed up 1MB, and it took 1s, then my throughput is 1MB/s even if I could have done more.)

SELECT
bs.database_name AS DBName
,bs.backup_start_date AS DateStarted
,bs.backup_finish_date AS DateCompleted
,Duration =
DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
,bs.backup_size / 1048576.0 AS DataSizeMB
,[MB/sec] = (bs.backup_size / 1048576.0) /
CASE
WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
ELSE 1
END
,bmf.physical_device_name AS BackupFile
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.type != 'L'
AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 600
;

That will give me the backup throughput for each Full or Differential backup running longer than 10 minutes that remains in the backup history. If you want to look at averages for all databases over time, you can group by parts of the datetimes. One way to do that would be like this:

WITH BackupThroughput AS (
SELECT
bs.backup_finish_date AS DateCompleted
,[MB/sec] = (bs.backup_size / 1048576.0) /
CASE
WHEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 0
THEN DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date)
ELSE 1
END
FROM msdb.dbo.backupset AS bs
INNER JOIN msdb.dbo.backupmediafamily AS bmf
ON bs.media_set_id = bmf.media_set_id
WHERE bs.type != 'L'
AND DATEDIFF(SECOND, bs.backup_start_date, bs.backup_finish_date) > 600
)
SELECT
BackupDate = MIN(DateCompleted)
,AverageThroughput = AVG([MB/sec])
FROM BackupThroughput
GROUP BY
DATEPART(YEAR,DateCompleted)
,DATEPART(MONTH,DateCompleted)
,DATEPART(DAY,DateCompleted)
ORDER BY
DATEPART(YEAR,DateCompleted)
,DATEPART(MONTH,DateCompleted)
,DATEPART(DAY,DateCompleted)

With that information in hand, I can check to see if there was an obvious drop in backup throughput, and then investigate that.

Hope that helps.
-David.

Dealing With Disk Space Issues.

On a typical day a few months ago, I received an automated warning in my inbox that a particular server was low on disk space. This came along with the requisite “panic” emails I usually get from other areas who are also on that notification list.  I responded that I was looking into it in order to keep the email volume down. :-)

I mapped the volume in question to my local machine and fired up SpaceSniffer to get a quick read on what was taking up all the space. It was a dedicated SQL Server data file drive and the usual suspects were taking up the lion’s share of the space. (SpaceSniffer is a fantastic tool, by the way. You should check it out, if you haven’t already.)

Now, when it comes to disk space issues, there are three basic options:

1. Do nothing.  This should always be considered as an approach, though you may rarely use this as a solution. Sometimes you really don’t need to do anything.

2. Reduce the space required. You can relocate files, clean up un-needed data, etc. You can even shrink files if you understand what the consequences would be and have a plan for dealing with that.

3. Increase the space available. In this case, we were on a SAN drive that had available space left, so we could grow the drive to make more space available.

Another quick check showed that most of the data files were over 90% used, and none of them would be able to grow unless more space was made available. That eliminated option 1. There were no other files on that drive to clean up or delete, i.e., no databases to drop, so we couldn’t reduce the amount of space required for option 2. That left us with option 3: Grow the drive.  This was an online operation handled off-hours by our SAN admin, and was successful.

After resolving any issue, it’s a good idea to go back and do some root-cause analysis. What caused the disk space issue?  One of the first things I do when taking on a new client or job is to set up a monitoring job on every server that keeps a few month’s worth of history on the database files. Using the sys.dm_io_virtual_file_stats DMV I take a snapshot of the disk activity for each data file, as well as it’s size on disk, every 15 minutes.  This is then logged to a table in my administrative database on each server.

With the stats in place, I was able to put together a quick query to see which database had grown the most over the last few months. It turned out that one of the smaller databases had now become the second biggest database, all within the space of the last 8 weeks. Checking with the owner of the application, I found out a lot of new data had recently been imported, causing the database to grow by over 600%.  After that, it didn’t take much more growth on the part of the other data files to push that disk over its threshold, and send an alert.

Here are the scripts I use, in case you’re interested.

  • DDL for FileIOStatsRaw table and vw_FileIOByHour view. – Created in the administrative DB. (In my case, [SQLAdmin].)
  • GetFileIOStats stored procedure – Run this as frequently as you want and output to the FileIOStatsRaw table. – Also created in the admin DB. Will keep 90 days by default, but call it how you want in an agent job.
  • Trend Query:
SELECT 
 DBName, 
 Yr, 
 Mo, 
 CalculatedGrowthInMBPerMonth = SUM(FileGrowthBytes) / 1024.0
FROM dbo.vw_FileIOByHour 
WHERE FileType = 'ROWS' 
GROUP BY DBName, Yr, Mo 
ORDER BY DBName, Yr, Mo;
GO

How do you monitor, measure and alert on disk space usage?

Thanks for reading.

-David.

Quick Tip: SSMS Pseudo-Full Screen View

I have a number of nervous and idle habits that may potentially annoy my cube neighbors. They haven’t complained, yet. One of my habits is that when I’m working on a particularly interesting or annoying script, I will randomly click around the query window. Click, click, click. I was doing this a couple of days ago, and for some reason, I was clicking on the tab at the top of the query window, where the name of the script is.

Tabbed T-SQL Window

Normal tabbed view.

Then, I double clicked on it.

Floating Window

Ooh, that’s neat.

Ooh, that’s neat, I thought. Then I clicked on the maximize button, and this is what I got.

Almost Full Screen

Full screen, but I still have my taskbar.

Awesome. Finally, I restored the window and dragged it back into SSMS to put it back into tabbed view.

Drag To Center

Drag and drop it back where it was.

Now, let me explain: Another habit of mine is sliding the mouse to the left or right of the screen when I’m coding. Sharp-eyed readers will notice that that’s where my side panes are minimized. So when I’m trying to focus on the code, I inevitably cause the side panes to slide out in front of where I’m either reading or typing. Now, I could just put SSMS into full screen to hide the panes, but I’m not a fan of that either, because I have status icons in my taskbar I want to see. This is a perfect hybrid for me, and it even gets the SSMS toolbars out of the way. Bonus! I figured if it was useful to me, it might be useful to someone else as well. So here you go.

Thanks for reading.
-David.