-- Check file existence and paths (from SQL perspective) SELECT type_desc, name, physical_name, state_desc FROM sys.master_files WHERE database_id = DB_ID(N'YourDatabaseName'); If the log file is missing, sys.master_files will still show its expected path, but the OS will not have the file. 4. Recovery Strategy Matrix | Scenario | Action | Data Loss Risk | |----------|--------|----------------| | Log file missing, but you have a full backup + all subsequent log backups | Restore with RECOVERY | None | | Log file missing, no recent log backups | Attempt emergency mode rebuild | High (only committed data in .mdf remains) | | Disk full | Free space, then restart SQL Server service | None | | Log file corrupted but .mdf intact | Use DBCC CHECKDB with ALLOW_DATA_LOSS | Moderate to High | | Restore left in NORECOVERY | Issue RESTORE DATABASE ... WITH RECOVERY | None | | File permissions | Grant Full Control to SQL service account, restart instance | None | 5. Step-by-Step Resolution Procedures Case A: Log File Missing – Full Backup Available (Minimal Risk) -- 1. Take database offline (force close any lingering handles) ALTER DATABASE YourDatabaseName SET OFFLINE; -- 2. Manually delete any orphaned log file from OS (if present but corrupt)

Unlike RECOVERING (where recovery is actively running) or SUSPECT (where recovery has definitively failed), RECOVERY PENDING means recovery cannot even begin .

The database is technically online but inaccessible to users. You will see it in SSMS Object Explorer with a yellow warning icon and (Recovery Pending) next to its name. | Cause | Description | |-------|-------------| | Missing or corrupted transaction log file (.ldf) | The log file is deleted, moved, or has sector-level corruption. | | Insufficient disk space | The drive hosting the log file is full, preventing recovery from allocating space for rollback/rollforward. | | Corrupted boot page of the log file | The first page of the log (containing VLH info) is unreadable. | | File system permission issues | SQL Server service account lacks read/write access to the log file or its folder. | | Restore operation interrupted | A RESTORE WITH NORECOVERY was left incomplete, or the restore failed mid-operation. | | Inconsistent file states | Data files and log files are out of sync (e.g., restoring old log onto newer data). | 3. Immediate Diagnostic Steps Run the following queries to assess the situation:

-- Bring back online normally ALTER DATABASE YourDatabaseName SET ONLINE; REPAIR_ALLOW_DATA_LOSS may truncate pages, drop indexes, or remove entire rows. Always export critical data before this step using SELECT INTO or BCP . Case C: Disk Space Issue -- Find log file size and growth settings SELECT name, size/128.0 AS SizeMB, growth, is_percent_growth FROM sys.database_files WHERE type_desc = 'LOG'; -- Shrink log if possible (after freeing disk space) DBCC SHRINKFILE (YourDatabaseName_log, 1024); -- Target 1 GB