October 9, 2012 at 1:12 pm
SQL 2008R2
Help please.
I am attempting to drop a database and keep getting the error message that it is in use. Normally I would solve this by running the following query:
USE master;
GO
ALTER DATABASE my_database
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;
GO
Drop Database my_database
GO
However, it is not working for me this time. It runs until I cancel the query, and then returns the following message:
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
Query was canceled by user.
Activity Monitor and sp_who2 both show no connections, but when I run just the Drop Database command from master, I get the error of database is in use.
I have also tried closing connections using the GUI 'Detach...' and that does not work either. This one has me stumped.
October 9, 2012 at 1:59 pm
What recovery mode is the database in, out of curiousity?
Also, what exact version and SP pack are you currently running? Any hotfixes/odd startup flags on the server?
My guess would be a hanging SPID offhand, and might be worth a browse through master.sys.sysprocesses and see if there's any antique connections that just can't seem to be rolled out. A reboot or shutdown/startup of the service would clear most of those problems if they exist, I assume you've tried this.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
October 9, 2012 at 2:23 pm
Thanks Blacksmith,
Version is:
Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
I have attempted this while in both Full and Simple recovery modes with same results.
I am trying everything short of restarting the service, as this instance is host to a COTS with 47 other databases. I guess I will now send out that notification so they can hate me even more.
Thanks for your response.
October 9, 2012 at 2:40 pm
mmontgo2 (10/9/2012)
Version is:Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Quick review of connect and known issues doesn't indicate anything for that... but that doesn't mean it's not there, just that I can't find it.
I have attempted this while in both Full and Simple recovery modes with same results.
This leads me closer to a locked spid scenario and it getting stuck on the single user mode rollbacks.
I am trying everything short of restarting the service, as this instance is host to a COTS with 47 other databases. I guess I will now send out that notification so they can hate me even more.
Sorry I don't have a better answer here for you, but it's really the only way to clear spids that get stuck sometimes. If you want to hold off some others may come by with some less drastic alternatives. I sledgehammer troubleshoot on occassion for connection problems just because they're rediculously long for me to track down, and usually end up needing the sledgehammer anyway.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply