Drop Table Generates TLog???

[lclechner]

A customer has asked me if Drop Table generates Transational log. I think that Drop Tables such as Truncate table must generate only one entry in Tlog, but I'm not sure about it. Anyone knows the anwser?

 

[evatype2]

Never used to. Databases really make a distinction between transactions and DML. A transaction has a begin (and hopefully, if coded correctly) an end. A transaction does work on the DATA in the database. So, the transaction log keeps track of these changes so you can recover all the work that has been done on the data (often by end users). On the other hand, there is no real need to track what the DBA did to the database structure. If the DBA screws up, its his/her problem to recover. That is why the manuals say to create backups of the database before and after you issue DML commands. The transaction log can not recover a whole table, just transactions against that table.

 

[jester777]

TRUNCATE TABLE still places entries in the transaction log. Each data page that becomes empty due to the TRUNCATE is deallocated. This deallocation is recorded in the transaction log. Therefore if many pages are deallocated there will be many entries placed in the transaction log.
This is still much less than if you DELETED each row in the table. In this case each row deletion is placed in the tran log.

The DROP TABLE will also place entries in the transaction log. Whenever to DROP or CREATE tables, Indexes etc MS SQL's own system tables are updated. These updates are logged to maintain the consistency of the database.

Hope this helps.