Blogs

I haven’t blogged much since getting back from vacation.  It seems like a good restart after the two week hiatus would be to address an issue that has arisen countless times.  And while addressing it, I’ll put it into the DBADiagnostics database that I’ve blogged about a few times before.

Sudden File Growth

In a number of SQL Server environments that I’ve worked in there either isn’t a method for monitoring file size or the process is more of a rubber stamp morning check.  The DBA gets in and looks at the size of the files and if there isn’t a log of changes to update nothing is really done and changes aren’t investigated.

In most cases this won’t be a problem.  For instance, if you’ve gone out and pre-grown your data and log files to appropriate sizes then nothing will grow and there is nothing to see here.

In cases were the unexpected happens, though, database files can and will grow.  And in the worst of these cases, which only occur at night or on vacations, the files will grow to a point where there is no longer any disk space available.  And if your annual review is next week, this will happen to the log file and force your database offline.

Knowing Is Half the Battle

In these types of situations, I like to recall the last couple minutes of the GI Joe epos ides that I watched as a kid.  They almost always ended with the quote, “Knowing is half the battle!  Yo, Joe!”.  If I know that a file growth has happened then I can do something about it.

Now the best case is to know, monitor, and plan for upcoming file growths.  This is what might be called a best practice and if you’re not doing it I’d really recommend putting a process in place.  But we need to be prepared for the unexpected.  And even in the most best, rock solid environment, I’d recommend a file growth monitoring process.

The Solution

This procedure for the DBADiagnostics database differs slightly from previous alerts.  Instead of sending out an e-mail that aggregates all of the changes an error is raised for each file change in question.  This method was selected because this allows the process to be tool agnostic.

If the client has a log file monitoring process, then the error can be picked up that way.  Or if all of the alerts are generated from the SQL Server instances, then a SQL Agent Alert can be created to notify people about the issue.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Monitor')
    EXEC('CREATE SCHEMA [Monitor] AUTHORIZATION [dbo]')
GO

IF OBJECT_ID('Monitor.FileGrowth') IS NULL
BEGIN
    CREATE TABLE [Monitor].[FileGrowth]
        (
        DatabaseName sysname NOT NULL,
        DatabaseFileName nvarchar(260) NOT NULL,
        FileSizeMB decimal(18,3) NOT NULL
        CONSTRAINT PK_MonitorFileGrowth PRIMARY KEY(DatabaseName, DatabaseFileName)
        )
END

IF OBJECT_ID('Alert.FileGrowth') IS NOT NULL
    DROP PROCEDURE [Alert].[FileGrowth]
GO

