-- Anzahl und Größe der VLFs anzeigen
DBCC LOGINFO;

-- Moderne Alternative (SQL Server 2012+), detaillierte VLF-Info
SELECT 
  database_id,
  file_id,
  vlf_begin_offset,
  vlf_size_mb,
  vlf_sequence_number,
  vlf_active
FROM sys.dm_db_log_info(DB_ID('IhreDatenbank'))
ORDER BY vlf_sequence_number;

-- Recovery Model ändern
ALTER DATABASE [MeineDB] SET RECOVERY FULL;
ALTER DATABASE [MeineDB] SET RECOVERY SIMPLE;
ALTER DATABASE [MeineDB] SET RECOVERY BULK_LOGGED;

-- Aktuelles Modell prüfen
SELECT name, recovery_model_desc FROM sys.databases;

-- Transaktionslog-Backup
BACKUP LOG [MeineDB] 
TO DISK = N'D:\Backups\MeineDB_Log_20250315_1200.trn'
WITH COMPRESSION;

-- Informationen über Log-Backups (Größe, letztes Backup)
SELECT 
  database_name,
  backup_start_date,
  backup_size / 1024 / 1024 AS SizeMB,
  DATEDIFF(minute, backup_start_date, backup_finish_date) AS DurationMin
FROM msdb.dbo.backupset
WHERE type = 'L' -- L = Log-Backup
  AND database_name = 'MeineDB'
ORDER BY backup_start_date DESC;

-- Bei Always On: Log-Sendungsstatus prüfen
SELECT 
  ag.name,
  ars.role_desc,
  drs.synchronization_state_desc,
  drs.log_send_queue_size,
  drs.redo_queue_size
FROM sys.dm_hadr_database_replica_states drs
JOIN sys.availability_replicas ar ON drs.replica_id = ar.replica_id
JOIN sys.availability_groups ag ON ar.group_id = ag.group_id
JOIN sys.availability_replicas ars ON drs.replica_id = ars.replica_id;

-- Log-Datei verkleinern (nur nach Log-Backup und Checkpoint)
USE [MeineDB];
CHECKPOINT; -- Dirty Pages schreiben

DBCC SHRINKFILE (N'MeineDB_log', 'ZIELGRÖSSE_IN_MB');

-- Um den aktuellen Log-Speicherplatz zu sehen:
SELECT 
  name,
  size * 8 / 1024 AS CurrentSizeMB,
  CAST(size * 8 / 1024.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT) * 8 / 1024.0 AS DECIMAL(10,2)) AS FreeSpaceMB
FROM sys.database_files
WHERE type_desc = 'LOG';

-- Aktive Transaktionen finden, die das Log blockieren
DBCC OPENTRAN;

-- Detailliertere Information über lang laufende Transaktionen
SELECT 
  st.session_id,
  st.text,
  tat.transaction_begin_time,
  DATEDIFF(minute, tat.transaction_begin_time, GETDATE()) AS RunningMinutes
FROM sys.dm_tran_active_transactions tat
JOIN sys.dm_tran_session_transactions tst ON tat.transaction_id = tst.transaction_id
JOIN sys.dm_exec_sessions s ON tst.session_id = s.session_id
CROSS APPLY sys.dm_exec_sql_text(s.most_recent_sql_handle) st
WHERE tat.transaction_state = 2; -- aktive Transaktion

-- Aktuelle Größe und Verwendung des Logs
SELECT 
  DB_NAME(database_id) AS DBName,
  CAST(total_log_size_mb AS DECIMAL(10,2)) AS TotalLogSizeMB,
  CAST(used_log_space_mb AS DECIMAL(10,2)) AS UsedLogMB,
  CAST(used_log_space_in_percent AS DECIMAL(5,2)) AS UsedPercent
FROM sys.dm_db_log_space_usage;

-- Log-Wachstums-Ereignisse im Standardprotokoll
SELECT TOP 10 log_time, [text], database_name
FROM sys.fn_xe_file_target_read_file(N'System_Health*.xel', NULL, NULL, NULL)
WHERE [text] LIKE '%log%grow%'
ORDER BY log_time DESC;