Postings

I just wanted to add a note about my posts. I’ve been posting once a month, but I have new goal of posting once a week. The main content that I’ve been posting is from a mini presentation I did for a SQL Saturday here in Atlanta and again to the user’s group Atlanta.MDF.  They are mainly DBA helpful stored procedures that I created and modified over the years.  I have a few more from this content, and then I’ll have to come up with new stuff to post.  My dad was in the newspaper business back in the day, although he had daily deadlines, I’m giving myself a weekly one for my postings

 

Application Alerts!

We had a need to check application processes once and a while to be sure that they are running.  So I created this table and stored procedure for an alerts jobs or exception reporting.  At the time we did not have Data Driven Subscriptions in SSRS, which can do the same thing as this process, and I haven’t had the time to convert it.

It’s a simple structure; the table holds the information about what to check, the stored procedure to run using sp_ExecuteSQL, when to run it, how often to check it, who to send emails to using our friendly SQL mailer sp_send_dbmail. The store procedure reads from the table, figures out which alerts need to run. The store procedure that gets called needs to have two Output parameters @AlertStatus (INT) that will be 1 or 0 and @Info Varchar(500) which is additional information that will be put into the email. See BOL on how to use sp_send_dbmail and sp_ExecuteSQL.

Create a job and a schedule that runs the stored procedure, Alert_Monitor, at the least interval needed

 Table:

Create table Alerts(

      AlertID int identity(1,1),          -- Unique alert ID

      StoredProcedure nvarchar(200),      -- Stored proc to run - must have @AlertStatus tinyint OUTPUT

      EmailRecipients nvarchar(200),      -- Who gets emails - comma separated list

      EmailSubject nvarchar(100),         -- Subject -- servername will be prefaced so we know live from test

      EmailBody nVarchar(300),            -- Email Body

      AlertStart24Hour int,               -- 24 hour/minutes clock when to start

      AlertStop24Hour int,                -- 24 hour/minutes clock when to stop

            AlertMonday [bit],

            AlertTuesday [bit],

            AlertWednesday [bit],

            AlertThursday [bit],

            AlertFriday [bit],

            AlertSaturday [bit],

            AlertSunday [bit],

      MinutesBetweenChecks int,           -- How many minutes to wait before checking again

      WhoOwnsThisAlert varchar(100),      -- just to keep track of ownership

      LastCheckTime DATETIME DEFAULT (GETDATE()), -- last check time

      IncludeOnDeveloperOnCall [bit]

)

The last field IncludeDeveloperOnCall  was added so whenever it’s run it will look at a table that tells which developer is on call and to get it and add the email/page to the recipients of the email.

 Code:

CREATE PROC [dbo].[Alert_Monitor]

AS /*

 created 10/23/2007

      This looks at the alerts defined in the Alerts Table in DBA_OPS

This will run every 5 to 20 minutes as a job.

*/

    SET nocount ON

-- define temp table and load with alerts to check

    DECLARE @Alerts TABLE

        (

          AlertID INT,

          processed INT DEFAULT ( 0 )

        )

    INSERT  INTO @Alerts ( AlertID )

            SELECT  AlertID

            FROM    Alerts WITH ( NOLOCK )

            -- Are we between the start and stop time?

            WHERE   DATEPART(HOUR, GETDATE()) * 100 + ( DATEPART(mi, GETDATE()) ) BETWEEN AlertStart24Hour

                                                                                  AND     AlertStop24Hour

                              -- is it time to run it again?

                    AND DATEADD(mi, MinutesBetweenChecks, LastCheckTime) <= GETDATE()

                    -- are we running it today?

                    AND CASE DATEPART(dw, GETDATE())

                          WHEN 1 THEN AlertSunday

                          WHEN 2 THEN AlertMonday

                          WHEN 3 THEN AlertTuesday

                          WHEN 4 THEN AlertWednesday

                          WHEN 5 THEN AlertThursday

                          WHEN 6 THEN AlertFriday

                          WHEN 7 THEN AlertSaturday

                        END = 1

    DECLARE @storedProcedure NVARCHAR(130),

        @Recipients NVARCHAR(200),

        @Subject NVARCHAR(200),

        @Message NVARCHAR(500),

        @ReturnCode INT,

        @ReturnInfo VARCHAR(500),

        @ParameterDefinition NVARCHAR(50),

        @AlertID INT,

        @email VARCHAR(50)

    DECLARE @RecTable TABLE ( email VARCHAR(50) )

    SET @ParameterDefinition = N'@AlertStatus INT OUTPUT,@Info VARCHAR(500) OUTPUT'

    SET @Recipients = ''

