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