How to Monitor Transaction Log Usage In SQL Server?

Question

I would like to monitor transaction log usage, regarding all the following aspects:

• What Task\Job\Query is making it fill the drive\file.

• Log file usage percentage.

• What time the transaction happened.

Any relevant way of doing this which is tested would be helpful.

Answer 1

The easiest way is to buy an off-the-shelf monitoring tool. They all give you this kind of information - Idera SQL DM, Quest Spotlight, SentryOne SQL Sentry do these kinds of things with really low impact.

The next easiest way is to build something yourself. If you're going that route, I'd start by logging sp_WhoIsActive to a table - especially with the @get_transaction_info = 1 switch.

If you try the roll-your-own approach, you need to be aware that queries aren't the only thing that will cause the transaction log to grow. For example, if you're using replication, database mirroring, or Always On Availability Groups, SQL Server needs to retain history when one of those replicas is offline. To learn more about what's causing that, check log_reuse_wait_desc:

SELECT name, log_reuse_wait_desc FROM sys.databases;

Answer 2

Windows Performance Monitor will display a graph showing the percentage of the transaction log currently in use against time for each SQL Server database. The counter is called "Percent Log Used" and it's in the "SQL Server:Databases" category.

It is useful for monitoring how quickly the log is filling up, and also detecting auto-grow events. It gives an intuitive visual indication of transaction log activity.

Answer 3

An easy way would be to use the already included default traces.

Read any event of type 'Log File Auto Grow' or 'Log File Auto Shrink' and you will have enough information to feed your curiosity.

Sample script:

DECLARE @filename NVARCHAR(200)

SELECT  @filename = CAST(value AS NVARCHAR(4000))
FROM    ::FN_TRACE_GETINFO(DEFAULT)
WHERE   traceid = 1
        AND property = 2

SELECT
    TE.name AS [EventName]
    , t.DatabaseName
    , t.FileName
    , t.ApplicationName
    , t.HostName
    , t.LoginName
    , t.SPID
    , t.ClientProcessId
    , t.Duration
    , t.StartTime
    , t.EndTime

FROM sys.fn_trace_gettable(@filename,DEFAULT) T
JOIN sys.trace_events TE ON T.EventClass = TE.trace_event_id
WHERE   te.name = 'Log File Auto Grow'
     OR te.name = 'Log File Auto Shrink'

It limits you however to a single instance. You'll have to have some automation (or external tools) involved if you want to check multiple instances at once.

Answer 4

As someone who is trying to use the DMVs as much as possible, there are few scripts that can give you a quick heads up

1)

This one will give you an info about the full backups and tran log backups, how long it took it, what was the size of the backup,where it is located,expiration date(if it has),logical device (if it exists as well), and server name.

Keep in mind its filtered for backups for past 7 days, and it displays all databases on the server, but you can sort it up as u please

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
msdb.dbo.backupset.expiration_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.logical_device_name, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name, 
msdb.dbo.backupset.description 
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE() - 7) 
ORDER BY 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_finish_date 

---

2)

This one will show you records that are placed in a log buffer, in what state transaction was, how many records are logged, size in bytes ,and a query that executed it. In a nutshell it displays all inserts/deletes/update from an active transaction that are not committed/rolled back yet

SELECT DTST.[session_id],
DES.[login_name] AS [Login Name],
DB_NAME (DTDT.database_id) AS [Database],
DTDT.[database_transaction_begin_time] AS [Begin Time],
-- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],
CASE DTAT.transaction_type
WHEN 1 THEN 'Read/write'
WHEN 2 THEN 'Read-only'
WHEN 3 THEN 'System'
WHEN 4 THEN 'Distributed'
END AS [Transaction Type],
CASE DTAT.transaction_state
WHEN 0 THEN 'Not fully initialized'
WHEN 1 THEN 'Initialized, not started'
WHEN 2 THEN 'Active'
WHEN 3 THEN 'Ended'
WHEN 4 THEN 'Commit initiated'
WHEN 5 THEN 'Prepared, awaiting resolution'
WHEN 6 THEN 'Committed'
WHEN 7 THEN 'Rolling back'
WHEN 8 THEN 'Rolled back'
END AS [Transaction State],
DTDT.[database_transaction_log_record_count] AS [Log Records],
DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used],
DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd],
DEST.[text] AS [Last Transaction Text],
DEQP.[query_plan] AS [Last Query Plan]
FROM sys.dm_tran_database_transactions DTDT
INNER JOIN sys.dm_tran_session_transactions DTST
ON DTST.[transaction_id] = DTDT.[transaction_id]
INNER JOIN sys.[dm_tran_active_transactions] DTAT
ON DTST.[transaction_id] = DTAT.[transaction_id]
INNER JOIN sys.[dm_exec_sessions] DES
ON DES.[session_id] = DTST.[session_id]
INNER JOIN sys.dm_exec_connections DEC
ON DEC.[session_id] = DTST.[session_id]
LEFT JOIN sys.dm_exec_requests DER
ON DER.[session_id] = DTST.[session_id]
CROSS APPLY sys.dm_exec_sql_text (DEC.[most_recent_sql_handle]) AS DEST
OUTER APPLY sys.dm_exec_query_plan (DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC;
-- ORDER BY [Duration ms] DESC;

---

3)

And the last one is IO related with info on all databases: Log reads,writes, read and write stall (how much time sql server had to wait to write or read from a log) and Read/Write ratio on a log and data files. Keep in mind that these are cumulative stats, and they reset only after restart, but you can insert snapshots in a table and keep a track of it.

select
fs.database_id as [DB ID], fs.file_id as [File Id], mf.name as [File Name]
,mf.physical_name as [File Path], mf.type_desc as [Type], fs.sample_ms as [Time]
,fs.num_of_reads as [Reads], fs.num_of_bytes_read as [Read Bytes]
,fs.num_of_writes as [Writes], fs.num_of_bytes_written as [Written Bytes]
,fs.num_of_reads + fs.num_of_writes as [IO Count]
,convert(decimal(5,2),100.0 * fs.num_of_bytes_read /
(fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Read %]
,convert(decimal(5,2),100.0 * fs.num_of_bytes_written /
(fs.num_of_bytes_read + fs.num_of_bytes_written)) as [Write %]
,fs.io_stall_read_ms as [Read Stall], fs.io_stall_write_ms as [Write Stall]
,case when fs.num_of_reads = 0
then 0.000
else convert(decimal(12,3),1.0 * fs.io_stall_read_ms / fs.num_of_reads)
end as [Avg Read Stall]
,case when fs.num_of_writes = 0
then 0.000
else convert(decimal(12,3),1.0 * fs.io_stall_write_ms / fs.num_of_writes)
end as [Avg Write Stall]
from
sys.dm_io_virtual_file_stats(null,null) fs join
sys.master_files mf with (nolock) on
fs.database_id = mf.database_id and fs.file_id = mf.file_id
join sys.databases d with (nolock) on
d.database_id = fs.database_id
where
fs.num_of_reads + fs.num_of_writes > 0;

Hope it gives you few startup points, in case your boss is not willing to invest in 3rd party tools, or you dont feel like setting up data collector tool

Answer 5

SQL Server has a built-in tool that can do that, which is called Data Collector. Almost every professional hates it because it's confusing to set up, hard to customize and almost impossible to troubleshoot. However, when it works, it is enough for this kind of basic monitoring.

You can find more information on the Data Collector here: https://docs.microsoft.com/en-us/sql/relational-databases/data-collection/data-collection

As Brent says, you can always buy a commercial tool. It won't come for free though. It's a good investment if you have to monitor multiple aspects of the server performance, but for a basic thing like this seems overkill. Maybe a custom collection process with a SQL Agent job is enough, but the Data Collector will do that out of the box, so why not try that first?