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

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.

Script: Can My Databases Grow?

Most of us try to proactively manage our available disk space as much as we can, but sometimes you’re not able to predict when a file will grow. We usually leave auto-grow on as a safeguard for this. One of the problems I’ve run into in the past is. having a very large growth setting or lack of disk space create a situation where there isn’t enough space on disk for a file to grow. There may be enough “free space” on the disk for whatever your monitoring threshold is, but maybe not enough for a growth or two.

Here’s a stored procedure you can use to check to see if any of the databases on your server would fail to auto-grow. It requires dbmail be set up. Also note that for this, and most scripts I will usually post, I have a separate administrative database that I put this in. Let me know if you have any feedback on this, or if you get any use out of it.

Thanks for reading.

-David.
USE [SQLAdmin];
GO

CREATE PROCEDURE AutogrowWarning
/*******************************************************************************
AUTHOR:
David M Maxwell

DATE:
Apr 24 2012

DESCRIPTION:
Checks to see if sufficient disk space is available for autogrow. If not, sends a warning.

DEPENDENCIES:
None

TEST EXEC:

EXEC dbo.AutogrowWarning
@dbaemail = 'david.maxwell@companyname.com'
GO

TO DO LIST:
1. Nothing at this time.

NOTES:
Please send any comments or suggestions to david.maxwell@companyname.com.

CHANGE HISTORY:
Date - Author - Change
Apr 24 2012 - David M Maxwell - Initial Version

*******************************************************************************/
@dbaemail varchar(500) = NULL -- DBA email address to receive warnings.
AS

SET NOCOUNT ON;

/* Test Parameters.  Run everything from here down to test. */
--DECLARE @dbaemail varchar(500), @msgquery nvarchar(4000)
--SELECT
--  @dbaemail = 'david.maxwell@companyname.com'

/* Parameter check */
IF (SELECT @dbaemail) IS NULL
BEGIN
RAISERROR
('ERROR: Email recipient address must be provided.',16,1)
--GOTO EXITPROC
END

/* Create temp tables for comparison. */
IF (SELECT object_id('tempdb.dbo.#DiskFree')) IS NOT NULL
DROP TABLE #DiskFree

CREATE TABLE #DiskFree (
DriveLetter char(1) NOT NULL,
FreePages int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#NextAutoGrow')) IS NOT NULL
DROP TABLE #NextAutoGrow

CREATE TABLE #NextAutoGrow (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL
);

IF (SELECT object_id('tempdb.dbo.#AutoGrowFail')) IS NOT NULL
DROP TABLE #AutoGrowFail

CREATE TABLE #AutoGrowFail (
DBName varchar(100) NOT NULL,
LogicalName varchar(100) NOT NULL,
FileType CHAR(10) NOT NULL,
DriveLetter char(1) NOT NULL,
Growth int NOT NULL,
FreePages int NOT NULL
);

/* Create perm table, if it does not exist. */

IF NOT EXISTS (SELECT name FROM sys.tables WHERE name = 'AutoGrowWarnings')
BEGIN
CREATE TABLE
AutoGrowWarnings (
WarningMessage nvarchar(max)
)
END

TRUNCATE TABLE dbo.AutoGrowWarnings;

/* Get the amount of disk space free on each volume. */
INSERT INTO #DiskFree
EXEC xp_fixeddrives;

/* Convert to pages for easier comparison, since master_files.size
is in pages. */
UPDATE #DiskFree
SET FreePages = FreePages * 128;

/* Get the next autogrow for each DB per drive letter. */
INSERT INTO #NextAutoGrow
SELECT
db_name(database_id),
name,
type_desc,
substring(physical_name,1,1),
CASE is_percent_growth
WHEN 1 THEN (size / growth)
ELSE growth
END AS Growth
FROM master.sys.master_files
WHERE Growth > 0

/* Compare the next autogrow to the disk free and create
a list of files that autogrow would fail on, due to
insufficient disk space. */
INSERT INTO #AutoGrowFail
SELECT
NAG.DBName,
NAG.LogicalName,
NAG.FileType,
NAG.DriveLetter,
NAG.Growth,
DF.FreePages
FROM #NextAutoGrow NAG
INNER JOIN #DiskFree DF
ON NAG.DriveLetter = DF.DriveLetter
WHERE NAG.Growth > DF.FreePages;

/* If the list is not empty, email it to the DBA. */
IF (SELECT COUNT(*) FROM #AutoGrowFail) > 0
BEGIN

INSERT INTO dbo.AutoGrowWarnings
SELECT 'WARNING: As of ' + CONVERT(varchar(24),GETDATE(),120) + ',' + DBName + '.'
+ LogicalName + ' requires ' + CONVERT(nvarchar(24),growth)
+
' pages of free space for autogrow and only ' + CONVERT(nvarchar(24),FreePages)
+
' are available on drive ' + DriveLetter + '.'
FROM #AutoGrowFail

EXEC msdb.dbo.sp_send_dbmail
@recipients = @dbaemail,
@subject = 'WARNING: Possible Auto-Grow Failure',
@query = 'SELECT * FROM SQLAdmin.dbo.AutoGrowWarnings'
END

EXITPROC:

T-SQL Toolbox – 30 Day Backup History

I keep a little toolbox of T-SQL scripts handy in Simplenote. There are a couple of plug-ins for my favorite browsers, or smartphones if you have one.  I don’t, but that’s a whole other post for a whole other blog. With that, I’m never far from a useful script or two.  One that I like to have around, especially when I’m in a new environment is my “30 Days of Backup History” scriptlet.  It does just what it says on the label – shows you the backup history for all the backed up databases on the server for the last 30 days.  So here it is. Feel free to take this, modify, rearrange, chuck it in a stored procedure, etc… If you repost it somewhere else, I just ask for a token acknowledgement.

/* ADMIN: Backup history for the last 30 days. */
DECLARE @dt datetime = dateadd(dd,-30,getdate());

SELECT
  DBName = bs.database_name
 ,BackupType =
  CASE bs.type
    WHEN 'D' THEN 'Full'
    WHEN 'I' THEN 'Diff'
    WHEN 'L' THEN 'TLog'
  ELSE 'Unknown'
  END
 ,DateCompleted = bs.backup_finish_date
 ,DataSize = bs.backup_size
 ,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
WHERE bs.backup_finish_date > @dt
ORDER BY bs.backup_finish_date DESC;
GO

Thanks for reading,

-David.