Tuesday, July 2, 2013

Error Logging and Alerts in SQL Server

Error Logging is main part of our database application. I am trying to share some sample code which I am using in my daily life.
We need to follow below steps.
1) Create database.
2) Create Error Log Table.
Table: Error_Log
Columns:
  1. Log_ID
    1. Identity column.
  2. Database_Name
    1. Database Name.
  3. Proc_Name
    1. Procedure Name.
  4. Err_Number
    1. Error Number from Procedure.
  5. Err_State
    1. Error Stage from Procedure.
  6. Err_Message
    1. Error Message from Procedure.
  7. Err_Line
    1. Error Line from Procedure.
  8. Created_Date
    1. Default current date from database.
3) Create Error Log Procedure.
4) Create Procedure with error which used Error Log Procedure in 3rd Step.
5) Execute Parent Procedure. Below is error output from Parent Procedure.
image66) Check logs in Error Log table.
image
7) Create Config Table. Add below entry for Mail Configuration.
  1. Mail_From
  2. Mail_Subject_Error_Log_Alert
  3. Mail_To
8) Create Procedure to send alert mails.
9) Create Job to execute Procedure to send alert mails. Below is alert mail.
image
Below are the scripts to download.
Error Logging and Alerts.sql

No comments:

Post a Comment