January 22, 2012 at 6:10 pm
Hi
Our DBA hasn't been able to uncover the reason behind this. We have an SSIS package in out SQL2005 server to load data onto our system every night. The SSIS package consists of several ETL's(around 20) that are currently run sequentially.
On some nights it spits out below deadlock messages for certain ETL's.
Some nights no errors at all. Every night what's run on the server is the same.
The specific ETL's deadlocking are not the same. ie: Monday it'll be ETL1, ETL3, ETL9
Tuesday & Wednesday all ok. Thursday ETL2, ETL5, ETL6, ETL7
Friday all ok
Below last night's message.
Pl help uncover this Mystery
------------------------------------------------------------
Started: 9:00:22 PM
Error: 2012-01-22 21:02:02.38
Code: 0xC002F210
Source: Overnight Load Execute SQL Task
Description: Executing the query "EXEC ETL.[Overnight Load]" failed with the following error: "Transaction (Process ID 70) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
January 23, 2012 at 4:02 am
These are deadlocks caused by your SQL query. Please modify your SQL queries using "with (NOLOCK)" to avoid deadlocks.
There is nothing to do with SSIS packages.
January 23, 2012 at 10:10 am
sravani.sriram (1/23/2012)
These are deadlocks caused by your SQL query. Please modify your SQL queries using "with (NOLOCK)" to avoid deadlocks.There is nothing to do with SSIS packages.
NOLOCK will result in dirty reads .. this is not an excuse to get rid of deadlocks
more info on how to minimize deadlocks can be found at
http://msdn.microsoft.com/en-us/library/ms191242.aspx
http://blogs.technet.com/b/fort_sql/archive/2011/12/12/get-rid-of-deadlocks.aspx
also try to read on effects of NOLOCK http://www.sqlservercentral.com/articles/Performance+Tuning/2764/
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 23, 2012 at 10:10 am
sravani.sriram (1/23/2012)
These are deadlocks caused by your SQL query. Please modify your SQL queries using "with (NOLOCK)" to avoid deadlocks.There is nothing to do with SSIS packages.
NOLOCK will result in dirty reads .. this is not an excuse to get rid of deadlocks
more info on how to minimize deadlocks can be found at
http://msdn.microsoft.com/en-us/library/ms191242.aspx
http://blogs.technet.com/b/fort_sql/archive/2011/12/12/get-rid-of-deadlocks.aspx
also try to read on effects of NOLOCK http://www.sqlservercentral.com/articles/Performance+Tuning/2764/
______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂
January 24, 2012 at 5:49 am
I would check to see if the ssis jobs are using bulk inserts with table lock. I have had inserts with table lock going and then a different job kicks off using that table and deadlock.
January 24, 2012 at 3:22 pm
Thanks for all replies. Just to clarify this is just one SSIS job. All the ETL's are within that single job.
January 25, 2012 at 4:14 am
Are you sure each data load is running sequentially within the package? Are they linked by constraints? What is/are the values for MaxConcurrentExecutables property of the package? You can also set that value in the sql agent job that fires it. Try setting it to 1 and see if it still locks up.
If so then you have to investigate what else is running at the same time.
January 26, 2012 at 4:35 pm
So your SSIS package is getting killed in the deadlock
This means the various tasks get in deadlocks with other processes on the server and because usually SSIS (ETL) transactions are long, they get killed.
Solution? Identify the resources involved in the deadlocks and rework the package with better staging to avoid the locks.
January 26, 2012 at 6:42 pm
No SSIS job still completes but the ETL's that were deadlock victims fail with the message given in my original post.
January 27, 2012 at 8:39 am
well then, ignore the first line of my previous post 🙂
May 6, 2016 at 9:58 am
Easy before the last step which probably is a OLEDB write or some other sort of commit add a derived column. Don't have to change anything else. And this step is done for only those commits which are failing. 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply