What you will learn
Recovery models control recovery options and log management. SIMPLE still logs transactions; FULL requires log backups for an ongoing recoverable chain. BULK_LOGGED can limit point-in-time recovery within a log backup containing minimally logged work. Choose the model from the acceptable data-loss window, not from a desire to hide log growth.
Before you begin
Use an isolated SQL Server 2022 Developer lab on host1.sample.com and a current SQL Server Management Studio client. Developer edition is for nonproduction use. Connect with a dedicated lab administrator for setup; application access is tested separately. The examples use TutorialDB and synthetic rows only. GO is a client batch separator, not a Transact-SQL statement. Sample paths are on the database server, not your workstation. Create TutorialDB first. Prepare C:\SqlBackups on the server with service-account write access and use a new backup filename for each exercise run.
1. Read the current state
SELECT name,recovery_model_desc,log_reuse_wait_desc
FROM sys.databases WHERE name=N'TutorialDB';
The recovery model is database-specific and may have been inherited from model. Log truncation marks inactive log space reusable; it does not make the physical log file smaller. Long transactions, backups, replication, and availability replicas can affect reuse.
2. Start a full-recovery lab chain
ALTER DATABASE TutorialDB SET RECOVERY FULL;
BACKUP DATABASE TutorialDB
TO DISK=N'C:\SqlBackups\TutorialDB_baseline.bak' WITH CHECKSUM, STATS=10;
BACKUP LOG TutorialDB
TO DISK=N'C:\SqlBackups\TutorialDB_first.trn' WITH CHECKSUM, STATS=10;
After switching from SIMPLE, take a data backup to establish the chain before depending on log backups. These files are new lab media; do not add WITH FORMAT or overwrite an existing recovery set. A backup command runs in the SQL Server process and uses its filesystem access.
3. Investigate reuse without shrinking
USE TutorialDB;
GO
SELECT total_log_size_in_bytes,used_log_space_in_bytes,used_log_space_in_percent
FROM sys.dm_db_log_space_usage;
SELECT log_reuse_wait_desc FROM sys.databases WHERE database_id=DB_ID();
DBCC OPENTRAN;
Observe again after a log backup. If ACTIVE_TRANSACTION persists, locate and understand the transaction before deciding whether it should finish or be canceled. Shrinking cannot solve an ongoing transaction or an unhealthy replication dependency.
4. Read the example output
The following is illustrative, normalized output, not a transcript from a customer system. Values depend on your release and lab state.
Illustrative state after successful backups:
recovery_model_desc log_reuse_wait_desc
FULL NOTHING
A different wait is not automatically a failure; evaluate it with the active workload. NOTHING means no current reuse blocker, not that the file should be reduced.
Verify the result
Confirm full and log backups completed, retain their media together, and perform the restore lesson. A recovery model label alone cannot prove that point-in-time recovery is possible.
Troubleshooting
A log-backup failure after changing models often indicates that a qualifying data backup has not yet established the chain. Growing logs with successful backups can still reflect long transactions or replica lag. Review the actual reuse wait and available storage.
Recovery and next steps
Schedule and monitor ongoing log backups for FULL recovery. Switching to SIMPLE breaks the log-backup chain and must be a deliberate recovery-policy change, followed by a new chain if FULL is restored. Size the log for normal peak demand.