SQL Server Performance Forum – Threads Archive

does a drop table get logged?

does a "drop table" get logged?

i think yes firing any query even if it is drop table get logged.

Yes it is a logged operation and is recorded on the Transaction log. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

One thing to the middle point, not all query commands are logged. For example truncate table is not logged. It results in a deallocation of the pages and the deallocation is stored, not the removal of the records themselves. There are other commands that are not logged too.
Chris

Might be nit-picking, but TRUNCATE is minimally logged. As Chris said the deallocation of the data pages is logged. If can even be rolled-back. I think a DROP TABLE is also logged as the fact. Not comparable to DELETE. —
Frank Kalis
SQL Server MVP
http://www.insidesql.de

quote:Originally posted by ChrisFretwell One thing to the middle point, not all query commands are logged. For example truncate table is not logged. It results in a deallocation of the pages and the deallocation is stored, not the removal of the records themselves. There are other commands that are not logged too.
Chris

Hi chris this microsoft article says that truncate table is partially logged.
http://support.microsoft.com/default.aspx?scid=kb;en-us;257334
it also shows the STATUS:
Microsoft has confirmed that this is a problem in the Microsoft products.

Thanks Everyone. One of our jobs drops a bunch of tables each with over 300K records. I wanted to check if it is better to truncate and then drop as I was not sure if drop gets completely logged (each record) just like a delete if I just drop the filled table.

]]>