Friday, April 25, 2014

When You Really, Really Want to Drop a SQL Server Database

I just discovered some things about dropping databases in SQL Server. Firstly, if you really want to first get rid of all connected users, this works:

 ALTER DATABASE [TruckData] SET OFFLINE WITH ROLLBACK IMMEDIATE

However, when I then drop the offline database, the MDF and LDF files still hang around. I don't think this is officially documented behavior, at least I don't see it on any Microsoft site. Here's the final version I am now using to drop tables with extreme prejudice:

 ALTER DATABASE [TruckData] SET OFFLINE WITH ROLLBACK IMMEDIATE
 ALTER DATABASE [TruckData] SET ONLINE
 DROP DATABASE [TruckData]

No comments: