SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Script to send database consistancy errors

By Nisarg Upadhyay,

Ideally, when we run the database consistancy check, we should get notified if consistency check error. To fulfil this requirement, i have created a script which will create a stored procedure which will execute database consistency check on database and email the status of DB consistency check.

Description:

This script will perform below task

  1. Script will create a parameterized store procedure named "spDB_Consistancy_Check"
  2. Stored procedure will accept parameter “@DatabaseType”. Parameter will be either 'S' Or 'U'. 'S' = be System Databases U = User Database.
  3. Based on the parameter, stored procedure will perform consistency check on databases.
  4. When procedure completes consistency check, it will send an email to notify the status. If consistency check returns any error than it will display error in email body or just say "No database consistancy error found".

How to Configure:

we need to replace 2 parameters in script before creating stored procedure

  1. Replace @Email_Profile parameter with valid database email profile
  2. Replace @Recipients parameter with valid email recipients.

E.g.

set @EMail_Profile='DEMO_Mail_Profile' --Replace this parameter with organization's email profile
set @recipients ='nisargupadhyay87@gmail.com' --Replace this with valid recipients

How to use:

  1. As mentioned in above code replace the parameter and execute the script on any database. This script will create a stored procedure and it can be executed by SQL Server agent job or we can run it manually.

Demo Code:

Use LABDB
go
Exec spDB_Consistancy_Check 'S' --S = System database

Total article views: 261 | Views in the last 30 days: 261
 
Related Articles
FORUM

Consistent Database Copy

Consistent Database Copy While Performing

FORUM

dbcc check db changing schema version

consistency check is causing a database schema version change

BLOG

Checking DBCC CHECKDB

I hope you run consistency checks on your databases, if you are not currently doing this you probabl...

FORUM

Check db command

How to run the check db command to remove consistency errors in msdb database

FORUM

SQL Server 2000 msdb Integrity check fails

SQL Server 2000 msdb Integrity check fails with consistency errors

Tags
corruption    
 
Contribute