SQL Server数据库判断最近一次的备份执行结果(最新推荐)

1 麻烦的地方

在SQL Server的官方文档里面可以看到备份和还原的表,但是这些表里面只能找到备份成功的相关信息,无法找到备份失败的记录,比如msdb.dbo.backupset。对于一些监控系统未监控作业的情况下,想要监控数据库备份任务执行失败而触发告警规则,有些麻烦。
但是SQL server内部是可以通过查询errorlog来判断数据库备份作业是否成功:

2 获取errorlog的trace表

我们可以借助sys.traces定位到errorlog的trace文件路径,然后通过再通过fn_trace_gettable将errlog的trace文件转换为普通的表来查询即可。

定位errorlog的trace文件物理路径

SELECT
 REVERSE(
 SUBSTRING(
 REVERSE([path]),
 CHARINDEX(CHAR(92), REVERSE([path])),
 260
 )
 ) + N'log.trc'
 FROM
 sys.traces
 WHERE
 is_default = 1

输出结果:

2. 得到路径后,使用fn_trace_gettable将errolog的trace文件转换为普通的数据表

SELECT
 *
FROM
 sys.fn_trace_gettable(
 'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
 default
 )

3. 查找与备份相关的事件记录,在trace文件中对应的EventClass为115,并将所有备份开头的语句筛选出来

SELECT
 TextData,Databasename,StartTime
FROM
 sys.fn_trace_gettable(
 'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
 default
 )
WHERE
 EventClass = 115
 AND UPPER(CONVERT(nvarchar(max), TextData)) LIKE 'BACKUP%'

从返回的TextData中没有找到是否关于备份成功或者失败的说明,也没有在其它列中找到相关描述,需要结合msdb.dbo.backupset来判断。

3 结合备份表backupset,判断备份状态筛选出所有数据库的备份任务执行情况

从查询的结果可知,每个成功的备份都有1个开始时间和结束时间,考虑将开始时间与trace文件转换的表进行对比: flowchart LR A[trace表的获取备份记录和时间]--> B[取得StartTime列]B[与backupset表对比判断]--> C{是否存在与StartTime列对应的值}C--存在-->D[备份成功]C--不存在-->E[备份失败]

形成了具体的思路后,下面将trace转换的表的StartTime列与backupset表的backup_start_date列进行对比判断
2. 判断存在对应的值则说明备份成功,不存在则备份失败

SELECT
 dt.DatabaseName,
 dt.StartTime,
 bs.backup_start_date,
 bs.backup_finish_date,
 [Status] = CASE
 WHEN bs.backup_start_date IS NULL THEN (dt.DatabaseName) + '数据库备份失败'
 ELSE (dt.DatabaseName) + '数据库备份成功'
 END
FROM
 sys.fn_trace_gettable(
 'S:\MSSQL13.MSSQLSERVER\MSSQL\Log\log.trc',
 default
 ) AS dt
 LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name
 AND ABS(
 DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)
 ) < 5
WHERE
 dt.EventClass = 115
 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE N'BACKUP%'
ORDER BY
 dt.StartTime DESC;

4 形成用于告警的SQL语句

完成上面的操作之后,我们已经能够看到所有数据库的备份是成功还是失败的状态,现在还需要将SQL再度细化,输出所有数据库最近一次备份执行成功或者失败的信息:即每个数据库只有一行记录用于说明最近一次的备份状态。
下面有两种写法可以实现,第1种是游标的写法,性能极差,后来找chatgpt一起讨论之后,采用了group by优化,形成第2种写法。

第1种,游标查看每个数据库最近一次备份状态

DBCC FREE
DECLARE @databaseName1 nvarchar(100)
DECLARE @sql nvarchar(4000)
DECLARE db_cursor CURSOR FOR
 SELECT name
 FROM sys.databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databaseName1
WHILE @@FETCH_STATUS = 0 
BEGIN 
 SET @sql = '
	SELECT
 TOP 1 dt.DatabaseName,
 dt.StartTime,
 bs.backup_start_date,
 bs.backup_finish_date,
 [Status] = CASE
 WHEN bs.backup_start_date IS NULL THEN (dt.DatabaseName) + ''数据库备份失败''
 ELSE (dt.DatabaseName) + ''数据库备份成功''
 END
FROM
 sys.fn_trace_gettable(
 (
 SELECT
 REVERSE(
 SUBSTRING(
 REVERSE([path]),
 CHARINDEX(CHAR(92), REVERSE([path])),
 260
 )
 ) + N''log.trc''
 FROM
 sys.traces
 WHERE
 is_default = 1
 ),
 default
 ) AS dt
 LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name
 AND ABS(
 DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)
 ) < 5
WHERE
 dt.EventClass = 115
 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE N''BACKUP%''
 AND dt.DatabaseName=''' + @databaseName1 +''' ORDER BY dt.StartTime DESC'
 EXEC sp_executesql @sql
 FETCH NEXT FROM db_cursor INTO @databaseName1
END 
CLOSE db_cursor
DEALLOCATE db_cursor

可以看到性能极差,查询3条数据耗时24秒,每次游标都要到消耗临时表和进行大量的逻辑读取。

SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 14,逻辑读取 36 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 98 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 2,逻辑读取 30 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 7,逻辑读取 42 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 31 毫秒,占用时间 = 27 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 23 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
(1 行受影响)
表 'backupset'。扫描计数 1,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 468 毫秒,占用时间 = 678 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 468 毫秒,占用时间 = 678 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 23 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
(0 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 406 毫秒,占用时间 = 732 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 406 毫秒,占用时间 = 732 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 21 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
(0 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 422 毫秒,占用时间 = 659 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 422 毫秒,占用时间 = 660 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 15 毫秒,占用时间 = 616 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
(0 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 375 毫秒,占用时间 = 678 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 375 毫秒,占用时间 = 678 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 1286 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
(1 行受影响)
表 'backupset'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 375 毫秒,占用时间 = 781 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 375 毫秒,占用时间 = 781 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 31 毫秒,占用时间 = 1608 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 13 毫秒,占用时间 = 13 毫秒。
(1 行受影响)
表 'backupset'。扫描计数 1,逻辑读取 3 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 390 毫秒,占用时间 = 737 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 406 毫秒,占用时间 = 751 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysobjvalues'。扫描计数 2,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syspalvalues'。扫描计数 0,逻辑读取 14 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysguidrefs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysclsobjs'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'syssingleobjrefs'。扫描计数 1,逻辑读取 6 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sysdbreg'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 20 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 12 毫秒,占用时间 = 12 毫秒。
(0 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 468 毫秒,占用时间 = 2492 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 484 毫秒,占用时间 = 2505 毫秒。
表 'Worktable'。扫描计数 0,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(1 行受影响)
 SQL Server 执行时间:
 CPU 时间 = 16 毫秒,占用时间 = 23 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

第2种,使用MAX函数和group by优化

SELECT
 [Status] = MAX(CASE
 WHEN bs.backup_start_date IS NULL THEN dt.DatabaseName+'数据库备份失败'
 ELSE dt.DatabaseName+'数据库备份成功'
 END)
 FROM
 sys.fn_trace_gettable(
 (
 SELECT
 REVERSE(
 SUBSTRING(
 REVERSE([path]),
 CHARINDEX(CHAR(92), REVERSE([path])),
 260
 )
 ) + N'log.trc'
 FROM
 sys.traces
 WHERE
 is_default = 1
 ),
 default
 ) AS dt
 LEFT OUTER JOIN msdb.dbo.backupset AS bs ON dt.DatabaseName = bs.database_name
 AND ABS(DATEDIFF(SECOND, dt.StartTime, bs.backup_start_date)) < 5
 WHERE
 dt.EventClass = 115
 AND UPPER(CONVERT(nvarchar(max), dt.TextData)) LIKE 'BACKUP%'
 GROUP BY
 dt.DatabaseName

这次执行只要1秒钟,占用的资源也极低。

SQL Server 分析和编译时间: 
 CPU 时间 = 15 毫秒,占用时间 = 20 毫秒。
(3 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'backupset'。扫描计数 1,逻辑读取 48 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
 SQL Server 执行时间:
 CPU 时间 = 469 毫秒,占用时间 = 935 毫秒。
SQL Server 分析和编译时间: 
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。
 SQL Server 执行时间:
 CPU 时间 = 0 毫秒,占用时间 = 0 毫秒。

截止到此,基本大功告成了。接下来要实现的就是监控系统怎么取得指标和触发告警,具体可以根据不同的监控平台进行配置。
实际上还可以通过xp_readerrorlog来读取errlog更加快速得筛选出备份失败的记录,但本次没有再测试,有兴趣的朋友可以自行参考测试。

作者:开始折腾吧!原文地址:https://www.cnblogs.com/kernelry/p/17383332.html

%s 个评论

要回复文章请先登录注册