SQL Server 2005 Alerts

Well the good news is that the issue causing SQL Server 2005 to restart itself is fixed in SP3. The bad news is that I do not have a test server to test the upgrade on. While working to get a test server setup, I decided to create some SQL Server Alerts. At least then I will know just a few moments after the event happens. Currently I would find out about the error if users reported a problem or during my daily review of the SQL Server Logs.

Here is how I setup SQL Server 2005 alerts.

1. Setup Database Mail. Just follow the wizard it will lead you to safety. When finished you can right click on Database Mail and Choose Send Test Email to make sure you have configured it properly.


2. Right Click on Database Mail and click Configure. Choose the Manage Profile Security option and click next. Make sure your public profile is set as the Default Profile.


3. Setup some operators. Give the operator a name and an email address at minimum.


4. Create your new alert. Right click alerts under SQL Server Agent and pick new alert.

  • Give your alert a name.
  • Under the Alerts will be raised based on section choose the error number.\
  • In my instance the following error message would signal the service restart: Error: 3449, Severity: 21, State: 1 So for error number I typed: 3449
  • On the response tab check the Notify Operators and choose who gets notified.
  • On the Options tab check the include alert error text in Email option. Click OK. You have now created the alert.

5. Right click on the SQL Server Agent and choose Properties. Click on the Alert System option and enable the Mail Profile. Choose Database Mail for your mail system and then choose the profile you setup in the step above. Click OK. You will then need to restart your SQL Server Agent Service.

There are tons of performance conditions and error messages you can monitor using the method above. You can also set alerts up to fire on you SQL Server Jobs. Handy if you have a job that has started failing and you want to keep a close eye on it. However if you have 100s of jobs to monitor you probably want to setup some kind of Centralized Management Server to monitor that. You do not want to go add alerts to every single one of your jobs.

Once you have the Database Mail, Operator, and Alert system setup you can explore the space and use it for all sorts of monitoring scenarios. Run wild.

Update: You can use the RAISERROR system function to test your alerts.

Advertisements

Did you check the ERRORLOG

Are you checking your SQL Server ERRORLOGS at least daily? If not you should. If you have 10 gazillion servers then I would suggest creating some kind of central script or repository so you can easily check them.  The other day I stumbled across the following error in one of our servers:

Error: 602, Severity: 21, State: 30.

Come to find out this error caused an application outage. Of course people were scratching their heads looking for answers when a quick inspection of the ERRORLOG would have clued them in on what happened to the server. This incident reminded me that I need to be more diligent about checking the ERRORLOGs on our critical servers throughout the day.

I wrote a handy script that I can execute using my Executor Keyboard Launcher. The script takes in a parameter and then will run a series of WMI queries against the server you pass. It is a combo between WMI calls and calls to a central database that stores some other information about the Server.  The script will also display the current ERRORLOG on the screen. The data is dumped onto a single HTML file report that I open up for a quick snapshot of the server.

The Data

  1. Ping the Server (WMI)
  2. Location of Server and IP Address (WMI/DB Query)
  3. Apps on the server (DB Query)
  4. Type Physical or Virtual (DB Query)
  5. Operating System version and build (WMI)
  6. Hard drives TotalSize/FreeSpace/%Free (WMI)
  7. Memory: Total/Free/%Free (WMI)
  8. Processor ProcNumber/Type/MaxClockSpeed (WMI)
  9. Processor Utilization %Util/Total Util (WMI)
  10. SQL Info Version/Port Number (DB Query)
  11. SQL Server Logs (VB Script Parse of physical File)

This is just the On Demand script. I also have a script that runs each morning that I check first thing when I get in. This is where I discovered the Error mentioned above.

Here are some of the Subs used in the VBScript to do the WMI calls

Disk Space

Sub DiskSpace
On Error Resume next
Set colItems = objWMIService.ExecQuery (“Select * from Win32_LogicalDisk where MediaType = 12”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

htmlfile.Writeline (“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Drive</td><td>%Free</td><td>TotalSize(MB)</td><td>FreeSpace(MB)</td></tr>”)
For Each objItem in colItems
freeSpace = Round((objItem.FreeSpace/objItem.Size) * 100, 2)
if freeSpace < 10.00 then
freeSpaceDS = “<font color=’red’>” & freeSpace & “</font>”
elseif freespace > 10.00 and freespace < 30 then
freeSpaceDS = “<font color=’orange’>” & freeSpace & “</font>”
else
freeSpaceDS = “<font color=’green’>” & freeSpace & “</font>”
end if

htmlfile.writeLine(“<tr><td>” & objItem.Name & “</td><td>” & freespaceDS & “</td><td>” & Round(((objItem.Size/1024)/1024),2) & “</td><td>” & Round(((objItem.FreeSpace/1024)/1024),2) & “</td></tr>”)
Next
htmlfile.writeLine(“</table”)
End Sub

On the Disk Space and Memory I added some color coding. If a certain threshold then write the data out in Red. This should help notify of any problem.

Processor

Sub Processor
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_Processor”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
htmlfile.WriteLine(“<table style=’font-size: 10pt’ border=1><tr style=’background-color: #DDDDDD; font-weight: bold;’><td>Processor</td><td>Name</td><td>MaxClockSpeed</td></tr>”)
For Each objItem5 in colItems5
htmlfile.WriteLine(“<tr><td>” & objItem5.DeviceID & “</td><td>” & objItem5.Name & “</td><td>” & objItem5.MaxClockSpeed & “</td></tr>”)
Next
htmlfile.WriteLine(“</table><br>”)
End Sub

Operating System

Sub OS
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_OperatingSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if
For Each objItem5 in colItems5
htmlfile.WriteLine(“OS: ” & objItem5.Caption & ” — ” & objItem5.CSDVersion & ” — ” & objItem5.BuildNumber & “<br>”)
Next
End Sub

Hardware

Sub Hardware
On error Resume Next
Set colItems5 = objWMIService.ExecQuery (“Select * from Win32_ComputerSystem”)
If err.Number <> 0 then
htmlfile.WriteLine(“Error Executing WMI Query on ” & strComputer & ” ” & Err.Description)
Err.Clear
Exit Sub
End if

For Each objItem5 in colItems5
htmlfile.WriteLine(“Hardware: ” & objItem5.Manufacturer & ” — ” & objItem5.Model & “<br>”)
Next
End Sub

Returns the Make and Model of the hardware of the server.

Extract Date from datetime

Here is a quick script to extract date from a date time stamp column:

select CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn)) from yourtable

So this value in the table: 2009-11-04 13:00:01.000
Will return this value: 2009-11-04 00:00:00.000

Worked well for me. I had a process that dumped data into a history table. The process took a few minutes to run and only ran once a day. I wanted to hook up a ASP page to it and give the end user some dates to chose based on a query of the history table. I ended up using this:

select DISTINCT(CONVERT(DATETIME, DATEDIFF(dd,0,yourcolumn))) from yourtable

This gave me a list of dates in my history table. Found this date hack and other cool tricks on this page: http://wiki.lessthandot.com/index.php/SQL_Server_Programming_Hacks_-_100%2B_List

sysjobhistory run_duration Conversion

Below is a quick script to convert the run_duration column into 3 separate columns for Hours, Minutes, and seconds. The script will also convert the run_date column to a date. Useful for dropping into Excel and running reports.

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),
 run_duration/10000 Hours, --hours
 run_duration/100%100 Minutes, --minutes
 run_duration%100 Seconds --seconds
 from sysJobHistory

Just add a job_id in your where clause if you want to track a single job. I used this script to generate a report of run times for a specific job. We had a SQL job run long (so we thought) and wanted to drill into the numbers a bit. After some investigation we discovered that the job was pretty consistent and that the long run time was consistent with the job history.

You can also just combine the hours, minutes, and seconds into 1 column like so:

