2

We have a database server who's data drive filled up see error log:

Error: 17053, Severity: 16, State: 1.
D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDb_log.ldf: Operating system error 112(There is not enough space on the disk.) encountered.
Error: 9002, Severity: 17, State: 4.
The transaction log for database 'MyDb' is full due to 'ACTIVE_TRANSACTION'. 
Error: 1101, Severity: 17, State: 12.
Could not allocate a new page for database 'MyDb' because of insufficient disk space in filegroup 'PRIMARY'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Error: 3314, Severity: 21, State: 3.
During undoing of a logged operation in database 'MyDb', an error occurred at log record ID (656640:40216:53). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Database MyDb was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
Error during rollback. shutting down database (location: 1).
Error: 9001, Severity: 21, State: 5.
The log for database 'MyDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Error: 3314, Severity: 21, State: 3.
During undoing of a logged operation in database 'MyDb', an error occurred at log record ID (656767:141696:38). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Error: 3314, Severity: 21, State: 5.
During undoing of a logged operation in database 'MyDb', an error occurred at log record ID (656636:43848:5). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.
Starting up database 'MyDb'.
Recovery of database 'MyDb' (6) is 0% complete (approximately 12971 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 0% complete (approximately 13720 seconds remain). Phase 1 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 0% complete (approximately 20674 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 0% complete (approximately 12011 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 0% complete (approximately 11984 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 1% complete (approximately 6901 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 2% complete (approximately 3721 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 3% complete (approximately 2616 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 4% complete (approximately 2080 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 5% complete (approximately 1721 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 6% complete (approximately 1473 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 7% complete (approximately 1280 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 8% complete (approximately 1116 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 9% complete (approximately 952 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 11% complete (approximately 787 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 14% complete (approximately 624 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 18% complete (approximately 462 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 26% complete (approximately 297 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 44% complete (approximately 136 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 44% complete (approximately 136 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Recovery of database 'MyDb' (6) is 85% complete (approximately 18 seconds remain). Phase 3 of 3. This is an informational message only. No user action is required.
Error: 17053, Severity: 16, State: 1.
D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyDb.mdf: Operating system error 112(There is not enough space on the disk.) encountered.
Error: 1101, Severity: 17, State: 12.
Could not allocate a new page for database 'MyDb' because of insufficient disk space in filegroup 'DEFAULT'. Create the necessary space by dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
Error: 3314, Severity: 21, State: 1.
During undoing of a logged operation in database 'MyDb', an error occurred at log record ID (656640:10816:112). Typically, the specific failure is logged previously as an error in the Windows Event Log service. Restore the database or file from a backup, or repair the database.

I have freed 120GB space but the database is still in recovery pending

I have tried to set it online

ALTER DATABASE MyDB SET ONLINE WITH ROLLBACK IMMEDIATE

but I get the error

Msg 5061, Level 16, State 1, Line 4
ALTER DATABASE failed because a lock could not be placed on database 'MyDb'. Try again later.
Msg 5069, Level 16, State 1, Line 4
ALTER DATABASE statement failed.

If I try ALTER DATABASE MyDB SET EMERGENCY

I get the same error.

If I run sp_lock I can see SPID -2 has a shared lock on the database

I ran

-- https://www.mytechmantra.com/LearnSQLServer/Kill-Negative-SPID-in-SQL-Server/
SELECT 
    DISTINCT(request_owner_guid) as UoW_Guid
FROM sys.dm_tran_locks
    WHERE request_session_id =-2
GO

but it returned a GUID of 00000000-0000-0000-0000-000000000000 which I cannot kill, I get

Msg 6110, Level 16, State 1, Line 17
The distributed transaction with UOW {00000000-0000-0000-0000-000000000000} does not exist.

I tried

sp_configure 'show advanced options', 1  -- was 0
GO  
RECONFIGURE  
GO  
Exec sp_configure 'in-doubt xact resolution',2 -- was 0
GO

but that has not cleared the SPID.

I have restarted the MSDTC which has not cleared the SPID. My next step is to restart the SQL Server

How can I get this database online? Will restarting the service kill the -2 SPID, allowing me to set the database online (or will the restart bring it online anyway?)

  • I suspect restarting your SQL Instance will retry the recovery operation, but honestly I don't know. I've not run into a situation where my recovery ran out of space. Do you have a backup available? Are there other databases on the server that can be taken offline shortly for a service restart? Weigh the business needs to the technical needs and make your decision based on that. – John Eisbrener Jun 5 at 14:19
  • @JohnEisbrener, thanks. We restarted the service which triggered recovery as normal and the database is back online – SEarle1986 Jun 5 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.