如何监控 SQL Server 中的事务日志使用情况？

最简单的方法是购买现成的监控工具。它们都为您提供此类信息 - Idera SQL DM、Quest Spotlight、SentryOne SQL Sentry 以非常低的影响进行此类事情。

下一个最简单的方法是自己构建一些东西。如果你要走那条路，我会先将 sp_WhoIsActive记录到一个表中——尤其是使用@get_transaction_info = 1开关。

如果您尝试自己滚动的方法，则需要注意查询并不是导致事务日志增长的唯一因素。例如，如果您使用复制、数据库镜像或 Always On 可用性组，则 SQL Server 需要在其中一个副本脱机时保留历史记录。要了解有关导致该问题的更多信息，请检查log_reuse_wait_desc：

SELECT name, log_reuse_wait_desc FROM sys.databases;

Windows 性能监视器将显示一个图表，显示每个 SQL Server 数据库当前使用的事务日志相对于时间的百分比。该计数器称为“使用的日志百分比”，它位于“SQL Server：数据库”类别中。

它对于监控日志填满的速度以及检测自动增长事件很有用。它提供了事务日志活动的直观视觉指示。

一种简单的方法是使用已经包含的默认跟踪。

阅读任何“日志文件自动增长”或“日志文件自动收缩”类型的事件，您将获得足够的信息来满足您的好奇心。

示例脚本：

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'

但是，它会将您限制为单个实例。如果您想一次检查多个实例，则必须涉及一些自动化（或外部工具）。

作为尝试尽可能多地使用 DMV 的人，很少有脚本可以让您快速了解

1)

这将为您提供有关完整备份和 tran 日志备份的信息，它花了多长时间，备份的大小是多少，它的位置，到期日期（如果有），逻辑设备（如果它存在好）和服务器名称。

请记住，它过滤了过去 7 天的备份，它显示了服务器上的所有数据库，但您可以根据需要对其进行排序

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)

这将显示放置在日志缓冲区中的记录、事务处于什么状态、记录了多少记录、以字节为单位的大小以及执行它的查询。简而言之，它显示尚未提交/回滚的活动事务中的所有插入/删除/更新

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)

最后一个是与所有数据库信息相关的 IO：日志读取、写入、读取和写入停顿（sql server 必须等待写入或读取日志的时间）以及日志和数据文件的读/写比率. 请记住，这些是累积统计信息，它们仅在重新启动后才会重置，但您可以在表中插入快照并对其进行跟踪。

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;

希望它能给你一些启动点，以防你的老板不愿意投资 3rd 方工具，或者你不想设置数据收集工具

SQL Server 有一个可以执行此操作的内置工具，称为 Data Collector。几乎每个专业人士都讨厌它，因为它设置起来很混乱，难以定制，而且几乎不可能排除故障。但是，当它起作用时，对于这种基本的监控就足够了。

您可以在此处找到有关数据收集器的更多信息：https ://learn.microsoft.com/en-us/sql/relational-databases/data-collection/data-collection

正如布伦特所说，您总是可以购买商业工具。但它不会免费提供。如果您必须监控服务器性能的多个方面，这是一项不错的投资，但对于像这样的基本事情来说似乎有点过分了。也许使用 SQL 代理作业的自定义收集过程就足够了，但数据收集器会开箱即用，所以为什么不先尝试呢？