Unable to drop and create database in SQL Server

Checking before going gung-ho

Before you unleash the DROP DATABASE command and possibly break something, perform some preliminary health-checks:

• Close all query windows on SSMS which reference YourDB, or they'll end up owning the place.

• Hunt down active connections like this:

  SELECT * FROM sys.sysprocesses WHERE dbid = DB_ID('YourDB');  -- Peek-a-boo! I see you!
  

• Disconnect invasive processes:

  DECLARE @kill varchar(8000) = '';  
  SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(5), spid) + ';'  
  FROM sys.sysprocesses  
  WHERE dbid = DB_ID('YourDB');
  -- Consider this a mass eviction!
  EXEC(@kill);
  

Your friendly neighbourhood etiquette

Condition yourself to always exercise the good practices:

• Always shift gears to the master database before performing operations like DROP or CREATE.
• In SSMS, while deleting from Object Explorer, be a law-abiding citizen by selecting "Delete" and checking the option "Close existing connections."

Extra yards and the ifs

In more complicated scenarios, we can't just use brute force. Here are alternative methods and additional considerations for handling the not-so-ordinary scenarios:

Tranquilising the replication monster

If your database is part of a replication or has extensive transaction logs, it's safer to perform a backup:

BACKUP LOG YourDB TO DISK='YourDB_LogBackup.trn';  -- Remember, safety first!
GO


Setting database to hibernate

Alternatively, you can send the database to sleep, offering a short break before the ultimate drop:

ALTER DATABASE YourDB SET OFFLINE WITH ROLLBACK IMMEDIATE;  -- Shhh, baby database is sleeping...
DROP DATABASE YourDB;  -- Woke up in purgatory.


Surviving the phantom objects

Annoying object-related errors can be intercepted using proactive checks:

-- Check if a particular menace exists before dropping
IF OBJECT_ID('YourDB.dbo.YourTable', 'U') IS NOT NULL
DROP TABLE YourDB.dbo.YourTable;  -- Sayonara, YourTable.


Doctor Google to the rescue

When specific and particularly exasperating issues crop up, don't hesitate to run for help:

• Visit Microsoft forums for version-specific first-aid.
• Mingle with the community on platforms such as StackOverflow.

These methods ensure smooth operation in all possible scenarios, overcoming unexpected hurdles.