Hi All,
Whenever there is a timeout occured from Application which was connected to
SQL Server.
I tried to get the Blocking objects from SysProcesses table of master DB.
But most of the time it will clear very soon so i couldnt able to find the
actual objects which is Blocking.
Could any one suggests some ways to get the blocking objects.
Thanks in advance...
Regards,
Herbert R.SQL Server Profiler may help, but be reminded to log only the required
information as logging eats up resources.
References
- SQL Server 2000 Administrator's Pocket Consultant: Profiling and
Monitoring (Solving Performance Problems with Profiler)
http://www.microsoft.com/technet/pr...s/c10ppcsq.mspx
Martin C K Poon
Senior Analyst Programmer
====================================
"Herbert" <Herbert@.discussions.microsoft.com> bl
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com g...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.|||Check out http://support.microsoft.com/kb/271509/ for a script that can help
monitor blocking and record the objects involved.
Hope this helps.
Dan Guzman
SQL Server MVP
"Herbert" <Herbert@.discussions.microsoft.com> wrote in message
news:1E649539-6330-4D18-878C-276251E4F915@.microsoft.com...
> Hi All,
> Whenever there is a timeout occured from Application which was connected
> to
> SQL Server.
> I tried to get the Blocking objects from SysProcesses table of master DB.
> But most of the time it will clear very soon so i couldnt able to find the
> actual objects which is Blocking.
> Could any one suggests some ways to get the blocking objects.
> Thanks in advance...
> Regards,
> Herbert R.
Showing posts with label blocking. Show all posts
Showing posts with label blocking. Show all posts
Thursday, March 29, 2012
Friday, February 24, 2012
Generate Deadlock For Test
I want to generate a deadlock for a test. Can anyone give me a method to do this? So far, I have been able to generate only blocking locks and not full deadlocks.
Thank you in advance
Try this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison
|||Greg,
'Exploring Deadlocks'
http://www.sqlservercentral.com/colu...intversion.asp
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
Thank you in advance
Try this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison
|||Greg,
'Exploring Deadlocks'
http://www.sqlservercentral.com/colu...intversion.asp
Dinesh
SQL Server MVP
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
Generate Deadlock For Test
I want to generate a deadlock for a test. Can anyone give me a method to do
this? So far, I have been able to generate only blocking locks and not ful
l deadlocks.
Thank you in advanceTry this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison|||Greg,
'Exploring Deadlocks'
asp" target="_blank">http://www.sqlservercentral.com/col...ion.
asp
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
this? So far, I have been able to generate only blocking locks and not ful
l deadlocks.
Thank you in advanceTry this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison|||Greg,
'Exploring Deadlocks'
asp" target="_blank">http://www.sqlservercentral.com/col...ion.
asp
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
Generate Deadlock For Test
I want to generate a deadlock for a test. Can anyone give me a method to do this? So far, I have been able to generate only blocking locks and not full deadlocks.
Thank you in advanceTry this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison|||Greg,
'Exploring Deadlocks'
http://www.sqlservercentral.com/columnists/rmarda/exploringdeadlocks_printversion.asp
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
Thank you in advanceTry this:
In one QA session enter the following:
create table x (x int)
BEGIN TRANSACTION
insert into x values (1)
select * from x with (TABLOCKX)
In another QA session enter:
create table y (x int)
BEGIN TRANSACTION
insert into y values (1)
select * from x
Now go back to the first QA session and enter
select * from Y.
This will cause a deadlock.
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Try this:
Run these transactions concurrently:
Tr-n One Tr-n Two
-- --
Begin transaction Begin transaction
Update table T1 Update table T2
Update table T2 Update table T1
Commit transaction Commit transaction
Thanks,
Lyudmila Fokina
Please do not send e-mail directly to this alias. This alias is for
newsgroup purposes only
Disclamer: This posting is provided "AS IS" with no warranties, and confers
no rights.
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance|||Greg,
in one QA window:
USE northwind
GO
BEGIN TRANSACTION
update Region set RegionDescription = 'Northern' where regionid = 3
WAITFOR DELAY '00:00:10'
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
commit TRANSACTION
in another:
USE northwind
GO
BEGIN TRANSACTION
update Categories set Description = 'Seaweed and fish' where CategoryName
= 'Seafood'
WAITFOR DELAY '00:00:10'
update Region set RegionDescription = 'Northern' where regionid = 3
commit TRANSACTION
HTH,
Paul Ibison|||Greg,
'Exploring Deadlocks'
http://www.sqlservercentral.com/columnists/rmarda/exploringdeadlocks_printversion.asp
--
Dinesh
SQL Server MVP
--
--
SQL Server FAQ at
http://www.tkdinesh.com
"Greg G." <anonymous@.discussions.microsoft.com> wrote in message
news:CCBDB167-53FA-4659-A137-A8DF2FAC34A5@.microsoft.com...
> I want to generate a deadlock for a test. Can anyone give me a method to
do this? So far, I have been able to generate only blocking locks and not
full deadlocks.
> Thank you in advance
Subscribe to:
Posts (Atom)