(ODBC driver) timeout expired when query "DELETE FROM mytable"

Hi,

I'm running SQL server 2000. I have at table with about 12 million records.

I want to empty the table. I use the Query option from Tables/mytable/opentable/query.

I type in the query and select Run. The process runs for some time, then raises

an error box as follows: (title)SQL Server Enterprise Manager. The body text says:

[Microsoft][ODBC SQL Server Driver] Timeout Expired. I've tried every timeout

setting I can find. I've tried setting all timeouts to 0 (infinite) to no avail.

Please help.........

Note: I can get the table empty if I select TOP n records, then DELETE FROM but

that takes forever!! It is also not a process that's very amenable to a clean programatic

solution.

Thanks, jack

That's very strange. Setting timeout to '0' should do the trick.

If all you need to do is empty the table you could just call "truncate table table_name". That should run pretty quickly.

Sorry you're having issues. Please reactivate this thread if the 'truncate' command doesn't fix your issue.

~Warren

|||

Warren,

Thanks!

I have changed the query to "Truncate Table" and it's much faster. I haven't

tried it with the large table yet -- I'll have to reload the data before I know for

sure. I used the "Delete" command only because I hadn't stumbled on the "Truncate"

command. I still would like to know why I'm getting the timeout since I'm sure it will

jump up and bite me later because I'm dealing with such large tables, and Murphy is

looking over my shoulder -- ha!

jack

|||Consider that when you execute DELETE, the operation is logged so you are essentially "moving" the deleted rows to the transaction log with all of the associated disk IO expenses. Truncate table is also logged, but simply tells SQL Server to "drop" the data and leave the schema--it's dramatically simpler and faster.|||

William,

"Dramatically" is an understatement! I'm amazed at how quickly the table is

emptied using "Truncate". Although knowing why, it makes perfect sense. Thanks

for the expanation of the 'why', that's even more valuable than the 'how'. Is there an

explicit means of preventing the transaction logging - since it's so time costly? Is

there a downside to such a thing if it exists?

I'm still bewildered about the "timeout expired" error inspired by the length of time

the DELETE takes. I guess I'll have to pull my copy of the "Guide to..." off the shelf

and review ADO/ODBC query timeouts etc.

thanks, jack

|||

No, you can't (and should not) "turn off" the transaction log--it's your safety net. Yes, there are other operations that can be executed without the log getting in the way (like BulkCopy).

Consider that the Delete command must also delete the Index(es) for each row as well as reallocate space and execute other operations that take CPU time, RAM and disk IO. While the Truncate is fast, it also means that the server can clean up the freed space when it has idle time and it needs the space. For long operations you can set the CommandTimeout to a higher number, but whenever I find a neeed to do this I look for a more efficient way to handle the task...

hth

|||

William,

Thanks! I'm in good shape now.

jack

"Additional information: System error" message

Hello,
I needed to look at some .BCP files, so I thought I'd create a small tool for that.
I decided to create a temporary DB, attach empty mdf, ldf files with predefined structure and then insert the BCP files, so I can view the data as DB tables .

Here is the procedure I follow:
1) I drop the existing (if exists) DB ("Drop DBName") - to be prepared for the next set of .BCP files.
2) Then I create a new DB using sp_attach_db,
3) Then I populate the db via "Bulk Insert".

1st time this works just fine, but when I try to execute step 1 (drop DB) after step 3 I get a message that there is still open connection and the DB can't be dropped.

Here I can do two different things:
1) Find the SPID of the open connection to my DB and KILL the process or
2) Instead of killing the process, set the DB to "SINGLE_USER WITH ROLLBACK IMMEDIATE" and then drop the DB.

In both cases I was able to Drop the DB and attach the new one, however the Bulk Insert (via ExecuteNonQuery() ) for some reason closes the current connection and brakes with the "System Error" message.

I simulated this procedure on Query Analyser and on SQL Server Studio Express, but everything worked just fine there.

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

To make it short, here is the SQL Query procedure (which worked, though):

USE Master
if exists
(select name
from master.dbo.sysdatabases
where has_dbaccess(name)=1 and name='MyDB')
BEGIN
Drop database MyDB
END
exec sp_who2

-- Before the next command I have to manually restore the .ndf, .ldf files, deleted by the "Drop DB" command

sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'D:\CurrSchema\DB_Patient.ndf',
@.filename2 = N'D:\CurrSchema\DB_Definitions.mdf',
@.filename3 = N'D:\CurrSchema\DB_Log.ldf',
@.filename4 = N'D:\CurrSchema\DB_Data.ndf'
exec sp_who2

-- WAITFOR DELAY '00:00:04'
-- need to wait manually here. Waitfor won't work with "USE DB"

USE MyDB
BULK INSERT CAL
FROM '\\srv\D$\CurrFiles\CAL.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)

BULK INSERT CAP
FROM '\\srv\CurrFiles\CAP.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
exec sp_who2

Has anybody seen something like this?

Thank you.

Haven't seen it personally. However, I suggest you insert a 'go' between each command to seperate them into batches. Also, be sure to change the database to 'master' for your connection before sending/executing 'drop db'.|||

Yes, Thank you - you were right. I did not need the 'GO', but the change to 'master' was the key. I should have been more careful when using somebody else's code - no matter how encapsulated, it's never context-free - in my case I had to add context switching with 'USE master'.

George.

|||

Hi... you mentioned you had the below source sample...

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

Can you please post or sent to billbbellevue@.hotmail.com

Kind regards,

Bill Bezick

"Additional information: System error" message

Hello,
I needed to look at some .BCP files, so I thought I'd create a small tool for that.
I decided to create a temporary DB, attach empty mdf, ldf files with predefined structure and then insert the BCP files, so I can view the data as DB tables .

Here is the procedure I follow:
1) I drop the existing (if exists) DB ("Drop DBName") - to be prepared for the next set of .BCP files.
2) Then I create a new DB using sp_attach_db,
3) Then I populate the db via "Bulk Insert".

1st time this works just fine, but when I try to execute step 1 (drop DB) after step 3 I get a message that there is still open connection and the DB can't be dropped.

Here I can do two different things:
1) Find the SPID of the open connection to my DB and KILL the process or
2) Instead of killing the process, set the DB to "SINGLE_USER WITH ROLLBACK IMMEDIATE" and then drop the DB.

In both cases I was able to Drop the DB and attach the new one, however the Bulk Insert (via ExecuteNonQuery() ) for some reason closes the current connection and brakes with the "System Error" message.

I simulated this procedure on Query Analyser and on SQL Server Studio Express, but everything worked just fine there.

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

To make it short, here is the SQL Query procedure (which worked, though):

USE Master
if exists
(select name
from master.dbo.sysdatabases
where has_dbaccess(name)=1 and name='MyDB')
BEGIN
Drop database MyDB
END
exec sp_who2

-- Before the next command I have to manually restore the .ndf, .ldf files, deleted by the "Drop DB" command

sp_attach_db @.dbname = N'MyDB',
@.filename1 = N'D:\CurrSchema\DB_Patient.ndf',
@.filename2 = N'D:\CurrSchema\DB_Definitions.mdf',
@.filename3 = N'D:\CurrSchema\DB_Log.ldf',
@.filename4 = N'D:\CurrSchema\DB_Data.ndf'
exec sp_who2

-- WAITFOR DELAY '00:00:04'
-- need to wait manually here. Waitfor won't work with "USE DB"

USE MyDB
BULK INSERT CAL
FROM '\\srv\D$\CurrFiles\CAL.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)

BULK INSERT CAP
FROM '\\srv\CurrFiles\CAP.bcp'
WITH (DATAFILETYPE='native',KEEPIDENTITY,KEEPNULLS)
exec sp_who2

Has anybody seen something like this?

Thank you.

Haven't seen it personally. However, I suggest you insert a 'go' between each command to seperate them into batches. Also, be sure to change the database to 'master' for your connection before sending/executing 'drop db'.|||

Yes, Thank you - you were right. I did not need the 'GO', but the change to 'master' was the key. I should have been more careful when using somebody else's code - no matter how encapsulated, it's never context-free - in my case I had to add context switching with 'USE master'.

George.

|||

Hi... you mentioned you had the below source sample...

The VB code is a little complicated with using app.config to open so many network drives and folders, but if anybody is interested I'll post it.

Can you please post or sent to billbbellevue@.hotmail.com

Kind regards,

Bill Bezick