More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
You need to remove a large table from your database ...
You need to remove a large table from your database. This table has over 200 extents of data and you issue a "DROP TABLE MYLARGETABLE". How is this handled in SQL Server 2005?
Answer: A two part scheme of destruction with a virtual sniper marking each allocation unit for deletion inside the transaction. Then in smaller separate transactions of destruction the allocation units are dropped.
Explanation: While a virtual phase locked laser would be nice, this actually occurs in two phases. The logical phase occurs inside the transaction with the drop statement and all of the allocation units are marked for deallocation and locked.
After the transaction commits, the allocation units marked are physically dropped in short batches inside background transactions.
(Continued on next question...)