Can''t drop table
-
Hi,
I have a job with a step: "drop table".
This step freezes.
Manual deletion of the table- from EM or QA freezes as well.
My questions are:
1. What is preventing me of dropping a table?
2. How can I drop that table?
Thx,
-
Most likely is that you have someone using that table.
Execute this :
sp_who2 and check the column blkby.
Then for any column with an id run this :
dbcc inputbuffer (spid)
This will show you the statement that the connection is executing and will tell you what's blocking you.
Then to terminate the blocking process : Kill spid.
If that doesn't work, then it's most likely that you don't have permission to execute that.
-
In fact (meanwhile) I restored the DB from full backup and got rid of the problem.
Running fine again.
Thx,
-
As a matter of practice with large tables I always perform TRUNCATE TABLE before i drop the table
MVDBA
-
What's the advantage? Does the server perform a delete from table before the drop?
-
Its likely a large table, then do as Quantix suggested - truncate the table and then drop the table.
-
I don't think so! ... Eagerly waiting to see what is the advantage ?
* Noel -
Waiting myself to see one
... -
Unless i'm mistaken - and there's every chance i could be.
DROP table is logged. if you have 10 million rows of data your drop table will generate a huge log file and take forever.
TRUNCATE does not log the deletes. so your drop statement is pretty fast.
although - i'm not 100% convinced i'm right here, I just do it as a matter of practice rather than risk my system grinding to a halt.
MVDBA
-
anyone got a huge table they can copy and drop to see the effects on the log?
-
185 Gb, but i can't be bothered to jam up my test server and also i don't think i have enough t-log space
MVDBA
-
Again Let me say that DROP Table and TRUNCATE Table are both minimally logged operations and neither will fill up the log!!!
Use TRUNCATE when you need to clear data use DROP when you want to remove the definition and its data.
I have been able to test this with a large table!
cheers!
* Noel -
Nice to have a confirmation I'm not crazy
.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply