Deadlock Issue when dropping/creating tables

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.

Quote:

Originally Posted by DWiggin

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...