November 5, 2015 at 3:03 am
Hi
My final code is:
-- Delete #Temp if it exists
if object_id('tempdb..#TempB', 'U') is not null
drop table #TempB
create table #TempB (ID int IDENTITY(1,1), [POLICY_DETAIL_SKEY] int)
-- Create a clustered index for efficiency
CREATE UNIQUE CLUSTERED INDEX TEMP__CL ON #TempB ( ID ) WITH ( FILLFACTOR = 100 )
-- Populate #Temp with all 'SIC' records
INSERT INTO #TempB
SELECT POLICY_DETAIL_SKEY--POLICY_DETAIL_BKEY
FROM [F_POLICY_DETAIL]
WHERE [POLICY_DETAIL_BKEY] like 'SIC%'
ORDER BY POLICY_DETAIL_SKEY
DECLARE @intLoop int = 1
-- Repeatedly loop and delete
WHILE 1 = 1
BEGIN
DELETE D
FROM #TempB AS T
JOIN [F_POLICY_DETAIL] AS D
ON D.POLICY_DETAIL_SKEY = T.POLICY_DETAIL_SKEY
WHERE T.ID BETWEEN @intLoop AND @intLoop + 50000
IF @@ROWCOUNT < 50000 BREAK;
SELECT @intLoop = @intLoop + 50000
END
drop table #TempB
This works fine when I just run it from Query Analyser
I've dropped it into a SSIS package and get the occasional deadlock issue:
Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim
I could run a trace on this but I'm just wondering why I would get this if this is the only code been run
Any thoughts?
Thanks
- Damian
November 5, 2015 at 6:48 am
How did you identify the above batch was the only code being run, without having collected a trace (or extended event)? Is the database single_user?
November 5, 2015 at 9:23 am
It's a development server and I'm the only person using it at the moment
I don't think (?) there's anything else going on
- Damian
November 5, 2015 at 9:58 pm
Could the deadlock winner be replication or a scheduled job? Could be an intraparallelism deadlock or a session/spid/query window that has lost track of its @@trancount. The newest and least intrusive way to collect deadlock details is described by http://qa.sqlservercentral.com/articles/deadlock/65658/
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply