July 10, 2005 at 11:24 am
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,
July 10, 2005 at 1:04 pm
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.
July 10, 2005 at 1:44 pm
In fact (meanwhile) I restored the DB from full backup and got rid of the problem.
Running fine again.
Thx,
July 11, 2005 at 10:41 am
As a matter of practice with large tables I always perform TRUNCATE TABLE before i drop the table
MVDBA
July 11, 2005 at 11:26 am
What's the advantage? Does the server perform a delete from table before the drop?
July 11, 2005 at 11:59 am
Its likely a large table, then do as Quantix suggested - truncate the table and then drop the table.
July 11, 2005 at 12:14 pm
I don't think so! ... Eagerly waiting to see what is the advantage ?
* Noel
July 11, 2005 at 12:38 pm
Waiting myself to see one ...
July 12, 2005 at 2:25 am
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
July 12, 2005 at 6:24 am
anyone got a huge table they can copy and drop to see the effects on the log?
July 12, 2005 at 6:37 am
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
July 12, 2005 at 12:24 pm
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
July 12, 2005 at 12:28 pm
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