SQL Server Portal

SQL Server database Maintenance plays an important role to improve database performance. One of the important aspects of maintenance is SQL Server log, even if you configured it properly, you should keep an eye on it and time to time, you should do its maintenance. To do the maintenance, you need to know the size of SQL Server log, used size and its percentage as well. There are multiple ways to achieve it. One of the famous methods of DBCC command is DBCC SQLPERF to find these details. I also have been using this DBCC Command since SQL Server 2005. It works fine but the only problem with this DBCC command is that if you need to manipulate its result set further, you need to place its result set in the temporary table and manipulate.

A few days ago, I was working on database log size and looking for some alternate solution. After some research, I found one undocumented dynamic management view that is shipped with SQL Server 2012 namely dm_db_log_space_usage. It gives the log size of the database and other details in bytes, you can convert it in MBs and manipulate its result set further (no need for temporary table) as well. Given below is the script to demonstrate how it works.

USE AdventureWorks2012
GO
SELECT
DB_NAME(database_id) AS [Database Name],
ROUND(CONVERT(FLOAT,total_log_size_in_bytes/1024)/1024,2) AS [Log Size (MB)],
ROUND(CONVERT(FLOAT,used_log_space_in_bytes/1024)/1024,2) AS [Log Used size (MB)],
ROUND(used_log_space_in_percent,2) AS [Log Used % (MB)]
FROM
sys.dm_db_log_space_usage

--OUTPUT