/*================================================================================
Procedure:    [Alert].[FileGrowth] 
Author:        Jason Strate
Date:        2007-11-14

Synopsis:
    Procedures monitors the size of each file for all user databases and tempdb.  In
    the event of file growrh for any of the monitored databases, a error is raised
    that can be captured through either tools monitoring SQL Servers log files or 
    through SQL Agent Alerts.
         

================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[FileGrowth] 

As

SET NOCOUNT ON

-- Validate that necessary error message exists
IF NOT EXISTS (SELECT * FROM master..sysmessages WHERE error = 70000)
    EXEC master..sp_addmessage  @msgnum = 70000, @severity = 12, @with_log = 'true', 
        @msgtext = 'File growth has occured in the database %s on the file %s.  The size has increased from %d to %d.  If the file growth was unplanned, please review for unexpected issues.', @replace = 'REPLACE'

-- Insert files for each database that are not currently being watched
INSERT INTO [Monitor].[FileGrowth] (DatabaseName, DatabaseFileName, FileSizeMB)
SELECT d.name, mf.name, CAST(mf.size as float)*8/1024
FROM sys.databases d
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id
    LEFT OUTER JOIN Monitor.FileGrowth fg ON d.name = fg.DatabaseName AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND fg.FileSizeMB IS NULL

-- Delete databases that no longer exist
DELETE FROM Monitor.FileGrowth 
FROM Monitor.FileGrowth fg 
    LEFT OUTER JOIN sys.databases d ON fg.DatabaseName = d.name
WHERE d.name IS NULL

DECLARE @FileGrowthDelta table
    (
    DatabaseName sysname
    ,DatabaseFileName nvarchar(260) 
    ,OldFileSizeMB decimal(18,3)
    ,NewFileSizeMB decimal(18,3)
    )

-- Update files that changed size and output delta rows
UPDATE fg
SET FileSizeMB = CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))
OUTPUT INSERTED.DatabaseName, INSERTED.DatabaseFileName, INSERTED.FileSizeMB, DELETED.FileSizeMB
INTO @FileGrowthDelta
FROM Monitor.FileGrowth fg 
    INNER JOIN sys.databases d ON d.name = fg.DatabaseName 
    INNER JOIN sys.master_files mf ON d.database_id = mf.database_id AND mf.name = fg.DatabaseFileName
WHERE d.state_desc = 'ONLINE'
AND source_database_id IS NULL
AND d.name NOT IN ('model', 'master', 'msdb')
AND FileSizeMB <> CAST(CAST(mf.size as float)*8/1024 as decimal(18,3))

--Declare variables section for triggering error event
DECLARE @DatabaseName sysname
    ,@DatabaseFileName nvarchar(260) 
    ,@OldFileSizeMB int
    ,@NewFileSizeMB int

--For each database name in sysdatabases
DECLARE ALTER_FILE_GROWTH_CURSOR CURSOR LOCAL FAST_FORWARD FOR 
    SELECT DatabaseName 
        ,DatabaseFileName 
        ,OldFileSizeMB 
        ,NewFileSizeMB
    FROM @FileGrowthDelta

OPEN ALTER_FILE_GROWTH_CURSOR
FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB

WHILE @@FETCH_STATUS = 0
BEGIN
    RAISERROR(70000, 0, 1, @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB) WITH LOG
    FETCH NEXT FROM ALTER_FILE_GROWTH_CURSOR INTO @DatabaseName, @DatabaseFileName, @OldFileSizeMB, @NewFileSizeMB    
END

CLOSE ALTER_FILE_GROWTH_CURSOR
DEALLOCATE ALTER_FILE_GROWTH_CURSOR
GO

EXEC [Alert].[FileGrowth]

In the next post, I’ll outline the process that I use to check to see if there are any files that may need be running out of available space.

If you’ve been looking for some free training on SQL Server Integration Services, I’ve got the answer for you.  The details are below and you can register for the event here.

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

Speaker: Jason Strate of Digineer, Inc.
Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

If you are still trying to make plans for tomorrow afternoon… there is a PASSMN meeting you could attend.  This month we’ll be hearing about Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server.  The meeting starts at 3:00 PM and there will some snacks and socializing before hand.

Feel free to contact me via e-mail (jstrate@digineer.com) or twitter (@stratesql) with questions or for more information.

Some lines got crossed with this month’s New Horizons ITCare event.  The topic and speaker have changed but the date is remaining the same.  Instead of the previous topic, I’ll be presenting on SQL Server Integration Services.  As I mentioned, the date of the event is still June 24th.  The event will be available online and you can register for the event here.

Course Overview

ITCare, delivered and hosted quarterly by our Partners is designed to keep you up to date on the cutting-edge technologies. ITCare is a FREE half-day technical seminar series created by IT Professionals for IT Professionals! We'll address the needs and issues today and show you how to make a real difference in the way that you work.

SQL Server Integration Services (SSIS) offers a wide range of features and functionality that can be used to solve business pains within Extract, Transform, and Load (ETL) processes. These pains can be caused by the performance of current ETL process, the maintainability of ETL processes or represent a new pain that SSIS features can resolve. This presentation will provide a demonstration of real world scenarios where features of SSIS were used to solve business pains through modification of existing SSIS packages or through the creation of new SSIS packages. The session will also discuss current best practices in SSIS package design.

Objectives

1. Understand flexibility that SSIS features provide
2. Demonstrate application of SSIS functionality to business needs
3. Discuss current best practices in SSIS package design

Audience

IT Administrators, IT Managers, Business Intelligence, DBA, SQL Server administrators.

Prerequisites

Understanding of basic ETL concepts and basic T-SQL coding

Course Length

1 Half Day

Speaker: Jason Strate of Digineer, Inc.
Jason Strate has been a Database Architect for over ten years. His experiences include designing and implementing both OLAP and OLTP solutions involving clustering servers, database monitoring and tuning, analysis services, DTS/Integration Services, Reporting Services, and Notification Services. Jason is actively involved with the local PASS chapter (SQL Server User Group); he is currently serving on the Executive Board and has presented at monthly meetings. Jason contributed to Microsoft's published white paper "Empowering Enterprise Solutions with SQL Server 2005 Enterprise Edition and actively blogs about SQL Server and related technologies. Also this past year, Jason participated in the development of Microsoft Certification exams for SQL Server 2008 and presented at the SSWUG SQL Server Launch Event and the last two SSWUG Virtual SQL Server Conferences.

Today’s stored procedure for the DBADiagnostics database is a re-hash of a post I did a couple months ago.  The reason for re-hashing the procedure is that when I used it last week I found out there were some serious issues with its results.  Some plans were being duplicated and the performance statistics weren’t accurate because of this.  And why not fix the issues and make a DBADiagnostics post out of it.

In the previous version, I was using the execution count from sys.dm_exec_query_stats to determine how often a plan was being executed.  In the DMV though the SQL statements in the may execute in varying counts depending on conditional logic.

Instead of using the execution count from sys.dm_exec_query_stats the procedure now uses usecount from sys.dm_exec_cached_plans; which represents how often the plan was used.  The execution count represented how often the statements in the plan were executed.

The stored procedure accepts the following parameters:

• @Database: The database to search for plans within.  This value can be NULL.
• @ObjectName: The name of the procedure to search for plans for.  This value can be NULL.

The procedure can use either or none of the variables.  Obviously with both variables empty the results will be for all cached plans on the server.

USE [DBADiagnostics]GOIF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Utility')    EXEC('CREATE SCHEMA [Utility] AUTHORIZATION [dbo]')GOIF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Utility.CachedPlanSearch'))    DROP PROCEDURE [Utility].[CachedPlanSearch]GO/*============================================================Procedure:    [Utility].[CachedPlanSearch]Author:       Jason StrateDate:         June 1, 2009 Synopsis:    Searches cache for all occurances of a plan based on an    procedure and or database name.  Results assumes that average    execution time for returned results are less than 24 hours.Exec Utility.CachedPlanSearch    @DatabaseName = 'msdb'    ,@ObjectName = 'sp_sqlagent_get_startup_info' ============================================================Revision History:Date:         By                Description------------------------------------------------------------ ============================================================*/CREATE PROCEDURE Utility.CachedPlanSearch    (    @DatabaseName sysname = NULL    ,@ObjectName sysname = NULL    )AS;WITH cteExecInfoAS (     SELECT DB_NAME(st.dbid) AS database_name         ,OBJECT_NAME(st.objectid, st.dbid) AS object_name         ,cp.usecounts -- Use in place of qs.execution_count for whole plan count        ,CAST(SUM(qs.total_worker_time)/(cp.usecounts*1.) as decimal(12,2)) AS avg_cpu_time         ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(cp.usecounts*1.) as decimal(12,2)) AS avg_io         ,SUM(qs.total_elapsed_time)/(cp.usecounts)/1000 as avg_elapsed_time_ms        ,st.text AS sql_text        ,qs.plan_handle     FROM sys.dm_exec_query_stats qs         INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle        CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st     WHERE (DB_NAME(st.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)    AND (OBJECT_NAME(st.objectid, st.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)    GROUP BY st.dbid, st.objectid, cp.usecounts, st.text, qs.plan_handle ) SELECT cte.database_name     ,cte.object_name     ,cte.usecounts     ,cte.avg_cpu_time     ,cte.avg_io     ,CONVERT(varchar, DATEADD(ms, cte.avg_elapsed_time_ms, 0), 114) AS avg_elapsed_time    ,qp.query_plan     ,cte.sql_textFROM cteExecInfo cte     OUTER APPLY sys.dm_exec_query_plan(cte.plan_handle) qp ORDER BY cte.usecounts DESC

I’ve often found this procedure useful when people complain about the execution of a procedure.  Instead of pulling out performance tools and running test versions of the procedure this will provide the last plan that was used for the procedure.

The topics and speakers for June PASSMN meeting have been announced.

Analysis Services Dimension Creation Best Practices & Disks, Real and Virtual and What is Important for SQL Server

June 16, 2009
3:00 PM - 5:15 PM
Register Here or visit http://www.mnssug.org/

Analysis Services Dimension Creation Best Practices
Speaker: Brian Larson, Superior Consulting Services

Having dimensions that are well structured and function efficiently is key to having performant cubes and an important factor when encouraging users to use cubes for ad hoc reporting and interactive analysis. This session will look at best practices to observe when designing and creating dimensions in Analysis Services.

Brian Larson has 24 years of experience in the computer industry and 20 years’ experience as a consultant creating custom database applications. He is the Chief of Technology and BI Practice Manager for Superior Consulting Services in Burnsville, Minnesota, a Microsoft Gold Certified Partner. Brian is a Microsoft Certified Solution Developer (MCSD) and a Microsoft Certified Database Administrator (MCDBA).

Brian served as a member of the original Reporting Services development team as a consultant to Microsoft. In that role, he contributed to the original code base of Reporting Services. Brian has presented at national conferences and events, including the SQL Server Magazine Connections Conference, the PASS Community Summit, and the Microsoft Business Intelligence Conference, and has provided training and mentoring on Reporting Services and business intelligence across the country. He has been a contributor and columnist for SQL Server Magazine. Brian is the author of Microsoft SQL Server 2008 Reporting Services and Delivering Business Intelligence with Microsoft SQL Server 2008, both from McGraw-Hill Professional.

Disks, Real and Virtual and What is Important for SQL Server
Speaker: James Lorenzen, Xiotech Corporation

1. Introduction
2. A brief history of Hard Drives
   1. How Moore’s Law applies
   2. The performance impact of large disks (Greater than 1 terabyte)
   3. Why bigger is not always better
3. Storage topology – DAS, NAS, and SAN
   1. Definitions
   2. Where they fit in a data center
4. Type of disks and interfaces available today
   1. More acronyms (SATA, SCSI, IDE, SSD, etc.)
5. Where the different disks fit, performance and capacity
6. RAID Definitions
   1. Why it came about
   2. RAID Levels (0 through 50, or so)
7. How SQL Server uses the storage
   1. The SQL Server file types
   2. The performance requirements of the different file types
8. Questions

James Lorenzen is a Technical Marketing Engineer at Xiotech Corporation. He is Xiotech’s database specialist with over 25 years of database experience, fifteen years of that working with relational databases, SQL Server and Oracle. At Xiotech, James has focused on how to configure the database storage on a SAN to achieve the best possible performance from the SQL Server database. Prior to joining Xiotech, James has worked as a DBA for various companies both as consultant and dedicated Database Administration Manager. James has presented at user group meetings, covering database configuration on SAN storage. He can be reached at James_Lorenzen@Xiotech.com.

It’s happened a few times, I go out to assist with recovery from some sort of failure and the question of database backups results in an uncomfortable pause.  One group thought the other group was taking the backups and the other group didn’t know that the backups needed to be taken.  And neither group previously attempted to execute the recovery plan before a failure had occurred.  The end result is that the miscommunication resulted in a lack of necessary backups.

The most common scenario where this occurs is when the network or backup team is taking the SQL Server backups through a third-party backup tool removing the DBA’s from the “backup” process.  In these cases, the DBAs tend to relinquish their responsibility for the backup process placing complete trust that the databases are being backed up.

Trust No On With Backups

If you are a DBA in this situation, don’t trust that the backups are just happening.  Confirm it.  Every day.  Verify it.  Every day.  It doesn’t matter the level of trust that exists between the DBAs and other groups, as a DBA it is critical that when a database failure occurs that the database can be recovered and that there is full confidence that the needed backups exist.  Users will only blame the DBAs when data can’t be recovered because that is what the DBAs are there for.  The users won’t care who should have taken the backups only that it doesn’t exist and the DBA let that happen.

As a disclaimer, this isn’t an admonishment of other groups having backup responsibilities.  But the reality when other groups are active in the backup process,

Now checking on backups every day can be tedious.  In the scenario I’m outlining in this post a separate team is executing the backups.  Should the DBAs check each database to make certain that backups are occurring on schedule?  Yes, but that doesn’t necessarily mean that each database needs to be opened in SSMS to verify this.

Instead, I have an stored procedure that is executed multiple times a day, typically every few hours, to alert if a full and/or differential backup hasn’t been taken in the last XX number of hours.  This way the only concern is the databases who’s backup schedules have fallen outside of SLA.

First the Setup

Before unveiling the new stored procedure for checking backups, there are some other object that will be needed first.  These tables are used to configure this and other alerts and maintenance procedures.  I’m a big fan of having a single database management code base between all servers, rather than customizing stuff and losing changes from time to time.

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Maintenance')
    EXEC sys.sp_executesql 'CREATE SCHEMA [Maintenance] AUTHORIZATION [dbo]'
GO

IF OBJECT_ID('Maintenance.ExcludeType') IS NULL
BEGIN
CREATE TABLE [Maintenance].[ExcludeType]
    (
    [ExcludeTypeID] [tinyint] NOT NULL IDENTITY(1,1),
    [Description] [varchar](500) NULL,
    [CreateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeType_CreateDate DEFAULT GETDATE(),
    [CreateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeType_CreateBy DEFAULT SUSER_SNAME(),
    [UpdateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeType_UpdateDate DEFAULT GETDATE(),
    [UpdateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeType_UpdateBy DEFAULT SUSER_SNAME(),
    CONSTRAINT [PK_ExcludeType] PRIMARY KEY CLUSTERED ([ExcludeTypeID] ASC) ON [Data]
    ) ON [Data]

CREATE UNIQUE NONCLUSTERED INDEX UIX_ExcludeType_Description ON Maintenance.ExcludeType ([Description]) ON Data

END
GO

IF NOT EXISTS(SELECT * FROM [Maintenance].[ExcludeType] WHERE [Description] = 'BackupCheck')
BEGIN
    INSERT INTO [Maintenance].[ExcludeType] ([Description])
    VALUES ('BackupCheck')
END
GO

IF OBJECT_ID('Maintenance.ExcludeDatabase') IS NULL
BEGIN
CREATE TABLE [Maintenance].[ExcludeDatabase]
    (
    [DatabaseName] [sysname] NOT NULL,
    [ExcludeTypeID] [tinyint] NOT NULL,
    [ExcludeReason] [varchar](255) NOT NULL,
    [CreateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeDatabase_CreateDate DEFAULT GETDATE(),
    [CreateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeDatabase_CreateBy DEFAULT SUSER_SNAME(),
    [UpdateDate] [smalldatetime] NOT NULL CONSTRAINT DF_ExcludeDatabase_UpdateDate DEFAULT GETDATE(),
    [UpdateBy] [varchar](128) NOT NULL CONSTRAINT DF_ExcludeDatabase_UpdateBy DEFAULT SUSER_SNAME(),
    CONSTRAINT [PK_ExcludeDatabase] PRIMARY KEY CLUSTERED ([DatabaseName] ASC, [ExcludeTypeID] ASC) ON [Data]
    ) ON [Data]
END
GO

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = 'FK_ExcludeDatabase_ExcludeTypeID_FROM_ExcludeType')
BEGIN
    ALTER TABLE [Maintenance].[ExcludeDatabase] WITH CHECK 
    ADD CONSTRAINT [FK_ExcludeDatabase_ExcludeTypeID_FROM_ExcludeType] FOREIGN KEY([ExcludeTypeID]) REFERENCES [Maintenance].[ExcludeType] ([ExcludeTypeID])
END

And then the Store Procedure

The next code window has the stored procedure code for this alert.  A few things to point out for the alert.  First, the alert assumes that all databases have the same SLA for checking backups.  This point of this was that the alert would be configured for the most crucial databases and take the less crucial databases along for the ride.  Overly cautious is better than being under prepared when it comes to backups.

Next, the procedure utilizes the table Maintenance.ExcludeDatabases.  This table serves as an exclusion table for this alert and some other maintenance processes that will be introduced in later posts.  On the cautionary side, I prefer processes that maintain all databases by default and excludes those that a conscious decision to exclude has been made.  I’d rather be safe than sorry.  Easier to keep my job this way.

To make certain the databases the alert isn’t checking databases that should be naturally ignored, the stored procedure ignore the following items as well:

• tempdb and model are ignored.
• Any database with a source_database_id which indicates that it is a snapshot database.
• Any database in standyby mode which represents scenario where logs are being restored.
• Any database with a RESTORING or OFFLINE state.  These databases cannot be backed up in their current state.

Lastly, the procedure accepts a few parameters to configure the alert.  The parameters are the following:

• @ToAddress: the person, group, or mailbox that needs to receive the alert.
• @SubjectWarning: a customizable message that can be added to the subject line of the e-mail alert. This was added so that when it is setup on pre-production and production servers the same alert code can be used on both servers but the subject line can include text informing the severity of the issue.
• @HoursSinceBackup: the number of hours that the backups can age before an alert should be generated.

Without further ado, here is the procedure:

USE [DBADiagnostics]
GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Alert')
    EXEC('CREATE SCHEMA [Alert] AUTHORIZATION [dbo]')
GO

IF EXISTS(SELECT * FROM sys.procedures WHERE object_id = OBJECT_ID('Alert.BackupCheck'))
    DROP PROCEDURE [Alert].[BackupCheck]
GO

/*================================================================================
Procedure:    Alert.BackupCheck
Author:        Jason Strate
Date:        June 1, 2009

Synopsis:
    This procedure check the datbase backup is done within a number of hours passed
    into the procedure.  


================================================================================
Revision History:
Date:        By            Description
----------------------------------------------------------------------------------
================================================================================*/
CREATE PROCEDURE [Alert].[BackupCheck]
    (
    @ToAddress varchar(255)
    ,@SubjectWarning varchar(255) = ''
    ,@HoursSinceBackup smallint = 36
    )
AS

SET NOCOUNT ON

DECLARE @SQL varchar(max)
    ,@Subject nvarchar(255)
    ,@Body nvarchar(max)
    ,@RowCount int
    ,@DBCount int

SELECT @Subject = @SubjectWarning + Space(1) + @@Servername + ': Check Database Backup Not Done in the past '
        + Convert(varchar, @HoursSinceBackup) + ' Hours.'
    ,@Body = 'The following database files are not backed up with in '
        + Convert(varchar, @HoursSinceBackup) 
        + ' hour(s).  Please determine the cause of missing Backup(s).' + Char(13)
    ,@SQL = 'Set NoCount On; Select * From tempdb..tmpAlertResults'

BEGIN TRY  
    IF OBJECT_ID('tempdb..tmpAlertResults') IS NOT NULL
        DROP TABLE tempdb..tmpAlertResults

    ;WITH Excludes
    AS (
        SELECT DatabaseName
        FROM Maintenance.ExcludeDatabase ed
            INNER JOIN Maintenance.ExcludeType et ON ed.ExcludeTypeID = et.ExcludeTypeID
        WHERE et.Description = 'BackupCheck'
        )
    SELECT CONVERT(varchar, d.Name) AS [Database Name], 
        MAX(backup_start_date) AS [Backup Start Date], 
        MAX(backup_finish_date) AS [Backup Finish Date],
        DATEDIFF(hour, MAX(backup_start_date),GETDATE()) AS [Hour Since Backup]
    INTO tempdb..tmpAlertResults
    FROM sys.databases d 
        LEFT OUTER JOIN msdb..backupset bs On d.Name = bs.database_name ANd bs.type IN ('D','I')
        LEFT OUTER JOIN Excludes e On d.Name = e.DatabaseName 
    WHERE d.Name NOT IN ('tempdb', 'model') -- Back ups not necessarily needed
    AND source_database_id IS NULL -- If not NULL then database is a snapshot
    AND is_in_standby = 0 -- is_in_standby is read-only log shipped
    AND state_desc NOT IN ('RESTORING','OFFLINE') -- mirroring flag, offline check
    GROUP BY d.Name
    HAVING DATEDIFF(hour, MAX(backup_start_date),GETDATE()) > @HoursSinceBackup
    OR MAX(backup_start_date) IS NULL
    ORDER BY MAX(backup_start_date) ASC 

    IF EXISTS (SELECT * FROM tempdb..tmpAlertResults)
    BEGIN
        Exec msdb.dbo.sp_send_dbmail 
        @recipients = @ToAddress, 
        @subject = @Subject,
        @body = @Body,
        @execute_query_database = 'DBADiagnostics',
        @query = @SQL,
        @query_result_separator = '|'
    END
END TRY 
BEGIN CATCH
    DECLARE @ErrState int,
        @ErrSev int,
        @ErrMsg varchar(max)

    SELECT @ErrSev = ERROR_SEVERITY(),
       @ErrState = ERROR_STATE(),
       @ErrMsg = ERROR_MESSAGE()

    RAISERROR (@ErrMsg, @ErrSev, @ErrState)
END CATCH