What is the best way to delete large database programmatically

Question

I need to drop a perticular database in SQL Server 2005 using T-SQL. I know droping a databse can not be done in a database trnsaction. So what if while deleting a real huge database some unexpected exception occurs E.g. Time out exception. Will it leave my database in unstable state ? If yes, how do i avoid such situation ?

Answer 1

The best way to delete all records in a database while leaving the database structures in place is to:

1. Take the database offline;
2. Disable all foreign key constraints (if referential integrity is enforced);
3. Truncate each table in turn;
4. Enable all foreign key constraints (if referential integrity is desired);
5. Refresh any materialized views;
6. Re-analyze the database (since previous stats are no longer relevant);
7. Bring the database back online.

This can all be done programatically but will vary depending on your database vendor and version. For SQL Server 2005 (your requested version) try How to: Truncate Multiple Tables In SQL Server and the magic of sp_MSforeachtable.

This shouldn't leave your database in an unusable state if it fails because it's done in such a way that if it stops unexpectedly you can just rerun it and it'll simply continue.

If you try and use the database if the script finishes prematurely then it's entirely possible your database will be in an inconsistent state (which is half the reason you take it offline to do it; the other reason being to avoid locks).

Update: if you simply want to delete the database entirely see How do I drop a SQL Server database?. It comes down to a DROP DATABASE command but sometimes you need to effectively take the database offline too.

Answer 2

detach the database, then delete the files.

Answer 3

depending on how you're running the t-sql, you can change the timeout before running the commands. SSMS defaults to no timeout, but you can change SqlCommand's CommandTimeout to either 0 (no timeout) or something large. In my personal experience, failed runs of "drop database" haven't prevented later runs of it from succeeding, so while you may get the database into a bad state, I don't believe it'd be one where it's still there but not drop-able (but that's just my experience, admittedly)

Depending on whether you want to delete the data files, I'd recommend setting the database into either single_user mode (if you want to delete the files) or offline (don't delete the files) with an immediate rollback to help prevent other connections from blocking the drop or causing it to fail.

So:

• alter database [foo] set single_user with rollback immediate
• OR
• alter database [foo] set offline with rollback immediate
• drop database [foo]

Make sure to check some of the various behaviors of drop database (like the above) @ http://msdn.microsoft.com/en-us/library/ms178613.aspx