question

Lynn avatar image
Lynn asked

Deadlock error when running SSIS package from SP

Hi everyone,

I have an SSIS package on SQL 2005 that is called from within a stored procedure by running sp_start_job to run the job that launches the SSIS package. It then checks on the run status until the package execution is complete. The package seems to fail occasionally because of deadlocking errors. There's usually no other user transactions running in the database so I think the conflict must be coming from my process somehow. Any idea what could possibly be causing the deadlock error? I appreciate any help that you can provide.

Lynn

sql-server-2005ssis
10 |1200

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

1 Answer

·
TG avatar image
TG answered

"Troubleshooting deadlocks" can be kind of an involved process. There is plenty of good info out there though so try googling it for some in depth articles.

However, some simple tools you can use are:

Your deadlock message should include the SPIDs of the colliding processes. You can use DBCC INPUTBUFFER([spid]) to see the last command for the spid.

sp_who2 active shows all active processes and if any are currently blocked.

You can also try just the package independent of the job that monitors for completion. Perhaps it is your monitoring code that is causing the problem

If you want more help you can edit your post to include the t-sql code you are using to "check the run status" as well as basically what DB work your package is performing. Does the package touch the same tables as your monitoring code?

10 |1200

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

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.