阿仁's Blog

匯出 SQL Server Job Log (2/2)

| Comments

在前一篇 匯出 SQL Server Job Log (1/2) 提到透過用 job history size 搭配匯出成 .csv 檔案的功能,來達到 SQL Server Job Log 維運的功能。

後來想想,這種用 Rows Size 的方式來控制 Job Log 似乎並非最佳解。
因為要是 Job 有新增或調整,可能 Rows Size 就必須跟著變動。
好像還是必須要透過自寫 T-SQL 來達成客製化需求。

所以,第一步,先大膽的將你的 SQL Server Job Log Rows Size 限制移除吧!(結界解放~)
再來直接看 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 @output INT;
DECLARE @file_name VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), GETDATE(), 112)+'.csv';
DECLARE @del_file VARCHAR(24) = 'SQL_Job_log_'+CONVERT(varchar(100), DATEADD(DAY, -14, 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;


--delete file 2 weeks ago
SET @bcp_cmd4 = 'DIR "D:\SQL Job Log\'+@del_file+'" /B';
EXEC @output = master..XP_CMDSHELL @bcp_cmd4, NO_OUTPUT
IF @output = 0
    SET @bcp_cmd4 = 'del "D:\SQL Job Log\'+@del_file+'"';
    EXEC master..XP_CMDSHELL @bcp_cmd4

-- delete job history older than yesterday
DECLARE @Target_date datetime = CONVERT(date,DATEADD(DAY,0,GETDATE()))
EXEC msdb.dbo.sp_purge_jobhistory @oldest_date=@Target_date

程式直接將 Log 檔儲存在 D:\SQL Job Log\ *.csv,
並 Rota 2 Weeks。

主要的重點在最後一段,
這邊呼叫了 SQL Server 內建的 stored procedure: msdb.dbo.sp_purge_jobhistory
這個 Procedure 主要就是用來刪 Job Log 的。
他有一個參數 @oldest_date 這個就是用來控制要刪除多久之後的 Log.

PS: 注意 @oldest_date 這個是 datetime 格式。

Reference

https://docs.microsoft.com/zh-tw/sql/relational-databases/system-stored-procedures/sp-purge-jobhistory-transact-sql?view=sql-server-2017

Comments

comments powered by Disqus