When encountering SQL Server Error 9001 indicating log file unavailability, we typically see these symptoms:

Event ID: 9001
The log for database 'YourDB' is not available. 
Check the event log for related error messages.
Resolve any errors and restart the database.

First, verify the physical existence of transaction log files:

-- Check database file status
SELECT 
    name AS [Database Name],
    physical_name AS [File Path],
    state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID('YourDB');

Then examine SQL Server error logs for correlated events:

-- View recent SQL Server errors
EXEC sp_readerrorlog 0, 1, 'error';

For emergency recovery when log files are corrupted but data files are intact:

-- Emergency mode recovery (use with caution)
ALTER DATABASE YourDB SET EMERGENCY;
ALTER DATABASE YourDB SET SINGLE_USER;
DBCC CHECKDB (YourDB, REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
ALTER DATABASE YourDB SET MULTI_USER;

To rule out underlying storage issues, run these checks:

-- Windows disk check command
chkdsk C: /f /r

-- SQL Server check for IO subsystem health
SELECT 
    database_id,
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

Implement these routines to prevent future occurrences:

-- Regular integrity checks
DBCC CHECKDB ('YourDB') WITH NO_INFOMSGS;

-- Configure database mirroring or AlwaysOn for redundancy
CREATE DATABASE YourDB_Mirror AS MIRROR OF YourDB
WITH (
    PARTNER = 'TCP://mirror.server.com:5022'
);

For deeper investigation of potential hardware issues:

-- Check for memory pressure
SELECT
    physical_memory_in_use_kb/1024 AS [Memory Usage (MB)],
    locked_page_allocations_kb/1024 AS [Locked Pages (MB)]
FROM sys.dm_os_process_memory;

-- Examine disk latency
SELECT 
    DB_NAME(vfs.database_id) AS database_name,
    vfs.file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    num_of_reads,
    num_of_writes
FROM sys.dm_io_virtual_file_stats(NULL, NULL) AS vfs;

When SQL Server throws error 9001 (The log for database is not available), it typically indicates one of three scenarios:

• Transaction log file corruption
• Storage subsystem failure
• Memory pressure causing write failures

First, check these critical system indicators:

-- Check disk health using SQL Server
SELECT 
    database_id,
    file_id,
    io_stall_read_ms,
    io_stall_write_ms,
    io_stall
FROM sys.dm_io_virtual_file_stats(NULL, NULL);

-- Check for memory pressure
SELECT
    total_physical_memory_kb/1024 AS total_physical_memory_mb,
    available_physical_memory_kb/1024 AS available_physical_memory_mb,
    system_memory_state_desc
FROM sys.dm_os_sys_memory;

If corruption is confirmed, try these recovery methods in order:

1. Emergency Mode Repair:

   ALTER DATABASE [YourDB] SET EMERGENCY;
   ALTER DATABASE [YourDB] SET SINGLE_USER;
   DBCC CHECKDB ([YourDB], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;
   ALTER DATABASE [YourDB] SET MULTI_USER;

2. Rebuild Log File (SQL Server 2012+):

   CREATE DATABASE [YourDB] ON 
   PRIMARY (NAME = 'YourDB', FILENAME = 'C:\Path\YourDB.mdf')
   FOR ATTACH_REBUILD_LOG;

To rule out storage issues, run these PowerShell commands:

# Check disk SMART status
Get-PhysicalDisk | Select-Object FriendlyName, HealthStatus, OperationalStatus

# Monitor disk latency
Get-Counter '\PhysicalDisk(* *)\Avg. Disk sec/Read' -Continuous

Implement these safeguards:

• Configure database mirroring or Always On availability groups
• Set up log shipping for critical databases
• Enable checksum page verification:

  ALTER DATABASE [YourDB] SET PAGE_VERIFY CHECKSUM;

For a database named "CustomerDB", here's the complete recovery script I've used successfully:

-- Step 1: Take tail-log backup if possible
BACKUP LOG [CustomerDB] TO DISK = 'C:\Backups\CustomerDB_Tail.trn' 
WITH CONTINUE_AFTER_ERROR, NORECOVERY;

-- Step 2: Detach the corrupted database
USE master;
GO
EXEC sp_detach_db @dbname = 'CustomerDB';

-- Step 3: Rename the existing LDF file
-- (Execute this in Command Prompt as Administrator)
rename "C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustomerDB_log.ldf" CustomerDB_log_corrupted.ldf

-- Step 4: Reattach with new log
CREATE DATABASE [CustomerDB] ON 
PRIMARY (NAME = 'CustomerDB_data', FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\CustomerDB.mdf')
FOR ATTACH_REBUILD_LOG;
GO

Add these alerts to your monitoring system:

-- Create alert for log file issues
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'SQL Server Error 9001', 
    @message_id=9001, 
    @severity=0, 
    @enabled=1, 
    @delay_between_responses=0, 
    @include_event_description_in=1, 
    @category_name=N'[Uncategorized]';
GO