SQL Server Advance Monitoring

SQL Server and Windows platforms provide some features for administrative alerts and notifications. SQL Server DBAs should take this advantage to perform pro-active monitoring on SQL Server, Databases, Windows Server and/or Failover Cluster setup. In this post, I will explain some of critical SQL Server and Windows Server errors.

SQL Server Alerts and Notifications

SQL Server errors are categorized between Severity 0 and Severity 24, which is the criticality of the error. By using SQL Server Database Mail feature, DBAs can monitor the instance activities near real-time via Email notifications. The followings are the critical severity errors that should be monitored actively.

[table id=5 /]

[info]SQL Server errors with severity level between 16 and 24 should be monitored and administrator needs to be notified once such error occurs. [/info]

Reference: https://msdn.microsoft.com/en-us/library/ms164086.aspx?f=255&MSPPError=-2147217396

Windows Alerts and Notifications

Windows Event Log is the source of information for us to troubleshoot SQL Server or Windows related issues. The following Events are critical to monitor and once any of them occurs, Administrator needs to respond and investigate regarding the event(s).

[table id=6 /]

How to Notify Administrator?!

To notify an administrator regarding SQL Server related errors, The database mail feature is super useful, for more information about Database Mail read this post. When it comes to Windows errors, Administrators are need to use some powershell scripts or some customized applications to run. The following PowerShell scripts emails the specific Event Log to administrator.

[codesyntax lang=”powershell”]

$log = get-eventlog -logname System| where-object {$_.EventID -eq 55} | Format-list
$result = $log | select-object -first 3
$filename = "C:Temp55.txt"
$result | out-file $filename
send-mailmessage -From "alert@Fard-Solutions.com" -To "SQLAdmin@fard-solutions.com" -Subject "SQL01-PROD Event ID 55" -SMTPServer "172.18.110.87" -Attachment $filename

[/codesyntax]

By using Windows Task Scheduler, Administrator is able to create trigger on each Windows Event ID such as the following figure.

task-scheduler

 

Share This Story

Share on facebook
Share on twitter
Share on linkedin
Share on whatsapp

Share Your Comments

About The Author

Search Articles

Categories

Follow Fard Solutions

Share Your Comments

Fard Solutions Sdn. Bhd. Company was established in 2012 in Kuala Lumpur. We are engaged in development, implementation, integration and maintenance of the most advanced database solutions and IT research & development (innovation).

(+60) 03 8994 6788

info@fard-solutions.com

Fard Technology Corp.
700-1199 West Hastings Street,
Vancouver, British Colombia,
V6E 3T5 Canada.

Fard Solutions Sdn. Bhd.
1-1C & 1-2C Incubator 1,
Technology Park Malaysia,
Bukit Jalil, 57000
Kuala Lumpur, Malaysia.

  • Copyrights © 2020 Fard Solutions Sdn. Bhd. , All rights reserved.
  • Privacy Policy
  • Terms of Service
  • A Project By Website Artisan