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
