pointers, solutions and scripts for the SQL DBA
not intended to replace common sense

8/20/2012

SQL Agent Alerts - Severity Condition is NOT "greater than or equal to"

I ran into some bad training that had convinced a client sys admin that the SQL Agent Alerts had the "greater than or equal to property."

As in, if I want to catch and notify an operator of all high severity errors 17 through 25, all I needed to do was set up an Alert for Severity 17.  This is false.

Here's a quick proof:

USE [msdb]
GO

--This query will not run in a batch for obvious reasons.  The high-severity error messages will kill the batch.
--Run each statement manually and observe the results.

EXEC msdb.dbo.sp_add_alert @name=N'020',
              @message_id=0,
              @severity=20,
              @enabled=1,
              @delay_between_responses=0,
              @include_event_description_in=1,
              @job_id=N'00000000-0000-0000-0000-000000000000'
GO
--This query will return date and time 0 | 0.  The new alert exists.
select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'
GO
RAISERROR (N'testingonly.  This is message %s %d.', -- Message text.
           21, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5) WITH LOG; -- Second argument.
GO
--This query will still return date and time 0 | 0.  Alert for Severity 20 has NOT been triggered by a Severity 21 error.
select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'
GO
RAISERROR (N'testingonly.  This is message %s %d.', -- Message text.
           20, -- Severity,
           1, -- State,
           N'number', -- First argument.
           5) WITH LOG; -- Second argument.
GO
--This query will return a valid date and time.  Alert for Severity 20 has been triggered by a Severity 20 error.
select name, last_occurrence_date, last_occurrence_time from msdb.dbo.sysalerts where name = '020'
GO


As you run the above script statement-by-statement (NOT in a batch), you'll see the occurrence data trigger only for an error with the severity EQUALS the defined alert state.  In this case, the Severity 21 error did NOT trigger the Severity 20 alert.

Here's a basic script from my toolbox to set up Alerts for all significant error severities, to notify your operator via email with details.  In this script, the operator is called "DBAs", and ideally would be a distribution group for all active members of the Database Administrator role in your IT department.

Make sure that database mail is setup, that SQL Agent is configured to use the appropriate Database Mail profile, and that the failsafe operator is enabled to email your DBA team.  

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 17',
              @message_id=0,
              @severity=17,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 17', @operator_name=N'DBAs', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 18',
              @message_id=0,
              @severity=18,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 18', @operator_name=N'DBAs', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 19',
              @message_id=0,
              @severity=19,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 19', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 20',
              @message_id=0,
              @severity=20,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 20', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 21',
              @message_id=0,
              @severity=21,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 21', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 22',
              @message_id=0,
              @severity=22,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 22', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 23',
              @message_id=0,
              @severity=23,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 23', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 24',
              @message_id=0,
              @severity=24,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 24', @operator_name=N'DBAs', @notification_method = 1
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'errorseverity 25',
              @message_id=0,
              @severity=25,
              @enabled=1,
              @delay_between_responses=600,
              @include_event_description_in=1
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'errorseverity 25', @operator_name=N'DBAs', @notification_method = 1
GO


More info:
Error severity info: http://msdn.microsoft.com/en-us/library/ms164086.aspx

8/16/2012

Actual Emails: The Customer Doesn't Want Full Recovery Mode


Got this question in an email.  I'll paraphrase.

William 
Hoping you can advise me on best way to handle this. 
My client doesn’t want to use SQL backups. Instead, they are using VM and a third party backup software by choice.

Without a DBA on staff, they would prefer to deal with SQL the less the better.

They do not plan on doing any transaction logs and are not concerned with restore from point of failure.
... 
Goal would be to not have transaction logs or somehow keep those logs small.   
Colleague

Here's how I replied.

So, the first question to ask a client who doesn't want transaction log backups is, "why?" 
The transaction log is not something to be afraid of. Backing up the transaction log is a simple process and clears out the log. Backup the log at regular intervals (somewhere between 15 and 60min is typical) and there is no problems. Then, you get all the advantages of point-in-time recovery, so if you have a 5:01pm disaster, you could restore all the way to 5:00pm, for example. Plus, full recovery mode is required for most SQL-based HA/DR scenarios.
Using a third party backup software is fine, but they must have purchased the SQL plugin.  This is usually an extra cost add-on for whatever backup license they bought.  You CANNOT backup an .mdf file with a file system backup and expect that to work. SQL MUST BACKUP SQL. 
That SQL plugin does exactly that for the third party software - sends commands to SQL to perform a backup. It's fine if they don't want to use sql maintenance plans or jobs to perform the backups, but they must use the sql plugin for any 3rd party enterprise backup software.
With a database in full recovery mode, you must perform full and transaction log backups, or the log will grow.
Typically, you do a nightly full and hourly tran log backups during business hours. The reason people get scared/confused/frustrated with transaction logs is that they don't realize you are supposed to back up the log (which empties it) regularly. As in, hourly at most. If they want the log to stay small, back it up every 15 minutes. BackupExec can do that. 
If they are really stubborn about not wanting to use all the DR capability that they paid for, putting the database in simple mode is fine. Just full sql backups are needed then. In simple mode, transactions aren't stored in the log after they commit. A transaction commits, there's a checkpoint, and then it is wiped from the log. A simple recovery mode database will have its transaction log grow to the size of the biggest single concurrent set of transactions at any given time. 
Never truncate a log file. Perform a log file backup, which will empty the file. The file may still be large though it is empty, in this case if you absolutely need the space back, you can shrink the log file. Never shrink a data or log file on a schedule. It creates fragmentation which hurts performance. 
Let me know if you have any questions! 
-William