When you encounter the "Database 'XXX' is in transition" error in SQL Server, it typically means the database is stuck between states after an interrupted operation. This frequently occurs when:

• A long-running query was forcefully terminated
• A database restore operation was aborted
• Failover operations were interrupted
• Service restarts occurred during maintenance

Here are several approaches to resolve this issue, ordered by increasing complexity:

-- Method 1: Simple recovery attempt
USE master;
GO
ALTER DATABASE [XXX] SET ONLINE;
GO

If that fails, try forcing the database into single-user mode:

-- Method 2: Force single-user mode
ALTER DATABASE [XXX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE [XXX] SET MULTI_USER;

For stubborn cases where the database remains stuck, you may need to:

-- Method 3: Emergency mode recovery
ALTER DATABASE [XXX] SET EMERGENCY;
ALTER DATABASE [XXX] SET SINGLE_USER;
DBCC CHECKDB ([XXX], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
ALTER DATABASE [XXX] SET MULTI_USER;

Warning: The REPAIR_ALLOW_DATA_LOSS option may result in data loss. Always attempt to restore from backup first if possible.

To avoid this situation:

• Use proper query termination methods (KILL WITH STATUSONLY first)
• Implement query timeouts
• Monitor long-running transactions
• Consider using READ COMMITTED SNAPSHOT isolation

-- Example: Setting query timeout
-- Client-side (ADO.NET example)
SqlCommand cmd = new SqlCommand(query, connection);
cmd.CommandTimeout = 300; // 5 minutes

-- Server-side configuration
EXEC sp_configure 'remote query timeout', 300;
RECONFIGURE;

For DBAs managing multiple servers, here's a more comprehensive recovery script:

DECLARE @dbname NVARCHAR(128) = 'XXX';
DECLARE @sql NVARCHAR(MAX);

-- Check database state
IF EXISTS (
    SELECT 1 FROM sys.databases 
    WHERE name = @dbname 
    AND state_desc = 'ONLINE'
)
BEGIN
    PRINT 'Database is already online';
END
ELSE
BEGIN
    -- Attempt standard recovery
    SET @sql = 'ALTER DATABASE [' + @dbname + '] SET ONLINE';
    BEGIN TRY
        EXEC sp_executesql @sql;
        PRINT 'Database brought online successfully';
    END TRY
    BEGIN CATCH
        PRINT 'Standard recovery failed, attempting emergency mode';
        SET @sql = 'ALTER DATABASE [' + @dbname + '] SET EMERGENCY';
        EXEC sp_executesql @sql;
        
        SET @sql = 'ALTER DATABASE [' + @dbname + '] SET SINGLE_USER';
        EXEC sp_executesql @sql;
        
        SET @sql = 'DBCC CHECKDB ([' + @dbname + '], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS';
        EXEC sp_executesql @sql;
        
        SET @sql = 'ALTER DATABASE [' + @dbname + '] SET MULTI_USER';
        EXEC sp_executesql @sql;
    END CATCH
END

When you encounter the "Database 'XXX' is in transition" error in SQL Server, it typically means the database is stuck in an intermediate state between operations. This commonly occurs after cancelling a long-running query or during database recovery operations.

The database might be in one of these transition states:

• RECOVERY_PENDING
• RESTORING
• OFFLINE
• EMERGENCY

First, check the exact state of your database:

SELECT name, state_desc FROM sys.databases WHERE name = 'XXX';

Try these T-SQL commands to bring the database online:

-- Method 1: Simple recovery attempt
ALTER DATABASE [XXX] SET ONLINE;

-- Method 2: Emergency mode recovery (SQL Server 2012+)
ALTER DATABASE [XXX] SET EMERGENCY;
ALTER DATABASE [XXX] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DBCC CHECKDB ([XXX], REPAIR_ALLOW_DATA_LOSS);
ALTER DATABASE [XXX] SET MULTI_USER;

If the database shows as RESTORING:

-- For incomplete restore operations
RESTORE DATABASE [XXX] WITH RECOVERY;

For persistent cases, examine the SQL Server error logs:

EXEC sp_readerrorlog 0, 1, 'XXX';

To avoid future occurrences:

• Always properly terminate connections before maintenance
• Configure appropriate timeout settings
• Monitor long-running transactions

Create a job to monitor database states:

DECLARE @ProblemDBs TABLE (DBName NVARCHAR(128), StateDesc NVARCHAR(60));
INSERT INTO @ProblemDBs
SELECT name, state_desc 
FROM sys.databases 
WHERE state NOT IN (0, 1); -- 0=ONLINE, 1=RESTORING

IF EXISTS (SELECT 1 FROM @ProblemDBs)
BEGIN
    -- Send alert or take corrective action
    SELECT * FROM @ProblemDBs;
END