Drop a 500 GB table, any consequences? I need to drop a table that is 500 GB in size. It contains historical data that's not needed or used. There are no FK constraints. Any consequences of just dropping the table? I'm also using DB Mirroring. Will there be blocking or some other things that I should be concerned?

Microsoft official statement:


  Large tables and indexes that use more than 128 extents are dropped in two separate phases: logical and physical. In the logical phase, the existing allocation units used by the table are marked for deallocation and locked until the transaction commits. In the physical phase, the IAM pages marked for deallocation are physically dropped in batches.
From MS:

When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table (including a schema (SCH-M) lock) and page but not each row.

https://docs.microsoft.com/en-us/sql/t-sql/statements/truncate-table-transact-sql?redirectedfrom=MSDN&amp;view=sql-server-ver15

Where possible I'd run it in a dev environment first and run sp_whoisactive @get_locks=1. This will give you some reassurance over what's going to happen in terms of locking/blocking. Appreciate this may not be possible with a massive DB.

Regarding the mirror, TRUNCATE is still a logged operation so the table will also be truncated on the mirror. This may help https://www.sqlservercentral.com/forums/topic/truncate-table-on-mirrored-db