The estimated reading time for this post is 1 minutes
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 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.
Severity Level Description
0 - 9 Informational messages that return status information or report errors that are not severe. The Database Engine does not raise system errors with severities of 0 through 9.
10 Informational messages that return status information or report errors that are not severe. For compatibility reasons, the Database Engine converts severity 10 to severity 0 before returning the error information to the calling application.
11 Indicates that the given object or entity does not exist.
12 A special severity for queries that do not use locking because of special query hints. In some cases, read operations performed by these statements could result in inconsistent data, since locks are not taken to guarantee consistency.
13 Indicates transaction deadlock errors.
14 Indicates security-related errors, such as permission denied.
15 Indicates syntax errors in the Transact-SQL command.
16 Indicates general errors that can be corrected by the user.
17 Indicates that the statement caused SQL Server to run out of resources (such as memory, locks, or disk space for the database) or to exceed some limit set by the system administrator.
18 Indicates a problem in the Database Engine software, but the statement completes execution, and the connection to the instance of the Database Engine is maintained.
19 Indicates that a non-configurable Database Engine limit has been exceeded and the current batch process has been terminated. Error messages with a severity level of 19 or higher stop the execution of the current batch.
20 Indicates that a statement has encountered a problem. Because the problem has affected only the current task, it is unlikely that the database itself has been damaged.
21 Indicates that a problem has been encountered that affects all tasks in the current database, but it is unlikely that the database itself has been damaged.
22 Indicates that the table or index specified in the message has been damaged by a software or hardware problem.
23 Indicates that the integrity of the entire database is in question because of a hardware or software problem.
24 Indicates a media failure. The system administrator may have to restore the database. You may also have to call your hardware vendor.
Reference: https://msdn.microsoft.com/en-us/library/ms164086.aspx?f=255&MSPPError=-2147217396
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).
Event ID Source Level Description
1001 BugCheck Error The computer has rebooted from a bugcheck.
257 Defrag Error The volume was not defragmented because an error was encountered: This shrink size specified is too big.
55 NTFS Error The filesystem structure on the disk is corrupt and unusable. Please run the CHKDSK utility on the volume %S.
1057 Microsoft-Windows-FailoverClustering Error The cluster database could not be loaded. The file may be missing or corrupt. Automatic repair might be attempted
1090 Microsoft-Windows-FailoverClustering Error The Cluster service cannot be started. An attempt to read configuration data from the Windows registry failed with error
1574 Microsoft-Windows-FailoverClustering Error The failover cluster database could not be unloaded. If restarting the cluster service does not fix the problem, please restart the machine.
1575 Microsoft-Windows-FailoverClustering Error An attempt to forcibly start the cluster service has failed because the cluster configuration data on this node is either missing or corrupt.
1593 Microsoft-Windows-FailoverClustering Error The failover cluster database could not be unloaded and any potentially incorrect changes in memory could not be discarded. The cluster service will attempt to repair the database by retrieving it from another cluster node.
1046 Microsoft-Windows-FailoverClustering Error Cluster IP address resource '%1' cannot be brought online because the subnet mask value is invalid. Please check your IP address resource properties.
1047 Microsoft-Windows-FailoverClustering Error Cluster IP address resource '%1' cannot be brought online because the address value is invalid. Please check your IP address resource properties.
1557 Microsoft-Windows-FailoverClustering Error Cluster service failed to update the cluster configuration data on the witness resource. Please ensure that the witness resource is online and accessible.
1558 Microsoft-Windows-FailoverClustering Error The cluster service detected a problem with the witness resource. The witness resource will be failed over to another node within the cluster in an attempt to reestablish access to cluster configuration data.
1563 Microsoft-Windows-FailoverClustering Error File share witness resource '%1' failed to come online. Please ensure that file share '%2' exists and is accessible by the cluster.
1573 Microsoft-Windows-FailoverClustering Error Node '%1' failed to form a cluster. This was because the witness was not accessible. Please ensure that the witness resource is online and available.
7000 Service Control Manager Error The %1 service failed to start due to the following error:
%2
7022 Service Control Manager Error The %1 service hung on starting.
7038 Service Control Manager Error The %1 service was unable to log on as %2 with the currently configured password due to the following error:
%3
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.
$log = get-eventlog -logname System| where-object {$_.EventID -eq 55} | Format-list
$result = $log | select-object -first 3
$filename = "C:\Temp\55.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
By using Windows Task Scheduler, Administrator is able to create trigger on each Windows Event ID such as the following figure.
SQL Server Administration by Powershell (Part 2)
SQL Server Backup/Restore and Completion Time Estimation
Security in SQL Server – Part 3
Why SQL Server Health Check Matters?
SQL Server StretchDB
Database Backup Verification Policy Automation
SQL Server Backup Performance Tuning
SQL Server and UDF’s Performance Issues
I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.
Follow Me:
Leave a Comment