阿仁's Blog

匯出 SQL Server Job Log (1/2)

| Comments

正常在 SQL Server 的 Job 中,可以點選 View History 的功能,來查看 Job 執行的 Log。
系統預設的 Log 儲存規則可以到 SQL Server Agent 中,按右鍵查看。
系統預設值是,job history size 最大為 1000 Rows,每個 Job 最多為 100 Rows

SELECT j.name JobName, h.step_name StepName, 
CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate,
STUFF(STUFF(RIGHT(''000000'' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,':'),3,0,':') RunTime,
h.run_duration StepDuration,
case h.run_status when 0 then 'failed'
when 1 then 'Succeded'
when 2 then 'Retry'
when 3 then 'Cancelled'
when 4 then 'In Progress'
end as ExecutionStatus,
h.message MessageGenerated
FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j
ON j.job_id = h.job_id
ORDER BY j.name,  h.run_date,  h.run_time

用上面這段 SQL 語法就可以 Query 出目前 SQL Server 中所有的 Job Log.

再來我們需要把這些資料匯出成 .csv 檔案存儲起來。
這裡需要用到 BCP 來達成。

PS: 如果你的 BCP 指令還沒開啟,可以用下面這個指令開啟

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

開啟之後就可以匯出成檔案拉!
來看看完整的 Code

-- BCP - Export query
DECLARE @sql_str VARCHAR(1000) = 
    'SELECT j.name JobName, h.step_name StepName, '+
    'CONVERT(CHAR(10), CAST(STR(h.run_date,8, 0) AS dateTIME), 111) RunDate, '+
    'STUFF(STUFF(RIGHT(''000000'' + CAST ( h.run_time AS VARCHAR(6 ) ) ,6),5,0,'':''),3,0,'':'') RunTime, '+
    'h.run_duration StepDuration, '+
    'case h.run_status when 0 then ''failed'' '+
    'when 1 then ''Succeded'' '+
    'when 2 then ''Retry'' '+ 
    'when 3 then ''Cancelled'' '+
    'when 4 then ''In Progress'' '+
    'end as ExecutionStatus, '+
    'h.message MessageGenerated '+
    'FROM msdb.dbo.sysjobhistory h inner join msdb.dbo.sysjobs j '+
    'ON j.job_id = h.job_id '+
    'ORDER BY j.name,  h.run_date,  h.run_time';


DECLARE @bcp_cmd4 VARCHAR(1000);
DECLARE @file_name VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), GETDATE(), 112)+'.csv';


--export today SQL Job log
SET @bcp_cmd4 =  
    ' cd C:\Program Files\Microsoft SQL Server\110\Tools\Binn\ & ' + 
    ' BCP.EXE "'+@sql_str+'" queryout ' +
    ' "D:\SQL Job Log\'+@file_name+'" -T -c -q -t, -r\n';
EXEC master..XP_CMDSHELL @bcp_cmd4;

主要就是利用 XP_CMDSHELL 搭配 BCP 來做到 Query 後匯出。

PS: 需注意的是,盡量不要把檔案存到 user 桌面上,容易有權限上的問題,導致匯出不成功。重點是,還沒有錯誤訊息...

Reference

https://docs.microsoft.com/en-us/sql/ssms/agent/set-up-the-job-history-log?view=sql-server-2017
https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/
https://docs.microsoft.com/zh-tw/sql/tools/bcp-utility?view=sql-server-2017#C
https://stackoverflow.com/questions/18619061/unable-to-open-bcp-host-data-file

Comments

comments powered by Disqus