question

mayankbhargava avatar image
mayankbhargava asked

Deadlock while importing data to many servers.

For more explanation, I have 47 servers. Data is imported from 47 regions on daily basis to these 47 servers through a job. Almost everyday job run successfully but sometimes it results in a failure. The job throws a deadlock issue and my import results in failure. The impact of this is that I have to manually insert the data in these servers. Is there any workaround.? Thanks!! Regards, Mayank
querydeadlock
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered
The workaround is to fix whatever is causing the deadlocks. If you're running SQL Server 2008 or better, look at the [system_health extended event session][1]. It's capturing deadlock graphs which will tell you which resources are causing the deadlock, and which statements. If you're running SQL Server 2005 or older, enable traceflag 1222 to get the same information in your error log. Deadlocks are primarily a structure and performance issue. Structure because two statements are written so that they are locking resources in reverse order from each other. Performance because they're holding these locks for too long. I can't tell you a lot more because I don't know what's causing your deadlocks. [Here's an article][2] that may help you. [1]: http://support.microsoft.com/kb/2160570 [2]: https://www.simple-talk.com/sql/database-administration/handling-deadlocks-in-sql-server/
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mayankbhargava avatar image mayankbhargava commented ·
Actually, the issue is intermittent.. It is not coming daily. But whenever it is coming it is becoming impossible to reproduce it. Thanks
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Right. Don't try to reproduce it. Capture the data so you can understand what's going wrong and then fix it.
0 Likes 0 ·
mayankbhargava avatar image mayankbhargava commented ·
OK Grant. Thanks, I will try to capture the data and will come again for any doubts.
0 Likes 0 ·
ramesh 1 avatar image
ramesh 1 answered
you make some changes in the schedule to avoid, can you please explain how data is imporrted from 47 servers. i means you use a job to import the data SSIS Bulk Insert Trigger
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mayankbhargava avatar image mayankbhargava commented ·
Thanks Grant and Ramesh for reply .. @Ramesh: The data is imported in these servers through some text files which are prepared through some process through some other job. The job which i used to import the data, executes a stored procedure which insert the data in these servers.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.