cannot drop database snapshot

Question

Post reply

cannot drop database snapshot

Deb Anderson

SSCrazy

Points: 2116
More actions
November 19, 2009 at 10:55 am

#214236

Scenario:
1. I created a snapshot of my database
CREATE DATABASE [Db_snapshot] ON
( NAME = N'Db_Data', FILENAME = N'd:\snapshots\Db_Data.mdf')
as snapshot of Db
2. ran my checkdb against the snapshot
3. dropped the snapshot
DROP DATABASE [Db_snapshot]
4. Now I need to create the next snapshot of this database to run the next checkdb. Here is what I get:
Msg 1801, Level 16, State 3, Line 1
Database 'Db_snapshot' already exists. Choose a different database name.
5. When I run the following queries, this db does not show up:
select * from sys.databases
select * from sysdatabases
6. When I check the snapshot folder where the sparse file is supposed to be - it IS there, and I cannot manually delete it, because the file is in use.
7. I have bounced the instance, the file still will not release. I have checked any hung spids that might be accessing it, there are none.
My first thought is to delete the file while I have the instance completely down. I really need some guidance here, please!!!
Thanks!
Deb
😎

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

Can you create a new snapshot, same name, different file?

This does sound strange. When you start the instance, the file goes in use, correct?

Deb Anderson SSCrazy Points: 2116 More actions · Answer 2

thanks for the reply.

yes, i can. i can create a new snap of the same db with a different name then delete it without a single problem.

i have managed to eliminate any other source of the file being held open by something else other than SQL Server.

😎

Deb Anderson SSCrazy Points: 2116 More actions · Answer 3

and yes, when the instance starts, the file goes into use by sqlserver.exe.

i reiterate though, that there is not 1 spid using it. so i am left to believe that the system has some unfinished business with it.

i don't normally have issues with performance otherwise on this box. 7GB memory. Quad core. normally runs like a dream. but i am new to how sparse files operate. i DID run a massive delete 3 mil records from a table yesterday while the snapshot was in place. is it possible for it to still be writing the delta's to the sparse file?

😎

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 4

I guess it's possible there is still being copy work done on the sparse file, but I wouldn't expect that. My thought was that a update/delete in the original file would need to be committed in the sparse file as well before it would be completed in the database. Otherwise you couldn't be sure the snapshot would be accurate.

Not sure what's happening here. If it's not a huge problem, I'd let this run along for a day and try deleting it tomorrow.

Also, if you run checkdb on the snapshot, you're hitting the main file for I/O, so be aware of that.

Deb Anderson SSCrazy Points: 2116 More actions · Answer 5

not a HUGE problem, but i like to run integrity checks everyday if i can. i have 10 servers with way more activity than this server/db that i am using the same methodology to run my integrity checks.

this is a problem for me really only in the sense that if it is happening on this server, it will happen on others. snapshots=increased i/o. i have already proven to my satisfactions that their presense is causing issues with a few of my applications. so, i have to drop them as soon as i can or users and developers start yelling at me when they experience the timeouts.

incidentally, i am using this particular methodology because we are using NEVERFAIL for our DR strategy, and it barfs on the 'system' create sparse files, so i am creating them manually in a directory that we can exclude.

i will let it run it's course for another day and see what happens.

thanks again.

😎

Deb Anderson SSCrazy Points: 2116 More actions · Answer 6

*sigh*

still there. what is the worst that could happen if i drop (rename) that sparse file while sql is down?

😎

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 7

That's the thing. I don't know. If you had a snapshot database, I'd say it would go suspect, but since you don't, I have no idea.

At this point, I would probably try what you've suggested, making sure my backups from last night were good. If I had a case with MS to burn, I might call them and check this out. I assume that SQL Server has the file. You could grab Process Monitor and be sure that sqlsrvr.exe has the handle (http://technet.microsoft.com/en-us/sysinternals/bb896645.aspx) for the file.

I would also go back and be sure that this instance is using the file, make sure you don't have anything confused and then I'd drop it.

Deb Anderson SSCrazy Points: 2116 More actions · Answer 8

i think i may just burn that ticket. since i will be using manual snaps on all my production servers going forward, i'd like to know what the MS peeps have to say about my little anomaly. i just have this feeling i will be back here again.

i will update this thread even if MS has nothing earth shattering to say about the situation.

*bracing myself for level 1 support*

🙂

thanks for your response.

😎

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 9

Good luck, thanks, and I hope this doesn't drag into your weekend.