Link to home
Start Free TrialLog in
Avatar of Adam Singleton
Adam Singleton

asked on

SSIS job failing due to deadlock

I'll start by confessing that I am unfortunately VERY new to SQL and a bit out of my depth, so your help would be massively appreciated.

I'm trying to run an SSIS job which up until recently has been working fine.

After about 15 mins the job fails and I receive

"Product Sales With View Fact:Error: SSIS Error Code DTS_OLEDBERROR. An OLE DB error has occurred. Error Code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Transaction (process ID 72)Was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

Open in new window


So I rerun the job and the same thing happens over and over.

I have had a look for any running jobs (I assume I checked correctly...) and cannot find anything.

I assume something is stuck in a locked state from where the job was previously running and had to be stopped (via job activity monitor) but I haven't got a Scooby where to go from here.

We are using MS SQL 2014.

I have been researching deadlocks but as I mentioned i'm new to SQL and i'm not getting very far
If anyone has any specific ideas or advice, that would be amazing.

Cheers
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image
Since you mentioned that you are new to SQL, please follow the steps below:
1. Enable Deadlock Trace in your SQL Server instance(Hope you have sysadmin privileges)
DBCC TRACEON(1222,-1);
2. Run your SSIS Package and if it encounters deadlock, then your Deadlock details will be available in SQL Server Error Log.
3. Try troubleshooting the deadlock details or share us to help better..
Avatar of Adam Singleton
Adam Singleton

ASKER

Thanks for the help Raja.
Ive attached a copy of the log if you wouldn't mind taking a look.

-----------

08/08/2018 12:40:56,spid22s,Unknown,waiter id=process1fb1ad848 mode=IX requestType=wait
08/08/2018 12:40:56,spid22s,Unknown,waiter-list
08/08/2018 12:40:56,spid22s,Unknown,owner id=process37f051c28 mode=S
08/08/2018 12:40:56,spid22s,Unknown,owner-list
08/08/2018 12:40:56,spid22s,Unknown,objectlock lockPartition=0 objid=917578307 subresource=FULL dbid=6 objectname=Merit.dbo.Trans id=lock268bc7100 mode=S associatedObjectId=917578307
08/08/2018 12:40:56,spid22s,Unknown,waiter id=process37f051c28 mode=S requestType=wait
08/08/2018 12:40:56,spid22s,Unknown,waiter-list
08/08/2018 12:40:56,spid22s,Unknown,owner id=process1fb1ad848 mode=IX
08/08/2018 12:40:56,spid22s,Unknown,owner-list
08/08/2018 12:40:56,spid22s,Unknown,pagelock fileid=1 pageid=4895952 dbid=6 subresource=FULL objectname=Merit.dbo.OnBusEquipment id=lock37f8eaa80 mode=IX associatedObjectId=72057594043629568
08/08/2018 12:40:56,spid22s,Unknown,resource-list
08/08/2018 12:40:56,spid22s,Unknown,Proc [Database Id = 6 Object Id = 1570820658]
08/08/2018 12:40:56,spid22s,Unknown,inputbuf
08/08/2018 12:40:56,spid22s,Unknown,@int2_STX
08/08/2018 12:40:56,spid22s,Unknown,coalesce(@StopReference<c/> '999999999999')<c/>
08/08/2018 12:40:56,spid22s,Unknown,@id_BatchNo<c/>
08/08/2018 12:40:56,spid22s,Unknown,@dat_TransDate + CAST(@str_TransTime as datetime)<c/>
08/08/2018 12:40:56,spid22s,Unknown,@dat_TransDate<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int2_Passes<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int2_Tickets<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int4_NonRevenue<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int4_Revenue<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int2_Class<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int2_AlightingStage<c/>
08/08/2018 12:40:56,spid22s,Unknown,@int2_BoardingStage<c/>
08/08/2018 12:40:56,spid22s,Unknown,@str_LocationCode<c/>
08/08/2018 12:40:56,spid22s,Unknown,@id_Module<c/>
08/08/2018 12:40:56,spid22s,Unknown,@id_Duty<c/>
08/08/2018 12:40:56,spid22s,Unknown,@id_Journey<c/>
08/08/2018 12:40:56,spid22s,Unknown,VALUES (@id_Stage<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_STX)
08/08/2018 12:40:56,spid22s,Unknown,StopReference<c/>
08/08/2018 12:40:56,spid22s,Unknown,id_BatchNo<c/>
08/08/2018 12:40:56,spid22s,Unknown,dat_TransTime<c/>
08/08/2018 12:40:56,spid22s,Unknown,dat_TransDate<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_PassCount<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_TicketCount<c/>
08/08/2018 12:40:56,spid22s,Unknown,int4_NonRevenue<c/>
08/08/2018 12:40:56,spid22s,Unknown,int4_Revenue<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_Class<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_AlightingStageID<c/>
08/08/2018 12:40:56,spid22s,Unknown,int2_BoardingStageID<c/>
08/08/2018 12:40:56,spid22s,Unknown,str_LocationCode<c/>
08/08/2018 12:40:56,spid22s,Unknown,id_Module<c/>
08/08/2018 12:40:56,spid22s,Unknown,id_Duty<c/>
08/08/2018 12:40:56,spid22s,Unknown,id_Journey<c/>
08/08/2018 12:40:56,spid22s,Unknown,INSERT INTO Trans(      id_Stage<c/>
08/08/2018 12:40:56,spid22s,Unknown,frame procname=Merit.dbo.usp_ProcessConcessionTicketSaleRecord line=71 stmtstart=5452 stmtend=7222 sqlhandle=0x0300060032d2a05dc82ecc0068a7000001000000000000000000000000000000000000000000000000000000
08/08/2018 12:40:56,spid22s,Unknown,executionStack
08/08/2018 12:40:56,spid22s,Unknown,process id=process1fb1ad848 taskpriority=0 logused=12688 waitresource=OBJECT: 6:917578307:0  waittime=2035 ownerId=29480874 transactionname=user_transaction lasttranstarted=2018-08-08T12:40:54.103 XDES=0x3743cd770 lockMode=IX schedulerid=1 kpid=87864 status=suspended spid=67 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2018-08-08T12:40:54.173 lastbatchcompleted=2018-08-08T12:40:54.173 lastattention=1900-01-01T00:00:00.173 clientapp=.Net SqlClient Data Provider hostname=hostname hostpid=5696 loginname=loginnameisolationlevel=read committed (2) xactid=29480874 currentdb=6 lockTimeout=4294967295 clientoption1=673185824 clientoption2=128056
08/08/2018 12:40:56,spid22s,Unknown,Select * from <nl/>DW_ProductSalesValidDataView<nl/>ORDER BY Trans_ID
08/08/2018 12:40:56,spid22s,Unknown,inputbuf
08/08/2018 12:40:56,spid22s,Unknown,unknown
08/08/2018 12:40:56,spid22s,Unknown,frame procname=adhoc line=1 stmtend=120 sqlhandle=0x020000004092a632e35248fc9f9c806c9af98e98c976cab20000000000000000000000000000000000000000
08/08/2018 12:40:56,spid22s,Unknown,executionStack
08/08/2018 12:40:56,spid22s,Unknown,process id=process37f051c28 taskpriority=0 logused=0 waitresource=PAGE: 6:1:4895952  waittime=2020 ownerId=29428821 transactionname=SELECT lasttranstarted=2018-08-08T12:31:20.190 XDES=0x1b5c9c180 lockMode=S schedulerid=8 kpid=53808 status=suspended spid=82 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2018-08-08T12:31:20.177 lastbatchcompleted=2018-08-08T12:31:20.177 lastattention=1900-01-01T00:00:00.177 clientapp=Microsoft SQL Server hostname=hostname hostpid=16108 loginname=loginname isolationlevel=read committed (2) xactid=29428821 currentdb=6 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
08/08/2018 12:40:56,spid22s,Unknown,process-list
08/08/2018 12:40:56,spid22s,Unknown,deadlock victim=process37f051c28
08/08/2018 12:40:56,spid22s,Unknown,deadlock-list
ASKER CERTIFIED SOLUTION
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Adam Singleton
Adam Singleton

ASKER

Thanks Raja

To be honest I haven't got a clue how to do this, I may have to pass the job on.
At least we've got somewhere to look now.

Cheers for the help
Avatar of Adam Singleton
Adam Singleton

ASKER

Thanks for your Help with this Raja.
Been getting nowhere for days
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image
Welcome, glad to help..