SELECT
 runDate = CONVERT (DATETIME, RTRIM(run_date)),CAST(run_duration/10000 as varchar)  + ':' + CAST(run_duration/100%100 as varchar) + ':' + CAST(run_duration%100 as varchar)
 from sysJobHistory

From there you can paste the data into Excel and chart it out or do whatever else you want to do with that data.jobscatter

Simple Stored Procedure with Output Parameter

While working on new project at work this week, I have created a set of five stored procedures to accomplish various tasks. I thought I would share a quick example of a stored procedure that returned a success flag.

create procedure sp_procedure1
@id int,
@success int OUTPUT
AS

update table1 set col1 = ‘New Value’ where id = @id
IF @@ROWCOUNT = 0
SELECT @success = 0
ELSE
SELECT @success = 1
GO

Then to call the stored procedure you would do something like this

DECLARE @success int
exec sp_procedure1 1, @success OUTPUT
select @success

This call will execute your stored procedure. If a row is updated then you will receive a success flag of 1 back from the sp. If zero rows are updated then you will receive a success flag of 0 back from the sp.

Before working with the ouptput parameters extensivley this week, I found myself constantly hitting BOL to look this concept up. Now I can just hit this awesome blog instead if I need a quick refresher, assuming I remember to look at this blog….

Automate DBCC CHECKDB scan

Update 10/30/2014 – I wrote this original article a long long long time ago. Geez its terrible. If you are not using Ola scripts for this kind of stuff you should check it out. https://ola.hallengren.com/

Original Article

Nothing like that sinking feeling when you have corruption in a production database. Regardless if you have a bullet proof backup strategy, database corruption can cause data loss if corruption goes undetected. One of the best ways to combat corruption is to know about the db corruption ASAP.

This article will not discuss the nitty gritty of DBCC CHECKDB or database corruption. That may be in a future article. Below are the steps I take to monitor Database corruption. This solution is not very elaborate but gets the job done. Let me know how to make it better.

First create a job on the server you wish to run the CheckDB scan on using this script:

Set Nocount on
Declare @dbname varchar(100)

Declare db Cursor For    –Cursor that holds the names of the databases without Pubs and Northwind
Select name from master.dbo.sysdatabases

Declare @osql varchar(1000)

Open db
Fetch Next from db into @dbname
While @@Fetch_status=0
Begin
Set @osql=’EXEC master.dbo.xp_cmdshell ‘+””+’osql -E -Q”DBCC Checkdb (“‘+@dbname+'”)” -oE:\scripts\checkdb\logs\’+@dbname+’.log’+””
EXEC (@osql) –Execute the osql statement
Fetch Next from db into @dbname
End
Close db

Deallocate db

Make sure you change the location of the log files to the location you will parse in the VBScript step below. Change this: E:\scripts\checkdb\logs\ to where you want the DBCC logs to be dumped.

Next you will make a Scheduled task to execute this VBScript.

‘Change these to work in your environment
logLocation = “E:\scripts\checkdb\logs”
attachLocation = “E:\scripts\checkdb\”
fromAddress = “YOURSERVER@mail.com”
emailServer = “mail.whatever.com”
youremail = “dude@whatever.com”

set fso = CreateObject(“Scripting.FileSystemObject”)
set outputLog = fso.OpenTextFile(“checkDBScan.log”,2,True)
FailFlag = 0

Set f = fso.GetFolder(logLocation)
Set fc = f.Files
if fc.Count = 0 then
Call SendCDOEmail(“**** WARNING **** CheckDB No Logs”, “No CheckDB logs are there. This may be a problem.”, youremail)
Wscript.Quit
End if

For Each f1 in fc
if f1.DateLastModified < Now – 1 then
Call SendCDOEmail(“**** WARNING **** Log Date Problem!!!”, “The dates did not check out on the CHECKDB Script. Connect to the server and find out if the SQL job failed.”, youremail)
WScript.Quit
end if

Next
‘——————————————————-
‘–        PARSE and LOOP
‘–
‘–
‘–     Following for statement will open each checkdb
‘–        log and parse the contents. Looking for 8000 msgs
‘–        if we find an 8000 message we will set the fail
‘–        flag to 1 indicating we have some corruption
‘–         somewhere
‘–
‘–
‘——————————————————
Set f = fso.GetFolder(logLocation)
Set fc = f.Files

For Each f1 in fc

set logfile = fso.OpenTextFile(f1.path, 1,True)

while not logfile.AtEndOfStream

lineread = logfile.ReadLine

if left(lineread,5) = “Msg 8” then
FailFlag = 1
outputLog.WriteLine(Now)
outputLog.WriteLine(“We have a serious problem here:” )
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
lineread = logfile.Readline
outputLog.WriteLine(”      ” & f1.Name & “::::   ” &  lineread)
outputLog.WriteLine
outputLog.WriteLine

end if
wend

logfile.Close
Next

outputLog.Close

if FailFlag = 1 then
Call SendCDOEmailAttach(“***** FAILURE ******* CHECK DB Problem!!!”, “The attached log file details a serious CHECKDB error. Connect to the server and investigate ASAP.”, youremail, attachLocation & “checkDBScan.log”)
end if

‘——————————————————-
‘–        EMAIL Sub
‘–
‘–
‘–     This subroutine will send the email. Takes in some
‘–        parameters to get the job done.
‘–
‘–
‘——————————————————

Sub SendCDOEmailAttach(sSubject, sMessage, sToAddress,sAttachment)

Set myMail=CreateObject(“CDO.Message”)
myMail.Subject=sSubject
myMail.From=fromAddress
myMail.To=sToAddress
myMail.TextBody=sMessage
myMail.AddAttachment sAttachment
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;)=2
‘Name or IP of remote SMTP server
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;)=emailServer
‘Server port
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) =25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

End Sub

Sub SendCDOEmail(sSubject, sMessage, sToAddress)

Set myMail=CreateObject(“CDO.Message”)
myMail.Subject=sSubject
myMail.From=fromAddress
myMail.To=sToAddress
myMail.TextBody=sMessage
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/sendusing&#8221;)=2
‘Name or IP of remote SMTP server
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserver&#8221;)=emailServer
‘Server port
myMail.Configuration.Fields.Item (“http://schemas.microsoft.com/cdo/configuration/smtpserverport&#8221;) =25
myMail.Configuration.Fields.Update
myMail.Send
set myMail=nothing

End Sub

At the top of the script there are a list of variables. You will need to change these to fit your needs. Make sure you run the Scheduled task after the DBCC CHECKDB job has finished. The script will parse the CHECKDB logs looking for “8000 Messages”. The Error code 8XXX indicates an error was found in the CHECKDB. The script will then fire off an email to let you know there is an issue.

Here is a sample Corrupted database you can play with: broken (rename this to a .bak or whatever). Just restore this .bak file to a SQL instance and run a CHECKDB against it. You will see some 8000 error messages. You can point the above VBScript to the DBCC CHECKB log and then test to make sure the script is working and properly notifies you.

Again the best tool in combating  corruption is to know about it as quickly as possible. I have read articles that say running a CHECKDB daily is excessive. However, say you have database corruption on Monday, you do not run the CHECKDB job until SUNDAY. You find out Sunday you have DB Corruption. Depending on the corruption, if your only option is to restore the db, you may lose data for the entire week because you have backed up a corrupt database all week.

If you are running a CHECKDB weekly, then at least do yourself a favor and make sure and check the logs each week or setup some kind of script to parse the logs to let you know if errors were encountered. Speaking from experience, I had a production database go corrupt. I did not know about it for 15 days. By that time we did not have a good backup to restore to (tape rotations), so I had to run a DBCC CHECKDB WITH REPAIR_ALLOW_DATA_LOSS…yeah that is as bad as it sounds. Fortunately the DB was repaired (took 12 hours of downtime).

Some Other CHECKDB Resources: