January 5, 2017 at 10:47 pm
Can you pls help in configuring of alerts like dead lock , disk space , job failed , backup status , db offline , memory utilization , long running quereries etc.. ..in our sql servers , we have more tan 30+ servers , I need to configure the alerts in a centralized server.
Replies are very helpful..
Thanks
January 5, 2017 at 11:35 pm
Hi
Check out Redgate SQL monitor software. Very easy to set up and use and can monitor everything you mentioned.
January 6, 2017 at 12:01 am
no budget we have configure manually..
January 6, 2017 at 1:15 am
ramyours2003 (1/5/2017)
Can you pls help in configuring of alerts like dead lock , disk space , job failed , backup status , db offline , memory utilization , long running quereries etc.. ..in our sql servers , we have more tan 30+ servers , I need to configure the alerts in a centralized server.Replies are very helpful..
Thanks
Have you set up a central management Server to control the group as a single unit?
If not then you should do that first.
Do you have a centralised monitoring system such as SCOM?
January 6, 2017 at 1:46 am
we have a central server to configure all the alerts ..
January 6, 2017 at 7:24 am
if you have SQL Server Agent running on the instances you want to monitor, it has a built in ability to notify by e-mail on a number of errors that are logged by SQL Server:
SELECT * FROM sys.messages m WHERE m.language_id = 1033 AND severity < 19 AND is_event_logged = 1
Just setup an Operator in SQL Server Agent with an e-mail address, ensure database mail is setup with a profile and account, then you can add the alerts as so:
-- Error 823: Read Write Request Failure
EXEC sp_add_alert @name = 'Error 823: Read or Write request failure', @message_id=823, @Severity=0, @enabled=1,@delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 823: Read or Write request failure', @operator_name='DBAs', @notification_method = 1
-- Error 824: Read Write Request Failure
EXEC sp_add_alert @name = 'Error 824: Logical Consistency I/O Error', @message_id=824, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 824: Logical Consistency I/O Error', @operator_name='DBAs', @notification_method = 1
-- Error 825: Read-Retry Required
EXEC sp_add_alert @name = 'Error 825: Read-Retry Required', @message_id=825, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 825: Read-Retry Required', @operator_name='DBAs', @notification_method = 1
-- Error 1101: disk space
EXEC sp_add_alert @name = 'Error 1101: Insufficient Disk Space', @message_id=1101, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 1101: Insufficient Disk Space', @operator_name='DBAs', @notification_method = 1
--Error 1105: filegroup full
EXEC sp_add_alert @name = 'Error 1105: Filegroup Full', @message_id=1105, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 1105: Filegroup Full', @operator_name='DBAs', @notification_method = 1
--Error 9002: tran log full
EXEC sp_add_alert @name = 'Error 9002: Transaction Log Full', @message_id=9002, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 9002: Transaction Log Full', @operator_name='DBAs', @notification_method = 1
--Error 6532: .Net out of Memory
EXEC sp_add_alert @name = 'Error 6532: .Net out of Memory', @message_id=6532, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 6532: .Net out of Memory', @operator_name='DBAs', @notification_method = 1
--Error 17053: OS error
EXEC sp_add_alert @name = 'Error 17053: Operating System Error', @message_id=17053, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 17053: Operating System Error', @operator_name='DBAs', @notification_method = 1
--Error 18452: untrusted
EXEC sp_add_alert @name = 'Error 18452: Untrusted Domain', @message_id=18452, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18452: Untrusted Domain', @operator_name='DBAs', @notification_method = 1
--Error 18456: general failure
EXEC sp_add_alert @name = 'Error 18456: Login Failed', @message_id=18456, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18456: Login Failed', @operator_name='DBAs', @notification_method = 1
--Error 18470: disabled
EXEC sp_add_alert @name = 'Error 18470: Account Disabled', @message_id=18470, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18470: Account Disabled', @operator_name='DBAs', @notification_method = 1
--Error 18486: locked out
EXEC sp_add_alert @name = 'Error 18486: Locked Out', @message_id=18486, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18486: Locked Out', @operator_name='DBAs', @notification_method = 1
--Error 18487: expired
EXEC sp_add_alert @name = 'Error 18487: Password Expired', @message_id=18487, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18487: Password Expired', @operator_name='DBAs', @notification_method = 1
--Error 18488: must change
EXEC sp_add_alert @name = 'Error 18488: Password Needs Changed', @message_id=18488, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1
EXEC sp_add_notification @alert_name = 'Error 18488: Password Needs Changed', @operator_name='DBAs', @notification_method = 1
January 6, 2017 at 7:35 am
for Deadlocks and Blocking, I tend to use Event Notifications, which can be setup in any database on the instance that has Service Broker enabled:
http://www.sqlfingers.com/2014/01/sql-server-deadlock-notifications.html
something similar can be done using the blocked process report feature:
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply