When you drop a database in SQL Server, the files are normally removed from the file system right away. POOF! Hope you had a backup if you ran DROP DATABASE against the wrong instance.
However, things are a little different if you take the database offline before you drop it. Consider the following code:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
use master; GO CREATE DATABASE DropMeOffline ON PRIMARY ( NAME = N'DropMeOffline', FILENAME = N'S:\MSSQL\Data\DropMeOffline.mdf' , SIZE = 256MB , FILEGROWTH = 256MB ) LOG ON ( NAME = N'DropMeOffline_log', FILENAME = N'S:\MSSQL\Data\DropMeOffline_log.ldf', SIZE = 128MB, FILEGROWTH = 128MB ) GO ALTER DATABASE DropMeOffline SET OFFLINE WITH ROLLBACK IMMEDIATE; GO DROP DATABASE DropMeOffline; GO |
Here’s what S:\MSSQL\Data looks like after I run the whole script, including DROP DATABASE…
This is by design and documented in Books Online — any database with one or more offline files won’t have the files removed from the filesystem when a database is dropped.
Why Take Databases Offline?
Taking databases offline is a very useful process tool. When you’re retiring or migrating a database off of a server, it’s often convenient to make the database unusable for a period before really deleting it. Because sometimes it turns out that a database is in use when we thought it wasn’t.
If you do choose to take databases offline before dropping them:
- Make sure you have enough copies of the last full backup for your policies
- Test that the backup restores properly elsewhere and isn’t corrupt
- When it comes time drop the database, remember that you’ll need to clean up the files manually if you drop it while it’s offline
But whatever you do, don’t skip that backup. The fact that the files aren’t automatically cleaned up doesn’t replace a good backup!
Brent Says: Wow, I didn’t know that.
23 Comments. Leave new
I noticed that when an application was turned over to me with about 400GB of orphaned database and t-log files and another 300GB of files belonging to deleted test DBs on production disk. So I always bring it back online and delete it if possible. The previous person simply deleted the offline DBs and left the disk cluttered with orphans. I see it’s not just a 2008 and lower phenomenon, it’s by design. Thanks for finding that.
Wow, Something I didn’t know, but VERY good to know! Thank you Kendra!
Q. Does dropping an offline DB set NTFS permissions/ownership on data/log files to current integrated logon as detach db?
A. Test it :-))
This is actually our current policy at my workplace now, but i’m curious as to “technically” what the difference is between the Offline/Drop method versus the Detach method. I understand the value in keeping a database offline for an amount of time especially for retirement, just curious. Great article.
I think it’s a change in preference/process. With “offline” it’s easy for anyone to see the database there and alter it to an online state with a single tsql command. So that appeals to me if there’s any chance I might need to bring it back online.
Got it. Thanks for the reply!
Is there a way to query the SQL Server instance meta-data dictionary to determine if any database has been detached from the instance? I’ve seen some extensive code that calls out to the OS (XP_CMDSHELL ) but it would be nice if the instance somehow tracked this and you could use a simple T SQL stmt.
Great question. I’ve never researched this. I believe it’s captured in the default trace, but I’m not sure if it’s something you can detect with DDL Triggers/Event Notifications (I don’t believe detach is technically configured as DDL).
My immediate question though, is why you’re worried? It sounds like there are people who have very high rights (sysadmin) who shouldn’t have that, and that seems like the root cause of the problem.
That’s right. Detach operations runs DBCC DETACHDB so this event is logged in errorlog (Category: Audit DBCC Event).
I just did a test on my lab box, created test DB, and then detached it. Nothing written on the detach to SQL Log, or to the event application or system logs. This is running Microsoft SQL Server 2012 – 11.0.5058.0 (X64) rather vanilla install.
Did you check the default trace? That’s where it should be. That trace does roll over periodically as activity comes in, but if the system is idle and you did this recently, it might still be in there.
OK. It shows up in the default trace. That is the ONLY place I found it. Not in the SQL Error log, not in the Windows event logs. The DB Creation showed up in the sql error log. But not the detach. Not that I really care, or need to worry about it, was just an exercise in seeing where it was logged.
I’ve observed the same behavior in ReadOnly databases. If you delete them, the files remain in the file system and you have to clean them up manually.
At least not in 2008R2, just cheched it – the datafile is also deleted after DROP DATABASE
Was the database actually offline when it was dropped? If so this sounds like a bug — it’s documented that it should not delete the files if anything is offline in that version: https://technet.microsoft.com/en-us/library/ms178613(v=sql.105).aspx
The database was readonly, not offline
Oh! I see! I missed the context of your comment entirely the first time around. Thanks for clarifying.
I hadn’t seen this before but it got me to wondering…should be possible to script for old artifact MDFs left laying around long after an online DB was dropped. This only works for 2012 and above (could be made to work with older, but the code would probably get noisier) but if you can live with xp_cmdshell it seems to work. Powershell might allow for some more elegant solutions, but then, I’m rarely accused of excessive elegance.
DECLARE @cmdOutput TABLE (txt VARCHAR(1000) NULL);
DECLARE @cmd VARCHAR(1000);
SELECT @cmd= ‘dir ‘ +
CAST(SERVERPROPERTY(‘InstanceDefaultDataPath’) AS VARCHAR(255))
+ ‘ /a:-d /b’;
INSERT INTO @cmdOutput EXECUTE master..xp_cmdshell @cmd;
SELECT RTRIM(REPLACE(c.txt,CHAR(13),”)) AS DetachedDataFiles
FROM @cmdOutput c
WHERE LOWER(c.txt) LIKE ‘%.[m,n]df%’
AND NOT EXISTS
( SELECT 1 FROM master.sys.master_files
WHERE physical_name LIKE ‘%’ + RTRIM(REPLACE(c.txt,CHAR(13),”)) )
Because I can’t let well enough alone…a revised, better version that works with 2005+ and runs nicely across CMS. Uses master_files to determine a list of places to check for orphaned MDF or NDF files. Still needs xp_cmdshell, of course.
DECLARE @files TABLE (pth VARCHAR(500), fname VARCHAR(500));
DECLARE @cmdOutput TABLE (txt VARCHAR(1000) NULL);
DECLARE @cmd VARCHAR(1000), @MDFpath VARCHAR(500);
SET NOCOUNT ON;
INSERT INTO @files
SELECT
LEFT(physical_name,LEN(physical_name)-PATINDEX(‘%\%’,REVERSE(physical_name))+1),
RIGHT(physical_name,PATINDEX(‘%\%’,REVERSE(physical_name))-1)
FROM sys.master_files WHERE type = 0;
DECLARE pathcurs CURSOR FOR SELECT DISTINCT pth from @files
OPEN pathcurs
FETCH NEXT FROM pathcurs INTO @MDFpath
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cmd = ‘dir ‘ + @MDFpath + ‘ /a:-d /b’;
INSERT INTO @cmdOutput EXECUTE master..xp_cmdshell @cmd;
FETCH NEXT FROM pathcurs INTO @MDFpath
END
CLOSE pathcurs
DEALLOCATE pathcurs
SELECT c.txt AS DetachedDataFiles FROM @cmdOutput c
LEFT JOIN @files f ON UPPER(c.txt) = UPPER(f.fname)
WHERE UPPER(c.txt) LIKE ‘%.[M,N]DF%’ AND f.fname IS NULL;
Amazingly this is still a misunderstood behaviour even today. Do I get bonus “Kendra points” for my 2009 post (and rather shameless plug) 🙂 http://tenbulls.co.uk/2009/12/13/dropping-offline-databases-dont-forget-your-files/
I found this out today – every day is a school day 🙂
This helped me since I was about to change a set single user mode to set offline before dropping a database, since I find when doing a restore that set offline with roll back immediate works better than single user mode.
However, I want to get rid of the files, so I will keep the previous code that had a 5 second wait before setting to single user mode ( the 5 sec wait was written by a previous developer who found that single user mode rollback immediate does not always work if the database is active when you call)
I know this is an old post, but as always still learn something new..