July 1, 2010 at 6:56 am
I am trying to test the alerts and notifications in Idera Diagnostic manager. I am trying to see how it responds to long running jobs. My question is how to simulate a scenario where a job is blocked by another process and thus making it run for a long time.
I created a job where it inserts getdate() value into a table every one minute. Now I was trying to block it by running another insert command with begin transaction. But even though it is still an open transaction it is not blocking the job.
By this I can see I need to improve my knowledge of locks and blocks. But for the time being can anyone advice any code that I can run to block this job.
The query running in the job is a simple insert statement.
Insert into <table> values (getdate())
July 1, 2010 at 7:09 am
One example can be found here.
A good summary of locking here.
BOL is a good resource here as well.
Hope this helps.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 1, 2010 at 12:08 pm
I have personally used this one and it works:
July 2, 2010 at 6:44 am
I've never had a problem getting a block this way. Open one query window:
BEGIN TRAN
UPDATE TableX
SET Col = 42
WHERE Id = 66
Open a second query window:
UPDATE TableX
SET Col = 92
WHERE Id = 66
Instantaneous block.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 6, 2010 at 12:17 am
Perfect, worked like wonder.
Thanks. 🙂
July 6, 2010 at 5:54 am
derkin (7/6/2010)
Perfect, worked like wonder.Thanks. 🙂
Which of the three answers helped? Just asking so someone else finding this thread on a search knows how to solve their problem too.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 7, 2010 at 3:04 am
Grant Fritchey (7/6/2010)
derkin (7/6/2010)
Perfect, worked like wonder.Thanks. 🙂
Which of the three answers helped? Just asking so someone else finding this thread on a search knows how to solve their problem too.
That should be your answer. I accidently pressed reply instead of quote. Thanks again.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply