Microsoft – SQLServer – Database – Log File Utilization
To review MS SQL Server Data file organization and size the MS Windows Explorer is one of the first and most accessible tools.
Internal to SQL Server itself, the traditional command has been the “DBCC Sqlperf(logspace)” command.
The command lists each database, the total size of each database’s log files, percentile used, and status.
Various design & technical issues govern the utilization, size, growth pattern, and re-usability of database log files. Those choices includes:
- Database Recovery Settings (SIMPLE, BULK LOGGED, and FULL)
- Database Mirroring
- Database Replication
Log files are a bit different than data files. Those differences are due to:
- As strict, very time conscious and related journal entries, Log files are used and accessed serially
Because of there unique qualities, occasionally it is useful to be a bit familiar with database log files current and max sizes, recovery settings, whether they are being freed, etc.
All Databases Log file size & Utilization %
Current \ Contextual Databases Log file size & Utilization %
--replace <db-name> with the name of DB use <db-name> go select dbname(db_id()) as databaseName , type , [name] as fileNameSymbolic , physical_name as fileNameActual , ([size] / 128) as sizeInMB , ([max_size] / 128) as maxSizeInMB , (fileproperty(name, 'spaceUsed')) / 128 as spaceUsedInMB , (size - (fileproperty(name, 'spaceUsed'))) / 128 as availInMB from sys.database_files tblDatabaseFile where ( (tblDatabaseFile.type = 1) )
Determine if any Database Operations is in the way of Log Re-use
select name , log_reuse_wait , log_reuse_wait_desc from sys.databases tblDatabase
- The Rambling DBA: Jonathan Kehayias – The random ramblings and rantings of frazzled SQL Server DBA – Getting Log Space Usage without using DBCC SQLPERF
- File Property