Sql-server – Sql Server 2016 Cannot drop database (it is really, really, really stuck)

sql server

I have a database that was involved in a mirroring session and something crashed hard and the database is stuck 'in recovery' and will not allow me to drop it because it thinks the database is in use although I can't see what.

I run this and the affected database is not listed:

SELECT DB_NAME(dbid) as 'DBName'
     , loginame as 'Login'
     , COUNT(dbid) as 'Connections' 
FROM sys.sysprocesses 
WHERE dbid > 0 
GROUP BY dbid, loginame

I also run EXEC sp_who2 and the database is not listed either

I run

ALTER database careplans set offline with ROLLBACK IMMEDIATE; DROP database careplans;

and I get an error "ALTER DATABASE failed because a lock could not be placed on database 'careplans'. Try again later."

This brings back no results:

select * from sys.sysprocesses where dbid = DB_ID( 'careplans')

This gives me the 'lock could not be placed' error:

ALTER DATABASE careplans
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;

This gives me an "Exclusive access could not be obtained because the database is in use." error:

RESTORE DATABASE CarePlans WITH RECOVERY

Any of the following give me the 'lock could not be placed' error:

ALTER DATABASE careplans SET  SINGLE_USER WITH NO_WAIT 
ALTER DATABASE careplans SET EMERGENCY; 
DBCC CHECKDB (careplans, REPAIR_ALLOW_DATA_LOSS) 
ALTER DATABASE careplans SET online; 
ALTER DATABASE careplans SET  Multi_USER WITH NO_WAIT

I have no idea what else I can try…?

Best Answer

This issue is pretty tricky to reproduce so I can suggest to try two more solutions, but both require SQL Server to be restarted:

Start SQL Server in single user mode (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/start-sql-server-in-single-user-mode) (just add SQL Server service startup parameter -m and restart the service). Then using SQL CMD connect to the SQL instance using local administrator account and try to drop database using DROP DATABASE statement. Perhaps this time you won't get an error related to exclusive access acquisition.
Another way (pretty primitive I know! But it might be effective either!) is to stop SQL service and delete (or change location) of database files. Then when you start SQL Server back this database should come up in 'recovery pending' status and you should be able to remove it.

Related Solutions

Sql-server – What are the names of SQL Server database statuses 24, 1024 and 65544

The others are combinations of the documented flags, e.g. 24 is 16 and 8.

This is a method for simulating optional parameters and is used in e.g. C iirc, the numbers 1, 2, 4, 8 and so on are structured like that because they correspond to binary values that combined in any unique way create a unique number. The function that accepts them then use bitmasking to extract each individual number. E.g. if you send the value 6 to a function, then we know that this is a combination of 4 and 2, to find this out using a bit mask we would do:

    0110 // 6
AND 0010 // 2
 == 0010

    0110 // 6
AND 0100 // 4
 == 0100

Both statements would be evaluated to true, thereby we have found out the individual values.

Note: In the real world, we would iterate through all values from 1, 2, 4, 8, 16 and so on to the maximum value of an integer (or whatever datatype the parameter was set to).

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

After a considerable amount of testing, I finally discovered the reason behind this error. The client connection explicitly set ANSI_WARNINGS and CONCAT_NULL_YIELDS_NULL OFF. XML data operations, such as @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), require both to be ON. I had attempted to override these within the trigger, but I may have placed them wrong. The final code below works, even with the explicit SET options in the connections from Great Plains:

CREATE TRIGGER [ddl_log] 
ON ALL SERVER 
FOR DDL_DATABASE_LEVEL_EVENTS, DDL_SERVER_LEVEL_EVENTS 
AS
BEGIN
SET NOCOUNT ON;
SET ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL ON;

DECLARE @data XML
SET @data = EVENTDATA() 

EXECUTE AS LOGIN='<dummy login>'
INSERT admin.dbo.ddl_audit (PostTime, DB_User, [Event], [TSQL], Host, DatabaseName)
VALUES (
   GETDATE(),
   CONVERT(NVARCHAR(100), ORIGINAL_LOGIN()),
   @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'), 
   @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)'), 
   CONVERT(NVARCHAR(100), HOST_NAME()),
   @data.value('(/EVENT_INSTANCE/DatabaseName)[1]','nvarchar(100)')
   ) ;
REVERT 
END

As an alternative, I also could have simply inserted EVENTDATA() as an XML LOB into a table, rather than parsing it out into columns. Because I would not be manipulating the XML, the SET options do not matter. Then I would just build an XML index for querying performance, and construct a view to use for audit log reporting that parses the XML in the view definition, in the same manner as I am doing above in my INSERT statement.

Thanks to Max for pointing me in a different research direction, and @AaronBertrand on #sqlhelp who helped me with correct SET options within the body of the trigger.

Best Answer

Related Solutions

Sql-server – What are the names of SQL Server database statuses 24, 1024 and 65544

Sql-server – sysadmin cannot drop login when ddl audit trigger enabled

Related Question