--  add any other email/pagers from other source table – Developer on call Table

    INSERT  @RecTable

            SELECT  SomeEmail

            FROM    Emailtable

            WHERE   GETDATE() BETWEEN StartDate AND EndDate

-- create list of recipients

    WHILE EXISTS ( SELECT   *

                   FROM     @RecTable )

        BEGIN

            SET @email = ( SELECT   MIN(email)

                           FROM     @RecTable

                         )

     

            SET @Recipients = CASE WHEN LEN(@Recipients) = 0 THEN ''

                                   ELSE @Recipients + ';'

                              END + @email

     

            DELETE  FROM @RecTable

            WHERE   email = @email

     

        END

-- loop through alerts and check

    WHILE EXISTS ( SELECT   *

                   FROM     @Alerts )

        BEGIN

            SET @ReturnInfo = ''

            SET @ReturnCode = 0

            SET @AlertID = ( SELECT MIN(AlertID)

                             FROM   @Alerts

                           )

            SET @storedProcedure = ( SELECT StoredProcedure

                                     FROM   Alerts WITH ( NOLOCK )

                                     WHERE  AlertID = @AlertID

                                   )

            SET @storedProcedure = N'EXEC ' + @storedProcedure

                + N' @AlertStatus OUTPUT,@Info OUTPUT'

      -- call stored proc

            EXEC master.dbo.sp_executesql @storedProcedure,

                @ParameterDefinition, @AlertStatus = @ReturnCode OUTPUT,

                @Info = @ReturnInfo OUTPUT

      -- if returned nonzero then send alert

            IF @ReturnCode <> 0

                BEGIN

                    SET @Recipients = ( SELECT  EmailRecipients

                                                + CASE WHEN IncludeOnDeveloperOnCall = 1

                                                       THEN ';' + @Recipients

                                                       ELSE ''

                                                  END

                                        FROM    Alerts WITH ( NOLOCK )

                                        WHERE   AlertID = @AlertID

                                      )

                    SET @Subject = ( SELECT EmailSubject

                                     FROM   Alerts WITH ( NOLOCK )

                                     WHERE  AlertID = @AlertID

                                   ) + N' ' + @@SERVERNAME

                    SET @Message = ( SELECT EmailBody

                                     FROM   Alerts WITH ( NOLOCK )

                                     WHERE  AlertID = @AlertID

                                   )

                    SET @Message = @Message

                        + CASE WHEN LEN(@ReturnInfo) > 0 THEN '

Additional info - ' + @ReturnInfo

                               ELSE ''

                          END

                    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'mailprofile',

                        @recipients = @Recipients, @body = @Message,

                        @subject = @Subject, @body_format = 'HTML'

                END

      -- update last check time and go onto the next alert to check

            UPDATE  Alerts

            SET     LastCheckTime = GETDATE()

            WHERE   AlertID = @AlertID

            DELETE  FROM @Alerts

            WHERE   AlertID = @AlertID

        END

Getting notified of structure changes via DDL Triggers

This procedure notifies our DBA Group of table and view changes by using a Database trigger. The trigger exposes the system function EVENTDATA() which returns XML data. See EVENTDATA in BOL for schema. This parses the XML to get information needed, and then uses the old standby sp_send_dbmail. Once the trigger is created they can be managed via SSMS by the path Database\Programmability\Database Triggers

USE MyDatabase

GO

CREATE TRIGGER Notify_Table_changes_MyDataBase ON DATABASE

FOR CREATE_TABLE, DROP_TABLE, ALTER_TABLE, CREATE_VIEW, ALTER_VIEW,

DROP_VIEW

AS

/*

Can View triggers under Database\Programmability\Database Triggers

*/

