Create Blocking alerts & testing it..!!

below is the doc that has step by step instruction to

1) configure db mail in MSSQL. please check the doc attached in below link

try t-sql to configure DB mail in this link – t-sql_DB_Mail_creation

db_mail_config steps

or refer below links for better understanding.

http://www.codeproject.com/Articles/485124/Configuring-Database-Mail-in-SQL-Server

2) configure the db blocing alerts. please refer the below link. superb article,great work!! thanks to the author!!

http://www.practicalsqldba.com/2012/07/sql-server-instant-blocking-alert-using.html

3) test the alerts.

Testing the blocking alerts

— step 1 : create a  sample database and table in DBA Monitor


Use master

Go

Create database sample_db

go

use sample_db

go

create table test_sample_table (emp_id int ,emp_name varchar(30))

-- step 2 : insert data into table, execute ‘commit transaction’ after blocking threshold time i.e if blocking is set tp 30 secs,60 secs(1min),900 sec(15 mins) then execute the commit command as given below

use sample_db

go

begin TRANSACTION

go

insert into test_sample_table (emp_id,emp_name) values (1,'aaa'),(2,'bbb'),(3,'ccc')

go

-- commit TRANSACTION

-- step 3 : open new query window & execute below query to check the total records in sample table

use sample_db

go

select * from test_sample_table

if all the above steps are done correctly then you will get a mail.

blocking_alert_email.Untitled

Advertisements
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s