By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,408 Members | 1,840 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,408 IT Pros & Developers. It's quick & easy.

Deadlock Issue when dropping/creating tables

P: 1
We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.
Aug 8 '07 #1
Share this Question
Share on Google+
1 Reply


ck9663
Expert 2.5K+
P: 2,878
We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...
Aug 9 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.