DECLARE @data XML

SET @data = EVENTDATA()

DECLARE @Recipients VARCHAR(MAX),

@Body VARCHAR(2500),

@Subject VARCHAR(50)

SET @Recipients = 'DBAGroup@MyCo.com'

SET @Subject = @@SERVERNAME +':'+RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ServerName)[1]',

'nvarchar(100)'))) + '-'

+ RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/DatabaseName)[1]',

'nvarchar(100)'))) + '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)')))

+ '-' + RTRIM(LTRIM(@data.value('(/EVENT_INSTANCE/ObjectName)[1]',

'nvarchar(100)')))

SET @Body = 'User:' + CONVERT(NVARCHAR(100), SYSTEM_USER) + ' Ran:'

+ @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)')

EXEC msdb.dbo.sp_send_dbmail @profile_name = 'Dataprofile',

@recipients = @Recipients, @body = @Body, @subject = @Subject

Start Up Procedures for SQL Sever (2005)

Start up procedures execute when the SQL Server Service is started. This can be advantageous at times. First you have to set the “Scan for Startup Procs” via sp_configure. Another, according to BOL is sp_procoption this sets the “Scan for startup Procs” and which one to execute.’ Below is the code for the proc that I want to run DBA_StartUpProc when the SQL Server service is started. After the Procedure code below there is code to run sp_procoption.

The Purpose of DBA_StartUpProc is to notify me when a SQL Server Service starts, if it’s one of our production servers I have it text me and other Operators. Why? There are several reasons: We have a cluster and I want to know when it fails over – a failover basically restart SQL Server on the other node. If a server goes bump in the night and restarts I want to know so I can check the services and jobs running on that server. To verify that something did happen that was supposed to like HW/SW/OS updates that cause a reboot or restart of SQL Server

The main flow of this proc is to determine if it’s a production server, then get then get the email addresses from the operators table in msdb, then it’s just formatting the addresses and setting parameters to call msdb.dbo.sp_send_dbmail

USE [master]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROC [dbo].[DBA_StartUpProc]

AS /*

12/11/2007 Dave Ott

Purpose of this proc is to notify when service is started

production servers will send an email and a text message,

all others will get an email

*/

SET NOCOUNT ON

DECLARE @ProductionServerList VARCHAR(100),

@IsProductionServer BIT,

@recipients VARCHAR(4000),

@email VARCHAR(1000),

@Subject VARCHAR(100),

@Message VARCHAR(100)

DECLARE @emails TABLE ( Email VARCHAR(1000) )

SET @ProductionServerList = 'Server1,Server2,Server3'-- so far

SET @IsProductionServer = 0

-- set is production server flag

IF CHARINDEX(@@SERVERNAME, @ProductionServerList) <> 0

SET @IsProductionServer = 1

-- get list of emails and in the case of production text/Pager emails

INSERT INTO @emails

SELECT CASE WHEN email_address IS NULL THEN ''

ELSE email_address

END

+ -- if production get pager adress too

CASE WHEN @IsProductionServer = 1

AND pager_address IS NOT NULL

THEN CASE WHEN email_address IS NULL THEN pager_address

ELSE ';' + pager_address

END

ELSE ''

END

FROM msdb.dbo.sysoperators WITH ( NOLOCK )

WHERE email_address IS NOT NULL

-- get all Recipants

SET @recipients = ''

WHILE EXISTS ( SELECT TOP 1

*

FROM @emails )

BEGIN

SET @email = ( SELECT MIN(Email)

FROM @emails

)

SET @recipients = @recipients + -- we need a ; between emails

CASE WHEN LEN(@recipients) = 0 THEN @email

ELSE ';' + @email

END

DELETE FROM @emails

WHERE Email = @Email

END

-- send email

SET @Subject = 'SQL Server ' + @@servername + ' was started...'

SET @Message = 'Which means that it was stopped for some reason'

EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients,

@profile_name = 'Profile', @body = @Message, @subject = @Subject,

@importance = 'High'

GO

-- Accpording to books online this also sets 'scan for startup procs' in sp_configure

EXEC sp_procoption N'[dbo].[DBA_StartUpProc]', 'startup', '1'