Paul Randal is a fellow MVP who worked at Microsoft on the SQL Storage Engine Team for eight years. He does a very good job of explaining how logging and recovery works in SQL Server in this TechNet article.
This seems to be a very poorly understood subject among novice DBAs, based on how many questions I see on the MSDN SQL Database Engine Forum about why someone’s transaction log is growing out of control.
The queries below will tell you a lot of information about what recovery model is being used, how big and how full their files are, and what has happened with recent database backups.
-- Individual File Size query SELECT name AS 'File Name' , file_id, physical_name AS 'Physical Name', size/128 AS 'Total Size in MB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'Available Space In MB' FROM sys.database_files; -- Get recovery model and log reuse wait description for each database SELECT [name], recovery_model_desc, log_reuse_wait_desc FROM sys.databases; -- Get File names and paths for all databases on an instance SELECT [dbid], fileid, [filename] FROM sys.sysaltfiles; -- Get recent Full backups SELECT TOP(10) database_name, name, backup_start_date, backup_finish_date, [type], backup_size FROM msdb.dbo.backupset AS bs WHERE type = 'D' ORDER BY bs.backup_finish_date DESC -- Get recent Transaction Log backups SELECT TOP(10) database_name, name, backup_start_date, backup_finish_date, [type], backup_size FROM msdb.dbo.backupset AS bs WHERE type = 'L' ORDER BY bs.backup_finish_date DESC