欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

读取SQL的Agent代理的日志文件

程序员文章站 2022-06-02 23:41:43
...

读取SQL的Agent代理的日志文件 Agent 日志文件 use msdbgoif exists (select * from sysobjects where name = N'sp_sqlagent_read_errorlog' and type ='P') drop proc dbo.sp_sqlagent_read_errorloggocreate proc dbo.sp_sqlagent_read_errorlog @fileversi

读取SQL的Agent代理的日志文件 Agent 日志文件
use msdb
go

if exists (select * from sysobjects where name = N'sp_sqlagent_read_errorlog' and type ='P')
    drop proc dbo.sp_sqlagent_read_errorlog
go

create proc dbo.sp_sqlagent_read_errorlog @fileversion int = null
as

set nocount on

declare @rc int
declare @version int
declare @pos int
declare @errorlog_file nvarchar(255)
declare @filename nvarchar(255)
declare @filename_no_ext nvarchar(255)
declare @dirname nvarchar(255)
declare @buf nvarchar(255)

-- SQL Server 7.0
if (charindex(N'7.00', @@version, 0) > 0)
begin
    exec @rc = master.dbo.xp_regread 
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
        N'ErrorLogFile',
         @errorlog_file OUTPUT,
        N'no_output'
end

-- SQL Server 2000 needs to use instance aware Registry read 
if (charindex(N'8.00', @@version, 0) > 0)
begin
    exec @rc = master.dbo.xp_instance_regread 
        N'HKEY_LOCAL_MACHINE',
        N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
        N'ErrorLogFile',
         @errorlog_file OUTPUT,
        N'no_output'
end

-- reverse the string to find the last slash '\'
select @buf = reverse(@errorlog_file)

-- determine posiktion of last slash, now first slash in reversed string
select @pos = len(@buf) - charindex(char(92), @buf, 0) + 1

-- extract the directory only part, part before the last slash
select @dirname = substring(@errorlog_file, 0,  @pos)

-- extract the filename, part after the last slash
select @filename = substring(@errorlog_file, @pos + 1, len(@errorlog_file) - @pos)

-- extract the filename with extension, part after dot in @filename
select @filename_no_ext  = substring(@filename, 0, charindex(N'.', @filename, 0))

-- change errorlog file to version X
if (@fileversion is not null)
    select @errorlog_file = @dirname +N'\' +  @filename_no_ext + N'.' + convert(nchar, @fileversion)

-- read file
exec master.dbo.xp_readerrorlog 1, @errorlog_file
go

-- sample usage

-- read currently active log file, SQLAGENT.OUT
-- exec msdb.dbo.sp_sqlagent_read_errorlog

-- read version 1 of log, SQLAGENT.1
-- exec msdb.dbo.sp_sqlagent_read_errorlog 1