We are running a query in SQL Server 2005 that makes use of temporary
tables and table variables. Occassionally a call to this query locks
up and subsequent calls timeout. The only way to get out of this is to
restart SQL Server which is a real pain.
This is an extract from the error log with the relevant information:
2007-09-12 11:43:53.21 spid4s Deadlock encountered .... Printing
deadlock information
2007-09-12 11:43:53.21 spid4s Wait-for graph
2007-09-12 11:43:53.21 spid4s
2007-09-12 11:43:53.21 spid4s Node:1
2007-09-12 11:43:53.21 spid4s OBJECT: 2:12221068:0
CleanCnt:3 Mode:Sch-S Flags: 0x0
2007-09-12 11:43:53.23 spid4s Grant List 2:
2007-09-12 11:43:53.23 spid4s Owner:0x03E85540 Mode: Sch-S
Flg:0x0 Ref:1 Life:00000000 SPID:65 ECID:0 XactLockInfo: 0x066F59CC
2007-09-12 11:43:53.23 spid4s SPID: 65 ECID: 0 Statement Type:
INSERT Line #: 13
2007-09-12 11:43:53.23 spid4s Input Buf: RPC Event: Proc
[Database Id = 5 Object Id = 1362103893]
2007-09-12 11:43:53.23 spid4s Requested By:
2007-09-12 11:43:53.23 spid4s ResType:LockOwner Stype:'OR'Xdes:
0x271F3178 Mode: Sch-M SPID:65 BatchID:0 ECID:0 TaskProxy:(0x26E78364)
Value:0x4938be0 Cost:(N/A)
2007-09-12 11:43:53.23 spid4s Deadlock monitor failed to resolve
this deadlock.
Server may require restart to recover from this condition
2007-09-12 11:43:53.23 spid14s deadlock-list
2007-09-12 11:43:53.23 spid14s deadlock victim=process0
2007-09-12 11:43:53.23 spid14s process-list
2007-09-12 11:43:53.23 spid14s process id=process91eb68
waitresource=OBJECT: 2:12221068:0 waittime=12625 ownerId=30369101
transactionnameüheckAndCleanupCachedTempTable
lasttranstarted 07-09-12T11:43:40.607 XDES=0x271f3178 lockMode=Sch-M
schedulerid=3 kpid=556 status=suspended spid=65 sbid=0 ecid=0
priority=0 transcount=1 lastbatchstarted 07-09-12T11:43:40.403
lastbatchcompleted 07-09-12T11:43:40.403 clientapp=.Net SqlClient
Data Provider hostname=DJR_SERVER_1 hostpid=3396 loginname=PPUser
isolationlevel=read committed (2) xactid=30368721 currentdb=5
lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
2007-09-12 11:43:53.23 spid14s executionStack
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.CurrentSchedule line=13 stmtstart=860
stmtend=1188
sqlhandle=0x03000500a7cf9334a4dff000609900000000000000000000
2007-09-12 11:43:53.23 spid14s insert into [at] ScheduleTable
2007-09-12 11:43:53.23 spid14s select * from
dbo.LiveSchedule( [at] channelid)
2007-09-12 11:43:53.23 spid14s -- Get the last item in the
Automation Xml i.e. the item with the greatest start time
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.sp_selectcontentfieldstrails line=95
stmtstart=6514 stmtend=8304
sqlhandle=0x03000500abe7e85620ebb400a69900000100000000000000
2007-09-12 11:43:53.23 spid14s if (( [at] master = 0) and
2007-09-12 11:43:53.23 spid14s (exists (select * from
#TrailScheduleList as tsl
2007-09-12 11:43:53.23 spid14s join ContentProgramme as
cp
2007-09-12 11:43:53.23 spid14s on tsl.ProgrammeID =
cp.ProgrammeID
2007-09-12 11:43:53.23 spid14s join
2007-09-12 11:43:53.23 spid14s (
2007-09-12 11:43:53.23 spid14s -- This finds the next
programme id in the Schedule on the specified channel
2007-09-12 11:43:53.23 spid14s -- that is not a
continuation of the current programme i.e. that is the
2007-09-12 11:43:53.23 spid14s -- first part of the
programme
2007-09-12 11:43:53.23 spid14s select top 1 Identifier
as ProgrammeID
2007-09-12 11:43:53.23 spid14s from
dbo.CurrentSchedule( [at] channelid)
2007-09-12 11:43:53.23 spid14s where Start >
[at] sequencestart and FirstProgrammePart = 1
2007-09-12 11:43:53.23 spid14s order by Start
2007-09-12 11:43:53.23 spid14s ) as n
2007-09-12 11:43:53.23 spid14s on n.ProgrammeID =
cp.ProgrammeID
2007-09-12 11:43:53.23 spid14s where n.ProgrammeID =
cp.ProgrammeID and
2007-09-12 11:43:53.23 spid14s -- Make sure that the
programme that it is associated with is within 9 minutes of coming on
air
2007-09-12 11:43:53.23 spid14s tsl.Start <=
(DATEADD(second, 9 * 60, [at] sequencestart)))))
2007-09-12 11:43:53.23 spid14s frame
procname=DJR_DATABASE.dbo.sp_selectcontentfields line=143
stmtstart=9438 stmtend=9892
sqlhandle=0x03000500550e3051be910501949900000100000000000000
2007-09-12 11:43:53.23 spid14s insert into #ContentData
2007-09-12 11:43:53.23 spid14s exec
sp_selectcontentfieldstrails [at] contentid, [at] channelid,
[at] contentchannelid, [at] sequencestart, [at] pagestart, [at] roundminute,
2007-09-12 11:43:53.23 spid14s [at] master,
[at] description, [at] categoryid, [at] voiceoverfile output
2007-09-12 11:43:53.23 spid14s inputbuf
2007-09-12 11:43:53.23 spid14s Proc [Database Id = 5 Object Id =
1362103893]
2007-09-12 11:43:53.23 spid14s resource-list
2007-09-12 11:43:53.23 spid14s objectlock lockPartition=0
objid=12221068 subresource=FULL dbid=2 objectname=tempdb.dbo.#00BA7A8C
id=lock3e2cac0 mode=Sch-S associatedObjectId=12221068
2007-09-12 11:43:53.23 spid14s owner-list
2007-09-12 11:43:53.23 spid14s owner id=process91eb68
mode=Sch-S
2007-09-12 11:43:53.23 spid14s waiter-list
2007-09-12 11:43:53.23 spid14s waiter id=process91eb68
mode=Sch-M requestType=wait
2007-09-12 11:43:53.49 spid4s Using 'dbghelp.dll' version '4.0.5'
2007-09-12 11:43:53.51 spid4s **Dump thread - spid = 4, PSS =
0x03E087D8, EC = 0x03E087E0
2007-09-12 11:43:53.51 spid4s ***Stack Dump being sent to d:
\Database Logs\SQLDump0022.txt
2007-09-12 11:43:53.51 spid4s *
************************************************************ *******************
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * BEGIN STACK DUMP:
2007-09-12 11:43:53.51 spid4s * 09/12/07 11:43:53 spid 4
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s * Unresolved deadlock
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
2007-09-12 11:43:53.51 spid4s *
************************************************************ *******************
2007-09-12 11:43:53.51 spid4s *
------------------------------------------------------------ -------------------
2007-09-12 11:43:53.51 spid4s * Short Stack Dump
2007-09-12 11:43:53.60 spid4s Stack Signature for the dump is
0x000000F3
2007-09-12 11:43:56.89 spid4s External dump process return code
0x20000001.
External dump process returned no errors.
Has anyone got an ideas what might be going wrong?
Regards
Ian Hannah
