Sep200924

Alex

0

Locks and Locking

SELECT
locktype  = CASE
WHEN master..syslocks.type = 1 THEN ‘Exclusive table lock’
WHEN master..syslocks.type = 2 THEN ‘Shared table lock’
WHEN master..syslocks.type = 3 THEN ‘Exclusive intent lock’
WHEN master..syslocks.type = 4 THEN ‘Shared intent lock’
WHEN master..syslocks.type = 5 THEN ‘Exclusive page lock’
WHEN master..syslocks.type = 6 THEN ‘Shared page lock’
WHEN master..syslocks.type = 7 THEN ‘Update page lock’
WHEN master..syslocks.type = 8 [...]

Apr200921

Alex

1

Analyze Locks and Locking in SQL Server 2000

SELECT
 locktype  = CASE
   WHEN master..syslocks.type = 1 THEN ‘Exclusive table lock’
   WHEN master..syslocks.type = 2 THEN ‘Shared table lock’
   WHEN master..syslocks.type = 3 THEN ‘Exclusive intent lock’
   WHEN master..syslocks.type = 4 THEN ‘Shared intent lock’
   WHEN master..syslocks.type = 5 THEN ‘Exclusive page lock’
   WHEN master..syslocks.type = 6 THEN ‘Shared page lock’
   WHEN master..syslocks.type = 7 THEN ‘Update page lock’
   WHEN master..syslocks.type = 8 [...]

Apr200921

Alex

0

How to monitor blocking in SQL Server 2000 and 2005 using sp_blocker_pss08 script

sp_blocker_pss08 is a stored procedure useful to perform blocking analysis.
http://support.microsoft.com/kb/271509/

Apr200916

Alex

0

Kill Blocked Processes - SPIDs

Useful script to kill all blocked process.
select * from sysprocesses where blocked > 0 and spid <> blocked
declare @cmd nvarchar(800)
declare @spid int
AGAIN:
if exists (select null from sysprocesses where blocked > 0 and spid <> blocked)
begin
set @cmd = ”
set @spid = ”
select @spid = max(blocked) from sysprocesses where blocked > 0 and spid <> blocked
select @cmd [...]