A while back I wrote a post on a procedure I created for the DBADiagnostics database that I talk about from time to time.  This procedure allowed users to search the procedure cache to find plans for procedures by database name and object name.  This had worked out pretty good until I noticed something the other day.

The procedure is using the sys.dm_exec_query_stats as the based table for the query to build itself upon.  The trouble with using this DMV is that it doesn’t have all of the plans listed from the procedure cache.  Only those that currently have stats stored for sql_handles.

About the same time I started writing this post, I noticed a post by Adam Machanic (twitter | blog) in which he puts out a warning to those using sys.dm_exec_query_stats.  In that post, he discusses how the ALTER TABLE statement can result in a batch missing this DMV.

Considering all this lends itself to the question, is there a better DMV that can be used to find plans in the procedure cache?

Use sys.dm_exec_cached_plans Instead

Another DMV that has this information in it is the DMV sys.dm_exec_cached_plans.  This DMV is designed to provide a list of all cached plans in the procedure cache.  To illustrate the difference, lets take a look at the following two queries.

SELECT COUNT(DISTINCT plan_handle)
FROM sys.dm_exec_cached_plans
GO 

SELECT COUNT(DISTINCT plan_handle)
FROM sys.dm_exec_query_stats
GO

On my system, the following results are returned:

The first value is the number of plan_handles in sys.dm_exec_cached_plans.  The second is the number of plan_handles in sys.dm_exec_query_stats.  There is a substantial difference between the two.  This is something you’d be expecting based on the information at the start of this post.

Query to Find Cached Plans

Now that I’ve outlined the issues with my original post, lets take a look at a script that will query the procedure cache to look for plans stored for stored procedures and other database objects.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO

DECLARE @DatabaseName sysname
    ,@ObjectName sysname 

SELECT @DatabaseName = 'msdb'
    ,@ObjectName = 'sp_jobhistory_row_limiter';

WITH PlanSearch
AS (
      SELECT qp.dbid
            ,qp.objectid
            ,DB_NAME(qp.dbid) as DatabaseName
            ,OBJECT_NAME(qp.objectid, qp.dbid) as ObjectName
            ,cp.usecounts
            ,cp.plan_handle
      FROM sys.dm_exec_cached_plans cp
            CROSS APPLY sys.dm_exec_text_query_plan(cp.plan_handle, DEFAULT, DEFAULT) qp
    WHERE cp.cacheobjtype = 'Compiled Plan'
      AND (DB_NAME(qp.dbid) = @DatabaseName OR NULLIF(@DatabaseName,'') IS NULL)
    AND (OBJECT_NAME(qp.objectid, qp.dbid) = @ObjectName OR NULLIF(@ObjectName, '') IS NULL)
)
,PlansAndStats
AS (
      SELECT ps.DatabaseName
        ,ps.ObjectName
        ,ps.usecounts -- Use in place of qs.execution_count for whole plan count
        ,CAST(SUM(qs.total_worker_time)/(ps.usecounts*1.) as decimal(12,2)) AS avg_cpu_time
        ,CAST(SUM(qs.total_logical_reads + qs.total_logical_writes)/(ps.usecounts*1.) as decimal(12,2)) AS avg_io
        ,SUM(qs.total_elapsed_time)/(ps.usecounts)/1000 as avg_elapsed_time_ms
        ,ps.plan_handle
    FROM PlanSearch ps
            LEFT OUTER JOIN sys.dm_exec_query_stats qs ON ps.plan_handle = qs.plan_handle
    GROUP BY ps.DatabaseName
        ,ps.ObjectName
        ,ps.usecounts
            ,ps.plan_handle
)
SELECT ps.DatabaseName
    ,ps.ObjectName
      ,ps.usecounts
      ,ps.avg_cpu_time
      ,ps.avg_io
      ,ps.avg_elapsed_time_ms
      ,qp.query_plan
      ,ps.plan_handle
FROM PlansAndStats ps
      CROSS APPLY sys.dm_exec_query_plan(ps.plan_handle) qp 

Executing the script above provides the following output:

The output includes the name of the object being sought and a link to the plan from the procedure cache.  Included with this is some basic performance information from sys.dm_exec_query_stats that can be useful in determining performance variations between the plans returned.

Procedure to Find Plans

For those that like to take these scripts and build stored procedures for them.  I’ve also included a script below that includes this information.

I’ve been meaning to join in on the T-SQL Tuesday event for the last couple months and let the time opportunity slip by.  This month’s topic is reporting and its seems like a good time to get in on the event.

The great thing about reporting is that for the past few months I’ve been building towards this very post.  A few months ago, I posted a blog on a process that I often use with clients and on my own machines to capture wait stats over time.  A while later, I followed-up with a blog on categorizing wait stat and providing definitions for them.

While that was all fine and good, I hadn’t provided any method to report this information in a manner that would make gathering this information useful at all.  Part of the delay was that shortly after the first post I realized that the reports I had intended to put up were useless.  They were pretty and had lots of data, but there wasn’t anything you could really accomplish with them.  What’s the point of useless content?

So, here I’ll walk through a level-100 report based on the information that’s been collected.  The point of this report will simply be to provide a snapshot over a number of days of all wait stats and their degree of activity on a SQL Server instance.

Wait Stat Snapshot Procedure

Before I can provide the report, we’ll first need a stored procedure to work off of.  It’s a fairly simple procedure based on the previous two posts on this topic.  Those ones I mentioned above.

USE [DBADiagnostics]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Report].[WaitStatSnapshot]') AND type in (N'P', N'PC'))
DROP PROCEDURE [Report].[WaitStatSnapshot]
GO

/*============================================================
Procedure:	Report.WaitStatSnapshot
Author:		Jason Strate
Date:		April 1, 2010

Synopsis:
This procedure aggregates wait stats for the selected date range.  Data is
joined with category information to provide description and action
guidance if available.

Parameters
@StartDate  : Begin date for aggregating wait stats.  Begins from the start
of the date.
@EndDate    : End date for aggregating wait stats.  Includes all data for the
end date.

============================================================
Revision History:
Date:		By			Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Report].[WaitStatSnapshot]
(
@StartDate datetime
,@EndDate datetime
)
As

;WITH WaitStatCTE
AS (
SELECT COALESCE(wtc.Category, 'UNKNOWN') AS Category
,COALESCE(wtc.Resource, 'UNKNOWN') AS Resource
,wtc.Description
,wtc.Action
,wsh.WaitType
,SUM(wsh.WaitingTasksCount) AS WaitingTasksCount
,SUM(wsh.WaitTimeMs) AS WaitTimeMs
,SUM(wsh.SignalWaitTimeMs) AS SignalWaitTimeMs
FROM Monitor.WaitStatHistory wsh
INNER JOIN Resources.WaitTypeCategory wtc ON wsh.WaitType = wtc.WaitType
WHERE wsh.WaitingTasksCount <> 0
AND	wsh.CreateDate BETWEEN @StartDate AND DATEADD(ms, -3, DATEADD(d, 1, @EndDate))
GROUP BY COALESCE(wtc.Category, 'UNKNOWN')
,COALESCE(wtc.Resource, 'UNKNOWN')
,wsh.WaitType
,wtc.Description
,wtc.Action
)
SELECT Category
,Resource
,[Description]
,[Action]
,WaitType
,WaitTimeMs
,WaitingTasksCount
,SUM(WaitTimeMs) OVER() AS TotalWaitTimeMS
,SignalWaitTimeMs
,CAST((1.*WaitTimeMs)/SUM(WaitTimeMs) OVER() AS decimal(8,6)) AS PercentWaitTimeMS
FROM WaitStatCTE
ORDER BY WaitTimeMs DESC
GO

Wait Stat Snapshot Report

The stored procedures above returns all of the wait stats aggregated over the selected time period and displays them on the report shown below.  By default the report won’t display Description or Action information, but you can easily drill-down into that information.

The report can be downloaded here :

To deploy this report, you’ll have to update the data source information for the report.  Beyond that it should be easy to deploy and begin using.

Finding Some Benefit

I’ve been using this report to keep an eye on what’s happening on the SQL Servers that I’ve been monitoring for clients and it provides a good snapshot of what’s been happening since I last reviewed the server.  The analysis isn’t too deep, but then it wouldn’t be called a snapshot report.

A few weeks ago I posted a job that I use to track wait stats in the DBA database that I bring to a number of clients.  I had promised to follow-up with a couple or three reports that could be used to monitor the values that were being aggregated in those tables.

Before getting to that, though, a quick look at the output of sys.dm_os_wait_stats might have indicated that there is a small issue with looking at the wait stats as they currently are.  There are a lot of wait types.  Specifically, my instances of SQL Server report 202 wait types for SQL Server 2005 and 485 wait types for SQL Server 2008.  Combining the two lists yields nearly 500 distinct wait types between SQL Server 2005 and 2008.  Ever notice that the MISCELLANEOUS wait type appears twice for SQL Server 2008, I bet there is a good story behind that one.

With all of those wait types, there needs to be a method to group them together to assist in researching resource performance related issues.  Of course, you already knew this and it’d probably be nice if I stopped my stalling and go to the point.

Building A Wait Type Category List

Before I go there, first I’d like to point out how I built the list and my plans for keeping it updated.  What’s the value in a classification process, if you don’t know the value of the information included?

To start with there are people that know the operations and internals of SQL Server much better than I do.  There is even a whole department at Microsoft, the CSS SQL Server Engineers,  that spends a lot time up to their elbows working with wait types.  Let’s be real, I mostly know what these guys have published.  Recently, Bob Ward has started a SQL Server Wait Type Repository this is the basis for the wait type categories.  Why not just use this list and be done with it?  Well, the repository only has 22 wait types currently listed.  They are hitting the ones that provide the most value.  Their table does provide a structure and starting point for this list and I consider it the source for updates to categorizing wait types.

Next the list was augmented with the information provided in the SQL Server 2005 Waits and Queues white paper.  This is a detailed white paper the provides categories and troubleshooting information for different wait types for SQL Server 2005.  If you haven’t read this white paper you are definitely missing out and should bail on this post and go read it.

The third resource I used was the SQLServerPedia Wait Type list.  This list covers a number of the items that aren’t covered in the previous two sources and almost all of the remaining wait types.  And depending on when you read this, it may just look identical to the wait type category list since I am hoping to submit an update of one with the other.

Beyond these, there are still a few holes in the list but hopefully over time one of the three sources above or some other source will fill in the gaps and I’ll just post updates from time to time.

Providing the Wait Type Category List

Based on the above, here is the schema for a table that will hold all of the wait types that conforms to the structure I mentioned earlier:

USE [DBADiagnostics]GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[Resources].[WaitTypeCategory]') AND type in (N'U'))    DROP TABLE [Resources].[WaitTypeCategory]GO

IF  EXISTS (SELECT * FROM sys.schemas WHERE name = N'Resources')    DROP SCHEMA [Resources]GO

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

CREATE TABLE [Resources].[WaitTypeCategory](    [WaitType] [nvarchar](60) NOT NULL,    [Category] [varchar](50) NULL,    [Resource] [varchar](50) NULL,    [Version] [varchar](50) NULL,    [Description] [varchar](255) NULL,    [Action] [varchar](max) NULL, CONSTRAINT [PK_WaitTypeCategory] PRIMARY KEY CLUSTERED     (    [WaitType] ASC    )) GO

If you don’t know where you’ve been, how can you expect to know where you are going.  Seems like such a simple little line and I’m sure many of us can look at history, politicians, and maybe that restaurant review that you didn’t heed when it comes to this line.  But this also applies to DBAs and more specifically to SQL Server.

In much the same way… if you don’t know what’s been happening in your SQL Server environment, you won’t know how to resolve performance problems that have occurred on the server.  One of the ways I track what’s happened on a SQL Server instance is through wait stats.  Now if you aren’t tracking wait stats and they aren’t one of the first few things that you check when monitoring performance then you need to stop reading this post and read this instead.

Wait Stat Snapshots

To facilitate wait stat monitoring, I typically take a snapshot of the current wait stats (sys.dm_os_wait_stats) on a server every 15 minutes.  This is enough time that I can roll get a good idea of what’s happened from a resource perspective in a short amount of time and I can also easily roll the value up to hour, two-hour, or full day totals.  It gives me options – we all like options.

As I’ve done in a number of posts in the past this new script will be setup to add to the DBADiagnostics database that I’ve blogged about before.

Building the Snapshot

There are two table included in the process for creating the snapshots of wait stat activity.  These are:

• Monitor.WaitStatSnapshot
• Monitor.WaitStatHistory

Here is the schema to create them:

USE [DBADiagnostics]
GO
�
CREATE TABLE [Monitor].[WaitStatSnapshot](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatSnapshot_CreateDateWaitType]
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]
GO
�
CREATE TABLE [Monitor].[WaitStatHistory](
    [CreateDate] [datetime] NOT NULL,
    [WaitType] [nvarchar](60) NOT NULL,
    [WaitingTasksCount] [bigint] NOT NULL,
    [WaitTimeMs] [bigint] NOT NULL,
    [MaxWaitTimeMs] [bigint] NOT NULL,
    [SignalWaitTimeMs] [bigint] NOT NULL,
    CONSTRAINT [PK_Monitor_WaitStatHistory_CreateDateWaitType]
        PRIMARY KEY CLUSTERED ([CreateDate] ASC, [WaitType] ASC)
) ON [Data]

The general process is to insert the current values from the wait stat DMV into Monitor.WaitStatSnapshot.  After which the delta between the last two snapshots in Monitor.WaitStatSnapshot is inserted into Monitor.WaitStatHistory.

If the values in the last snapshot are smaller that those in the second to last snapshot, then the insert assumes that the wait stat DMV has been reset.  Resetting can occur either though a SQL Server service restart or a DBCC command.

Either way the procedure below will accumulate a snapshot history from the wait stat DMV.  Of courses there are a couple parameters that provide some automated cleanup.  Data in Monitor.WaitStatSnapshot is cleaned up after 1 day and the data in Monitor.WaitStatHistory is cleaned up after 90 days.  This helps prevent the data in the DBADiagnostic database from taking over when it’s neglected because everything is running perfectly.

USE [DBADiagnostics]
GO

/*============================================================
Procedure:    Monitor.TrackWaitStats
Author:        Jason Strate
Date:        October 26, 2009

Synopsis:
    This procedure takes snapshots of wait stats and compares them with previous
    snapshots to determine a delta of changes over time.  Raw snapshot information
    is deleted on a short time span, while the delta information in the history
    table is deleted over a longer time span.

============================================================
Revision History:
Date:        By            Description
------------------------------------------------------------

============================================================*/
CREATE PROCEDURE [Monitor].[TrackWaitStats]
    (
    @SnapshotDays tinyint = 1
    ,@HistoryDays smallint = 90
    )
AS

INSERT INTO Monitor.WaitStatSnapshot
SELECT GETDATE()
    , CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END
    , SUM(waiting_tasks_count)
    , SUM(wait_time_ms)
    , SUM(max_wait_time_ms)
    , SUM(signal_wait_time_ms)
FROM sys.dm_os_wait_stats
GROUP BY CASE wait_type WHEN 'MISCELLANEOUS' THEN 'MISCELLANEOUS' ELSE wait_type END

;WITH WaitStatCTE
AS (
    SELECT CreateDate
        , DENSE_RANK() OVER (ORDER BY CreateDate DESC) AS HistoryID
        , WaitType
        , WaitingTasksCount
        , WaitTimeMs
        , MaxWaitTimeMs
        , SignalWaitTimeMs
    FROM Monitor.WaitStatSnapshot
    )
INSERT INTO Monitor.WaitStatHistory
SELECT w1.CreateDate
    , w1.WaitType
    , w1.WaitingTasksCount - COALESCE(w2.WaitingTasksCount,0)
    , w1.WaitTimeMs - COALESCE(w2.WaitTimeMs,0)
    , w1.MaxWaitTimeMs - COALESCE(w2.MaxWaitTimeMs,0)
    , w1.SignalWaitTimeMs - COALESCE(w2.SignalWaitTimeMs,0)
FROM WaitStatCTE w1
    LEFT OUTER JOIN WaitStatCTE w2 ON w1.WaitType = w2.WaitType
        AND w1.WaitingTasksCount >= COALESCE(w2.WaitingTasksCount,0)
        AND w2.HistoryID = 2
WHERE w1.HistoryID = 1

DELETE FROM Monitor.WaitStatSnapshot
WHERE CreateDate < DATEADD(d, -@SnapshotDays, GETDATE())

DELETE FROM Monitor.WaitStatHistory
WHERE CreateDate < DATEADD(d, -@HistoryDays, GETDATE())
GO

There you have it.  While I didn’t go into how you can and will use this information, the basis for collecting this data for reporting has been laid out.

Later this week I’ll post a follow-up to show how to report on this information and include a couple Reporting Services reports for digging into the data.

Everyone likes taking some time to relax.  You’re sitting out in the sunshine or on a beach and the waitress is bringing you your favorite beverage.  The company and conversation is great and it seems like the perfect day.  And then… suddenly without warning… your drink is gone.

You were having a great time and the waiter, who should be monitoring for this situation, failed to do so.  As the conversation is interrupted by this horror of this situation you look around the table and start sipping on what’s left of the water you had with dinner.

What if there wasn’t water to switch to?  What would have happened?

In much the same way, DBAs are the waiters and waitresses for their SQL Servers.  Instead of serving beverages, we provide space to the database files for our environments.  In most cases the conversations (transactions) in the database will be safe from harm because there will be extra water (space) laying around.

What if there isn’t and you haven’t planned ahead for this scenario?  It would be worse to have you actual drinks and evening with friends interrupted because the SQL Servers are offline because they ran out of the space necessary to grow.

I’m Growing and Taking More Beverages With Me

In a previous post I talked about knowing when a file growth has occurred.  This is the after-the-fact alert to inform that things may not be going as planned.  Before you get to that point,  it would be nice to have something in place that informs that unplanned growth may occur.

To that end…

How Many Kegs Are Left In the Cooler?

To help alleviate evening interruptions, I put together a file space monitoring alert for the  DBADiagnostics database a while back.  This alert is usually scheduled through a SQL Server Agent job and is executed in the morning before the day starts.  This allows me to see which databases may need some action through the day and I get all day minus meetings, emergencies, meetings, and other work to get this taken care of.

To cut to the chase, 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 OBJECT_ID('Alert.FilegroupSpaceAvailable') IS NOT NULL

    DROP PROCEDURE [Alert].[FilegroupSpaceAvailable]

GO

/*================================================================================

Procedure:    Alert.FilegroupSpaceAvailable

Author:        Jason Strate

Date:        2005-04-05

Synopsis:

    Alert to monitor amount of available space within database datafiles. 

    This process does not take into account space of transaction logs. Database 

    recovery strategy should account for those files.

Parameters:

    @PercentFull: Threshold percenteage for space used in data file

    @MBEmpty: Threshold MB value for space remaining in each data file.  Prevents

        false-positive alerts for large databases

    @ToAddress: E-mail address to send the alert

    @SubjectWarning: Prefix to e-mail message to code or rate the alert

================================================================================

Revision History:

Date:        By            Description

----------------------------------------------------------------------------------

================================================================================*/

CREATE PROCEDURE [Alert].[FilegroupSpaceAvailable]

    (

    @PercentFull decimal(5,2) = 90,

    @MBEmpty int = 2500,

    @ToAddress varchar(255),

    @SubjectWarning varchar(255) = ''

    )

AS

SET NOCOUNT ON

--Declare variables section

DECLARE @db_full_pages float,

    @db_full_percentage float,

    @SQL nvarchar(4000),

    @db_size float

IF @PercentFull NOT BETWEEN 1 AND 99 OR @PercentFull IS NULL

BEGIN

   RAISERROR('@PercentFull must be a value between 1 and 99.', 16, 1)

   RETURN -1

END 

IF @MBEmpty IS NULL

BEGIN

   RAISERROR('@MBEmpty can not be null.', 16, 1)

   RETURN -1

END 

IF OBJECT_ID('tempdb..#FileStats') IS NOT NULL

    DROP TABLE #FileStats

CREATE TABLE #FileStats

    (

    FileID int,

    FileGroup int,

    TotalExtents bigint,

    UsedExtents bigint,

    Name varchar(255),

    [FileName] varchar(255)

    )

IF OBJECT_ID('tempdb..#Output') IS NOT NULL

    DROP TABLE #Output

CREATE TABLE #Output

    (

    DatabaseName sysname,

    ID int IDENTITY(1,1),

    FileGroup varchar(255),

    FileName varchar(255),

    FilePath varchar(255),

    SpaceAvailable int,

    SpaceUsed int,

    PercentUsed decimal(12,6)

    )

DECLARE @DBName nvarchar(128)

DECLARE CURS_DATABASE CURSOR LOCAL FAST_FORWARD FOR

    SELECT name FROM sys.databases

    WHERE source_database_id IS NULL

    AND state_desc = 'ONLINE'

    AND is_read_only = 0

    AND is_in_standby = 0

    AND name NOT IN ('model', 'master', 'msdb')

    OPEN CURS_DATABASE

FETCH NEXT From CURS_DATABASE INTO @DBName

WHILE @@Fetch_Status = 0

BEGIN

    Set @SQL = 'Use [' + @DBName + '];

        Exec(''DBCC showfilestats'')'

    DELETE FROM #FileStats

    INSERT INTO #FileStats

    EXEC sp_ExecuteSQL @SQL

    Set @SQL = 'Use [' + @DBName + '];

        SELECT ''' + @DBName + '''

            ,RTRIM(fg.name)

            ,RTRIM(f.name)

            ,RTRIM(f.physical_name)

            ,(TotalExtents * 64) / 1024.

            ,(UsedExtents * 64) / 1024.

            ,100 * CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6))

        FROM #FileStats fs

            INNER JOIN sys.database_files f On fs.Name = f.Name COLLATE SQL_Latin1_General_CP1_CI_AS

            INNER JOIN sys.filegroups fg On    f.data_space_id = fg.data_space_id 

        WHERE CAST(((UsedExtents * 64) / 1024.)/((TotalExtents * 64) / 1024.) as decimal(12,6)) > ' + Convert(varchar, @PercentFull/100) + '

        AND ((TotalExtents * 64)) / 1024. - ((UsedExtents * 64) / 1024.) <=  ' + Convert(varchar, @MBEmpty) + '

        ORDER BY fg.name, f.name'

    INSERT INTO #Output

    EXEC sp_ExecuteSQL @SQL

    FETCH NEXT From CURS_DATABASE INTO @DBNAME

END

CLOSE CURS_DATABASE

DEALLOCATE CURS_DATABASE

IF EXISTS (SELECT * FROM #Output)

BEGIN

    DECLARE NOTIFICATION_CURS CURSOR LOCAL FAST_FORWARD FOR

        SELECT DatabaseName,

            FileGroup,

            FileName,

            FilePath,

            SpaceAvailable,

            SpaceUsed,

            PercentUsed

        FROM #Output

    DECLARE @DatabaseName nvarchar(255),

        @FileGroup nvarchar(255),

        @FileName nvarchar(255),

        @FilePath nvarchar(255),

        @SpaceAvailable nvarchar(255),

        @SpaceUsed nvarchar(255),

        @PercentUsed nvarchar(255),

        @Subject nvarchar(255),

        @Body nvarchar(max)

    SET @Subject = LTRIM(@SubjectWarning + SPACE(1) + @@SERVERNAME) + ': FileGroup Space Available Notification'

    SET @Body = 'The following database files belong to filegroups that nearly full.  Please increase the size of the files to prevent unscheduled file growth.<p>' +

            '<table border=1 cellspacing=0 cellpadding=0 align=center>' + 

            '<tr><td>Database Name</td><td>File Group</td><td>File Name</td><td>File Path</td><td>Space Available</td><td>Space USed</td><td>Percent Used</td></tr>'

    OPEN NOTIFICATION_CURS

    FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed

    While @@Fetch_Status = 0

    BEGIN

        Set @Body = @Body + '<tr><td>' + @DatabaseName + '</td><td>' + @FileGroup + '</td><td>' + @FileName + '</td><td>' + @FilePath 

            + '</td><td>' + @SpaceAvailable + '</td><td>' + @SpaceUsed + '</td><td>' + @PercentUsed + '</td></tr>'

        FETCH NEXT FROM NOTIFICATION_CURS INTO @DatabaseName, @FileGroup, @FileName, @FilePath, @SpaceAvailable, @SpaceUsed, @PercentUsed

    END

    CLOSE NOTIFICATION_CURS

    DEALLOCATE NOTIFICATION_CURS

    Exec msdb.dbo.sp_send_dbmail 

        @recipients = @ToAddress,

        @subject = @Subject,

        @body = @Body,

        @body_format =  'HTML'

END

GO

The parameters for the procedure are as follows:

• @PercentFull: Threshold percenteage for space used in data file
• @MBEmpty: Threshold MB value for space remaining in each data file.  Prevents false-positive alerts for large databases
• @ToAddress: E-mail address to send the alert
• @SubjectWarning: Prefix to e-mail message to code or rate the alert

Getting It Taken Care Of

This alert should not be used an a replacement to planning for the future growth of your databases.  Instead, the current and historical database sizes should be used to accommodate for future growth and should be reviewed at regular intervals.

Notifications of this sort are designed to let you know that unplanned growth may be possible and the historical growth rates ay no longer be valid based on the receipt of the alert.  Always know where you expect the database size to be and use this notification to determine when pre-determined safety threshold have been exceeded.

Nobody wants to run out of space or beer.

Caution:  The procedure uses the undocumented command DBCC SHOWFILESTATS.  Use this in production with caution.  It is the same process that SQL Server Management Studio uses to get this information.  But the command can change at any time without notice.

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 DBADiagnosticsdatabase 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, FileSizeM

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, @NewFileSizeM 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.

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]
GO

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

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

/*============================================================
Procedure:    [Utility].[CachedPlanSearch]
Author:       Jason Strate
Date:         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 cteExecInfo
AS (
    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_text
FROM 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.

Yesterday I posted about having an alert in place to know when SQL Agent jobs don’t have operators setup.  I’ve found this alert to be extremely useful, but often when its been shared it just leads to junk e-mail.

The reason for this is when the alert is added to 5, 10, or 50 servers it usually brings back a large number of results the first couple times.  And all of the jobs listed need to be updated so that the alerts start going out as required.

To alleviate the pain that the first execution can create, an maintenance procedure is included in the DBADiagnostics database which allows for mass or bulk updating of all of the SQL Agent jobs on the SQL Server instance.

The stored procedure accepts the following parameters:

• @operator: The name of the SQL Agent Operator that should receive the alerts.
• @notify_level_email: The type of notifications the job should send to e-mail.  The values for these can be found in help for sp_update_job.  The value 2 translates to On Failure.
• @notify_level_eventlog: The type of notification the job should send to the event log.  The value 0 translates to Never.

USE [DBADiagnostics]

GO

IF NOT EXISTS(SELECT * FROM sys.schemas WHERE name = 'Maintenance')

    EXEC('CREATE SCHEMA [Maintenance] AUTHORIZATION [dbo]')

GO

IF OBJECT_ID('Maintenance.SQLAgentJobsNotifyUpdate') IS NOT NULL

    DROP PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate

GO

/*============================================================

Procedure:    [Maintenance].[SQLAgentJobsNotifyUpdate]

Author:       Jason Strate

Date:         June 1, 2009

Synopsis:

    Mass update all jobs without notifications setup with

    new settings. 

Exec [Maintenance].[SQLAgentJobsNotifyUpdate] 

    @operator = 'DBA'

    ,@notify_level_email = 2

    ,@notify_level_eventlog = 0

============================================================

Revision History:

Date:         By                Description

------------------------------------------------------------

============================================================*/

CREATE PROCEDURE Maintenance.SQLAgentJobsNotifyUpdate

    (

    @operator sysname

    ,@notify_level_email int = 2

    ,@notify_level_eventlog int = 0

    )

AS

DECLARE @job_id uniqueidentifier

DECLARE JOB_UPDATE CURSOR FAST_FORWARD FOR

    SELECT job_id

    FROM msdb.dbo.sysjobs

    WHERE notify_email_operator_id = 0

    AND notify_level_eventlog = 0

    AND enabled = 1

OPEN JOB_UPDATE

FETCH NEXT FROM JOB_UPDATE INTO @job_id

WHILE @@FETCH_STATUS = 0

BEGIN

    EXEC msdb.dbo.sp_update_job 

        @job_id=@job_id 

        ,@notify_level_email=@notify_level_email

        ,@notify_level_eventlog=@notify_level_eventlog

        ,@notify_email_operator_name=@operator

    FETCH NEXT FROM JOB_UPDATE INTO @job_id

END

CLOSE JOB_UPDATE

DEALLOCATE JOB_UPDATE

CAUTION:  Since the procedure updates all jobs that don’t have notifications setup, check and double check before running this in a production environment to verify that the update it will do is exactly what you are looking for.

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

If anyone knows how to get the last modified date for a database that would be very helpful.  I’d like to add additional logic to the procedure that ignores databases that have been backed up at least once since being made read-only.

Have you ever gone out and made a new SQL Agent job and forget to assign notification settings for it? God, I hope I’m not the only one because that would mean I suck.

This is a common issue that exists in most environments that I’ve worked in. So I must not suck. Quite often I see SQL Agent Jobs executing throughout an organization and no one knows that a critical job has been failing day after day because the notifications where not setup for the job.

Every environment has its own requirements for how notifications need to be aggregated and handled. Most often the SQL Agent jobs are configured to send e-mail notifications and then the person that receives the e-mail is charged with resolving the issue. Sometimes these e-mails are sent to applications that automatically generate a ticket that is then forwarded to the responsible individual. In other environments, the failed jobs are logged to the event log and a server monitoring tool collects the event log information and generates alerts based on these entries.

The alert below was created with these two scenarios in mind. The procedure looks for jobs that don’t have either e-mail operators or an event log status set. To reduce clutter, it also ignores disabled jobs and jobs with the ‘Report Server’ category. The ‘Report Server’ category was mainly added to reduce noise from subscriptions created by reporting services.

The procedure accepts two parameters:

• @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.

Typically this procedure gets scheduled once a day in the morning so that any items it finds can be resolved before the day gets going.

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 name = 'SQLAgentJobsWithoutNotify')

    DROP PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]

GO

/*============================================================

Procedure:    [Alert].[SQLAgentJobsWithoutNotify]

Author:       Jason Strate

Date:         8-11-2004

Synopsis:

Exec [Alert].[SQLAgentJobsWithoutNotifyEmail] 

        @ToAddress = 'jstrate@hotmail.com'

        ,@SubjectWarning = 'TEST ALERT'

============================================================

Revision History:

Date:         By                Description

------------------------------------------------------------

============================================================*/

CREATE PROCEDURE [Alert].[SQLAgentJobsWithoutNotify]

    (

    @ToAddress varchar(255)

    ,@SubjectWarning varchar(255) = ''

    )

AS

IF EXISTS (SELECT *

    FROM msdb.dbo.sysjobs sj 

        LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id

    WHERE sj.enabled = 1

    AND sj.notify_email_operator_id = 0 

    AND sj.notify_level_eventlog = 0

    AND c.[Name] <> 'Report Server')

BEGIN

    DECLARE @Subject nvarchar(500),

        @Body nvarchar(500),

        @SQL nvarchar(max)

    SELECT @Subject = @SubjectWarning + SPACE(1) + @@SERVERNAME + ': MSDB Job Notification Audit',

        @Body = 'The following SQL Agent jobs do not have either e-mail or event log notifications configured to alert in the event that the job fails.',

        @SQL = 'SET NOCOUNT ON; 

            SELECT CONVERT(varchar, COALESCE(sos.originating_server, @@SERVERNAME)) as [Originating Server], 

                CONVERT(varchar, sj.[name]) as [Job Name], 

                COALESCE(STUFF(STUFF(next_run_date, 7, 0, ''/''), 5, 0, ''/'') + Space(1) + 

                    STUFF(STUFF(next_run_time, 5, 0, '':''), 3, 0, '':''), ''Unscheduled'') as [Next Run Date],

                CONVERT(varchar, c.[name]) as [Category Name]

            FROM msdb.dbo.sysjobs sj 

                INNER JOIN msdb..sysjobschedules sjs ON sj.job_id = sjs.job_id

                LEFT OUTER JOIN msdb.dbo.syscategories c ON sj.category_id = c.category_id

                LEFT OUTER JOIN msdb.dbo.sysoriginatingservers sos ON sj.originating_server_id = sos.originating_server_id

            WHERE sj.enabled = 1

            AND sj.notify_email_operator_id = 0 

            AND sj.notify_level_eventlog = 0

            AND c.[Name] <> ''Report Server'''

    EXEC msdb.dbo.sp_send_dbmail 

        @recipients = @ToAddress, 

        @subject = @Subject,

        @body = @Body,

        @execute_query_database = 'DBADiagnostics',

        @query = @SQL,

        @query_result_separator = '|'

END

GO

P.S.  This was supposed to go out last Friday which would have put some context around my other post on my DBADiagnostics database.  But I went to the lake instead… so yeah.  Priorities.