Thursday, November 27, 2008

DBCC SQLPERF(logspace)

DBCC SQLPERF(logspace) is an absolutely functional command if you are only interested in consumption of your database log files. It provides the cumulative size for each log file for each database on the SQL Server instance as well as the amount of space consumed (as a percentage of total log file size). A drawback is the fact that the results are an aggregate for the database.

logspace can be 1 of the value below:
UMSStats - SQL thread management
WaitStats - resources, wait types
IOStats - outstanding reads & writes (note: always zeros)
RAStats - read ahead activity (note: always returns zeros)
Threads - I/O / CPU / memory usage per thread
SpinLockStats - statistics on spinlocks
UMSSpinStats - statistics on UMS
NetStats - ODS statistics
LRUStats - LRU-MRU chain statistics (Note: free page scan always zero)