Have you ever made the comment to your friends or family that you are always the last to know something? Good news travels fast but bad news doesn’t always travel fast enough. When dealing with database corruption SQL Server, which is always bad news, if you are the DBA you will want to be first on the scene to stop the spread and prevent further harm to innocent bystanders. There are many ways to be instantly notified of such sad news but unfortunately not everyone takes advantage of this. In this article, we will focus on a fast and affordable method to keep you in the know, fast.
SQL Server Alerting – Not Turned On By Default
SQL Server has a wide array of utilities to monitor itself and notify its owners when something is wrong, much like a modern automobile does today with indicators that tire pressure is low or the oil needs changing. Unlike cars though, SQL Servers alerting is not turned on out of the box. One of the simplest alerts to turn on is a check for possible database corruption and it is one that not all DBAs take advantage of. Where they are diligent in running DBCC CHECKDB commands to verify the integrity of their databases or periodically review the error logs, without an immediate alert, corruption can easily go undetected for days or even weeks. The problem with that is that once corruption makes its way to a backup file, it is generally too late to fix the problem with a database restore.
Setting Up SQL Server Agent Alerts
Let’s setup a quick alert to let us know of possible corruption. We will do this with SQL Server Agent. To begin, we will want to ensure that Database Mail is setup and configured with a valid profile and account. You can do this by connecting to your SQL Server instance in SSMS, right clicking Database Mail under the Management folder and selecting “Configure Database Mail”. You will need to supply your email server information and a valid email account. Once setup you can send a test email, also by right clicking Database Mail and selecting “Sent test email…” Assuming all goes well, we can then setup SQL Agent to use the same mail profile. Right click on SQL Server Agent in SSMS and select Properties. On the Alert System page, check “Enable mail profile” and select the profile you setup previously in Database Mail.
That is all you have to do to enable mail for SQL Agent. Now we can create an alert. Under SQL Server Agent in SSMS you will see the Alerts folder. Right click this folder and select “New Alert”. Name the alert “Possible Corruption”, leave “<all databases>” as the default option for Database name and on “Severity” select “023 –Fatal Error: Database Integrity Suspect”.
Next, on the Response page you can select “Notify operators” and either select an existing operator or create a new operator. The operator will have an email address assigned. We recommend creating a distribution list for the DBA or IT teams so that every DBA will receive notification of the event when it happens. Check “Email” and click OK. That is it. You have just created an alert that will send an email to the DBAs when a database integrity event occurs signaling possible corruption.
Note too that this event will by default also be written to the Application logs and SQL Server error logs and as an added measure written to the “suspect_pages” table in the MSDB database. Having the alert will simply ensure that you will be notified immediately so you can be the first on scene and start composing the email to IT management that you will need to take action to resolve the corruption and that they need to look for hardware issues as a possible root cause.
In an ideal world every IT department would have a global monitoring system that sends alerts to the appropriate team member if corruption occurs or is about to. An even better solution would be to have the system fix the problem for you and let you know that all is good. Such systems are possible but very expensive to build or buy. Taking advantage of SQL Server’s native diagnostics and alerting is a good first step toward being the first to know when corruption strikes.
Need a query to pull other useful information from SQL Server Agent? We’ve got one for you right here!
