It sounds like the table is locked and the process is timing out waiting for
it.
It is doubtful that the size of the table has anything to do with it. it
could be 50 million records or 5 records, I think you'd still get the same
error.
It could be a few different things. Here are some thoughts.
Either it is a job/process/user that is locking the table (update statements
are usually the #1 source of full table locks) when you try to drop it.
Investigate all the other job logs and see if there are any conflicts with
the job to drop the table. If you can't find what's holding the table with
that, then you might want to run a trace and capture the results to see if
that will help. Also, check that there aren't any ghost processes from
disconnected users that didn't get killed properly. This could keep the
table locked as well.
Also are you using the copy database wizard to do any of your jobs. The
error number below (Step Error code: 80004005) is sometimes associated with
this process, and you may want to investigate that as well as a source of
your timeout. Read
http://support.microsoft.com/default.aspx?scid=KB;EN-US;q274463& for further
information.

-----Original Message-----
From: Colin Shelton [mailto:Colin.[Email Address Removed]
Sent: Friday, September 27, 2002 3:14 AM
To: LazyDBA.com Discussion
Subject: timeout error dropping a table

Hi everyone,

Can anyone advise me where to look to try to resolve this problem please?

I have a DTS job that runs each night to extract data from a UNIX system
onto a Windows 2000 server running SQL Server 2000. Before extracting the
data I drop tables from the previous run. Most nights this works fine, but
sometimes I get the following error (given in the job run log) on the first
extract job I run:

Step 'Drop table TEDTRAN_INPUT Step' failed

Step Error Source: Microsoft OLE DB Provider for SQL Server
Step Error Description:Timeout expired
Step Error code: 80004005
Step Error Help File:
Step Error Help Context ID:0

Step Execution Started: 27/09/2002 2:00:47 AM
Step Execution Completed: 27/09/2002 2:05:00 AM
Total Step Execution Time: 253.578 seconds
Progress count in Step: 0

The table only contains around 30,000 rows & is normally dropped in a couple
of seconds. There should be no other activity on the server as the extract
is done at 2am. All of the subsequent jobs, even dropping larger tables, all
work fine. There doesn't seem to be any messages in the server event log or
server 2000 logs. Is there anything else I can look at, or do, to find out
what is going wrong?

Regards,
Colin Shelton.
IT Services,
UCE, Perry Barr, Birmingham, B42 2SU.
Tel: 0121 331 5665
Email: colin.[Email Address Removed] unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page