Dropping a table

Question

Post reply

Dropping a table

sunny.tjk

SSCertifiable

Points: 5041
More actions
May 24, 2010 at 4:30 pm

#219347

Hi everyone,
I'm not able to drop a table which has no constraints on it. It's giving me an error message which says "Lock request time out period exceeded."
It has about 24 million records.
How do I drop this table?
Thanks,
Sunny.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 1

Aashini Shah

Ten Centuries

Points: 1129

May 24, 2010 at 4:34 pm

#1171807

Truncate the table first and then drop it.

Neepa

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 2

I may be wrong but I was under the assumption that truncate table doesn't consume time at all irrespective of the number of the records.

It has close to 25 million records and truncate table operation is still running more than 5 min.

Any clues?

Leo.Miller SSChampion Points: 13051 More actions · Answer 3

In a new query window run SP_WHO2 to see if the truncate is blocked by another spid.

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 4

I see the status is in SUSPENDED mode and the command shows as "KILLED/ROLLBACK". Not sure if it's trying to rollback but it's taking helluva time.

Leo.Miller SSChampion Points: 13051 More actions · Answer 5

I wonder if your original DROP Table command is still rolling back. Could be.

How big is the log file?

Leo

There are 10 types of people in the world.

Those who understand binary and and those that don't

Leo
Nothing in life is ever so complicated that with a little work it can't be made more complicated.

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 6

Get the SPID of the killed/rollback command.

check DBCC INPUTBUFFER (SPID ) to see which command is rolling back.

If the DROP is rolling back, just stop the truncate statement and close that window. Make sure that SPID is gone.

Let the DROP rollback completely. Then go for Truncate and DROP.

Neepa

sunny.tjk SSCertifiable Points: 5041 More actions · Answer 7

This is a test server, so I restarted sql services and the db went to "in recovery" mode. So, I just waited till the database went into normal mode by itself.

Aashini Shah Ten Centuries Points: 1129 More actions · Answer 8

When the SPID is rolling back and really something is happening ( I mean the SPID is just not hung ), even if you restart , sql server has to recover cleanly. It would still try to complete the rollback noncommited transactions in a nutshell. That is why you saw the database in recovery mode for longer than usual.