September 9, 2008 at 6:34 am
It seems our main business application is freezing and i think it may be record locking.
is there any way to determine if this is the issue
we are using SQl Server 2005
Thanks,
Iain.
September 9, 2008 at 6:46 am
use master
go
select * from sysprocesses
where blocked > 0
Above query shows if there is any blocking issue.
September 9, 2008 at 1:31 pm
Additionally, you can create a job to store information about blocked/blocking processes.
Create a table to hold the results:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[blockingprocesses]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[blockingprocesses]
GO
CREATE TABLE [dbo].[blockingprocesses] (
[spid] [smallint] NOT NULL ,
[kpid] [smallint] NOT NULL ,
[blocked] [smallint] NOT NULL ,
[waittype] [binary] (2) NOT NULL ,
[waittime] [int] NOT NULL ,
[lastwaittype] [nchar] (32) COLLATE Latin1_General_BIN NOT NULL ,
[waitresource] [nchar] (256) COLLATE Latin1_General_BIN NOT NULL ,
[dbid] [smallint] NOT NULL ,
[uid] [smallint] NOT NULL ,
[cpu] [int] NOT NULL ,
[physical_io] [bigint] NOT NULL ,
[memusage] [int] NOT NULL ,
[login_time] [datetime] NOT NULL ,
[last_batch] [datetime] NOT NULL ,
[ecid] [smallint] NOT NULL ,
[open_tran] [smallint] NOT NULL ,
[status] [nchar] (30) COLLATE Latin1_General_BIN NOT NULL ,
[sid] [binary] (86) NOT NULL ,
[hostname] [nchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[program_name] [nchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[hostprocess] [nchar] (8) COLLATE Latin1_General_BIN NOT NULL ,
[cmd] [nchar] (16) COLLATE Latin1_General_BIN NOT NULL ,
[nt_domain] [nchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[nt_username] [nchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[net_address] [nchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[net_library] [nchar] (12) COLLATE Latin1_General_BIN NOT NULL ,
[loginame] [nchar] (128) COLLATE Latin1_General_BIN NOT NULL ,
[context_info] [binary] (128) NOT NULL ,
[run_date] [datetime] NOT NULL ,
[mess] [varchar] (30) COLLATE Latin1_General_BIN NULL ,
[input_buffer] [varchar] (255) COLLATE Latin1_General_BIN NULL
) ON [PRIMARY]
GO
Then create the following stored procedure:
CREATE PROCEDURE sp_blocker_blockee
AS
set nocount ON
declare @blocker_spid int, @blockee_spid int, @blockee_blocker int,@t varchar(255)
IF EXISTS (SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
BEGIN
CREATE TABLE #blockingprocesses (
[spid] [smallint] NOT NULL ,
[kpid] [smallint] NOT NULL ,
[blocked] [smallint] NOT NULL ,
[waittype] [binary] (2) NOT NULL ,
[waittime] [int] NOT NULL ,
[lastwaittype] [nchar] (32) NOT NULL ,
[waitresource] [nchar] (256) NOT NULL ,
[dbid] [smallint] NOT NULL ,
[uid] [smallint] NOT NULL ,
[cpu] [int] NOT NULL ,
[physical_io] [bigint] NOT NULL ,
[memusage] [int] NOT NULL ,
[login_time] [datetime] NOT NULL ,
[last_batch] [datetime] NOT NULL ,
[ecid] [smallint] NOT NULL ,
[open_tran] [smallint] NOT NULL ,
[status] [nchar] (30) NOT NULL ,
[sid] [binary] (86) NOT NULL ,
[hostname] [nchar] (128) NOT NULL ,
[program_name] [nchar] (128) NOT NULL ,
[hostprocess] [nchar] (8) NOT NULL ,
[cmd] [nchar] (16) NOT NULL ,
[nt_domain] [nchar] (128) NOT NULL ,
[nt_username] [nchar] (128) NOT NULL ,
[net_address] [nchar] (12) NOT NULL ,
[net_library] [nchar] (12) NOT NULL ,
[loginame] [nchar] (128) NOT NULL ,
[context_info] [binary] (128) NOT NULL,
run_date datetime NOT NULL ,
mess varchar(30) null,
input_buffer varchar (255) NULL
)
--ALL PROCESSES THAT ARE BLOCKING OTHER PROCESSES BUT ARE NOT BLOCKED
insert into #blockingprocesses([spid],[kpid],[blocked],[waittype],[waittime],[lastwaittype],[waitresource],
[dbid],[uid],[cpu],[physical_io],[memusage],[login_time],[last_batch],[ecid],
[open_tran],[status],[sid],[hostname],[program_name],[hostprocess],[cmd],[nt_domain],
[nt_username],[net_address],[net_library],[loginame],[context_info],run_date, mess,input_buffer)
SELECT [spid],[kpid],[blocked],[waittype],[waittime],[lastwaittype],[waitresource],
[dbid],[uid],[cpu],[physical_io],[memusage],[login_time],[last_batch],[ecid],
[open_tran],[status],[sid],[hostname],[program_name],[hostprocess],[cmd],[nt_domain],
[nt_username],[net_address],[net_library],[loginame],[context_info],
getdate() ,null, null FROM master.dbo.sysprocesses sWHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses) AND blocked=0
--ALL BLO0CKED PROCESSES.
insert into #blockingprocesses([spid],[kpid],[blocked],[waittype],[waittime],[lastwaittype],[waitresource],
[dbid],[uid],[cpu],[physical_io],[memusage],[login_time],[last_batch],[ecid],
[open_tran],[status],[sid],[hostname],[program_name],[hostprocess],[cmd],[nt_domain],
[nt_username],[net_address],[net_library],[loginame],[context_info],run_date, mess,input_buffer)
SELECT [spid],[kpid],[blocked],[waittype],[waittime],[lastwaittype],[waitresource],
[dbid],[uid],[cpu],[physical_io],[memusage],[login_time],[last_batch],[ecid],
[open_tran],[status],[sid],[hostname],[program_name],[hostprocess],[cmd],[nt_domain],
[nt_username],[net_address],[net_library],[loginame],[context_info],
getdate() ,null,null FROM master.dbo.sysprocesses WHERE blocked > 0
SELECT convert(int,spid ) 'spid'
into #tab1
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
SELECT convert(int,spid ) 'spid', convert(int,blocked) 'blocked'
into #tab2
FROM master.dbo.sysprocesses WHERE blocked > 0
create table #tab3(spid int, bstatus varchar(22), EventType varchar(22) null, Parameters int null,inbuff varchar(255) null)
--ALL PROCESSES THAT ARE BLOCKING OTHER PROCESSES BUT ARE NOT BLOCKED
DECLARE blocker_cursor CURSOR FOR
SELECT spid FROM #tab1
--ALL BLO0CKED PROCESSES.
DECLARE blockee_cursor CURSOR FOR
SELECT spid ,blocked FROM #tab2
OPEN blocker_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker_spid
WHILE (@@FETCH_STATUS =0 )
BEGIN
select @t='dbcc inputbuffer('+ convert(varchar,@blocker_spid) +') WITH NO_INFOMSGS'
insert into #tab3(EventType,Parameters,inbuff) execute(@t)
--need to do this way because of dbcc inputbuffer
update #tab3
set spid =@blocker_spid, bstatus ='BLOCKER Head of chain'
where spid is null
update t
set mess=tt.bstatus, input_buffer=inbuff
from #tab3 tt , #blockingprocesses t
where tt.spid=t.spid
and mess is null
OPEN blockee_cursor
FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker
WHILE (@@fetch_status = 0)
BEGIN
IF (@blockee_blocker=@blocker_spid)
BEGIN
select @t='dbcc inputbuffer('+ convert(varchar,@blockee_spid) +') WITH NO_INFOMSGS'
insert into #tab3(EventType,Parameters,inbuff) execute(@t)
update #tab3
set spid =@blockee_spid , bstatus = 'Blocked by: '+CONVERT(VARCHAR,@blocker_spid )
where spid is null
update t
set mess=bstatus, input_buffer=inbuff
from #tab3 tt , #blockingprocesses t
where tt.spid=t.spid
and mess is null
and tt.spid =@blockee_spid
END
ELSE
BEGIN
select @t='dbcc inputbuffer('+ convert(varchar,@blockee_spid) +') WITH NO_INFOMSGS'
insert into #tab3(EventType,Parameters,inbuff) execute(@t)
update #tab3
set spid =@blockee_spid, bstatus = 'Blocked by: '+CONVERT(VARCHAR,@blockee_blocker)
where spid is null
update t
set mess=bstatus, input_buffer=inbuff
from #tab3 tt , #blockingprocesses t
where tt.spid=t.spid
and mess is null
and tt.spid =@blockee_spid
END
FETCH NEXT FROM blockee_cursor INTO @blockee_spid, @blockee_blocker
END
CLOSE blockee_cursor
FETCH NEXT FROM blocker_cursor INTO @blocker_spid
END
CLOSE blocker_cursor
DEALLOCATE blocker_cursor
--select * from #tab3
insert into blockingprocesses
SELECT *
from #blockingprocesses
drop table #tab2, #tab1,#tab3,#blockingprocesses
END
GO
Then, run this stored procedure as a job every 10-15 minutes.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply