Here’s a short story about a semaphore timeout error that quickly went from bad to worse and also demonstrates why you should configure SQL Server Agent Alerts.
Act 1
Once upon a time, I was having a relaxing morning…until I received an e-mail alert about an Error Number 825 on a database. The error stated:
‘D:\sql_dat\DatabaseName.mdf_MSSQL_DBCC47’ at offset 0x00000004b9e000 succeeded after failing 1 time(s) with error: 121(The semaphore timeout period has expired.). Additional messages in the SQL Server error log and system event log may provide more detail. This error condition threatens database integrity and must be corrected. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
That’s not how you want your day to go. Fortunately, the database was not one that needed to be online 24/7 so I felt I could relax a little bit while starting to investigate. At this point, the issue seemed to be with one database.
Act 2
I noticed while starting to troubleshoot the database that the server was running a bit slow. CPU looked normal or even a bit below normal utilization. I checked to see what all was running in SQL Server and noticed blocking across multiple databases. Uh oh.
I checked event viewer and found these types of errors in the System log:
The IO operation at logical block address 0x6872f97 for Disk 2 (PDO name: \Device\MPIODisk1) was retried.
At this point, I’m thinking an entire drive might be going down based on these disk errors. But as I click through them, I’m seeing these types of errors for multiple disks.
Checking the Application log while I’m in Event Viewer doesn’t make me feel any better:
SQL Server has encountered 3 occurrence(s) of I/O requests taking longer than 15 seconds to complete on file [E:\sql_dat\AnotherDatabase.mdf] in database id 148. The OS file handle is 0x0000000000002630. The offset of the latest long I/O is: 0x000000183cc000
These types of errors were showing for multiple databases.
What started out as “oh it’s one lightly used database” quickly became “this entire server is going down!”
Act 3
I soon lost the ability to run any queries on the server. When replacing SAN switches failed to make a difference, moving to a new enclosure ended up being the way to get back to normal. Running DBCC CHECKDB found no errors.
The moral of the story? CONFIGURE YOUR SQL SERVER AGENT ALERTS! With these alerts in place, the issue was noticed right away. It’s always better to find out about the errors yourself rather than receiving an angry phone call in the middle of the night. You can use this script after setting @DBMailOperator to your own:
USE [msdb];
GO
DECLARE @DBMailOperator AS VARCHAR(50);
SET @DBMailOperator = 'Your_DBMailOperator';
EXEC msdb.dbo.sp_add_alert @name = N'Severity 017'
,@message_id = 0
,@severity = 17
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 017'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 018'
,@message_id = 0
,@severity = 18
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 018'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 019'
,@message_id = 0
,@severity = 19
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 019'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 020'
,@message_id = 0
,@severity = 20
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 020'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 021'
,@message_id = 0
,@severity = 21
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 021'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 022'
,@message_id = 0
,@severity = 22
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 022'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 023'
,@message_id = 0
,@severity = 23
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 023'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 024'
,@message_id = 0
,@severity = 24
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 024'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Severity 025'
,@message_id = 0
,@severity = 25
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000';
EXEC msdb.dbo.sp_add_notification @alert_name = N'Severity 025'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Error Number 823'
,@message_id = 823
,@severity = 0
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 823'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Error Number 824'
,@message_id = 824
,@severity = 0
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 824'
,@operator_name = @DBMailOperator
,@notification_method = 7;
EXEC msdb.dbo.sp_add_alert @name = N'Error Number 825'
,@message_id = 825
,@severity = 0
,@enabled = 1
,@delay_between_responses = 60
,@include_event_description_in = 1
,@job_id = N'00000000-0000-0000-0000-000000000000'
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error Number 825'
,@operator_name = @DBMailOperator
,@notification_method = 7;
Thanks for reading!
Chad Callihan 
One thought on “Have You Configured SQL Server Agent Alerts?”