Sql-Server

SQL Server 2012 作業歷史記錄失去

  • January 11, 2021

我有一個帶有許多代理作業的 SQL Server 2012 數據庫。作業執行沒有問題，但作業歷史記錄始終為空。為什麼？

我還注意到手動執行作業（右鍵點擊和“在步驟開始作業…”）將執行作業，但顯示作業進度的視窗永遠不會關閉。就好像工作步驟完成了，但工作本身從未完成。

所有任務都以系統管理員身份執行，因此權限應該不是問題。

通過執行作業sp_start_job會導致作業完成但仍然沒有作業歷史記錄。

我已經完成了所有通常的檢查/更改jobhistory_max_rows_per_job等。

雖然這只解決了您問題的一小部分（您可能已經知道）：

當您右鍵點擊作業並選擇“在步驟…開始作業”時，打開的視窗不會在作業完成後自動關閉。您必須手動關閉視窗（可以在作業完成之前或之後完成）。

一旦視窗中的“執行作業”行具有狀態（無論是成功還是錯誤），（通常）應該在 sysjobhistory 中有一個條目，即使視窗沒有關閉（剛剛在我的系統中確認）。

您可以使用以下查詢來獲取有關如何配置 SQL Server（包括 SQL Server 代理）的所有詳細資訊，這可能會向您顯示配置不正確的位置：

DECLARE @MasterPath nvarchar(512);
DECLARE @LogPath nvarchar(512);
DECLARE @ErrorLog nvarchar(512);
DECLARE @ErrorLogPath nvarchar(512);

SELECT @MasterPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) 
FROM master.sys.database_files 
WHERE name=N'master';

SELECT @LogPath=substring(physical_name, 1, len(physical_name) - charindex('\', reverse(physical_name))) 
FROM master.sys.database_files 
WHERE name=N'mastlog';

SELECT @ErrorLog=cast(SERVERPROPERTY(N'errorlogfilename') as nvarchar(512));

SELECT @ErrorLogPath=substring(@ErrorLog, 1, len(@ErrorLog) - charindex('\', reverse(@ErrorLog)));

DECLARE @SmoRoot nvarchar(512);
EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
   , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
   , N'SQLPath'
   , @SmoRoot OUTPUT;

IF OBJECT_ID('tempdb..#tmp_sp_get_sqlagent_properties') IS NOT NULL
DROP TABLE #tmp_sp_get_sqlagent_properties;
CREATE TABLE #tmp_sp_get_sqlagent_properties
(
   auto_start int null
   , msx_server_name sysname null
   , sqlagent_type int null
   , startup_account nvarchar(255) null
   , sqlserver_restart int null
   , jobhistory_max_rows int null
   , jobhistory_max_rows_per_job int null
   , errorlog_file nvarchar(255) null
   , errorlogging_level int null
   , error_recipient nvarchar(30) null
   , monitor_autostart int null
   , local_host_server sysname null
   , job_shutdown_timeout int null
   , cmdexec_account varbinary(64) null
   , regular_connections int null
   , host_login_name sysname null
   , host_login_password varbinary(512) null
   , login_timeout int null
   , idle_cpu_percent int null
   , idle_cpu_duration int null
   , oem_errorlog int null
   , sysadmin_only int null
   , email_profile nvarchar(64) null
   , email_save_in_sent_folder int null
   , cpu_poller_enabled int null
   , replace_alert_tokens_enabled int null
);

INSERT INTO #tmp_sp_get_sqlagent_properties
(
   auto_start
   , msx_server_name
   , sqlagent_type
   , startup_account
   , sqlserver_restart
   , jobhistory_max_rows
   , jobhistory_max_rows_per_job
   , errorlog_file
   , errorlogging_level
   , error_recipient
   , monitor_autostart
   , local_host_server
   , job_shutdown_timeout
   , cmdexec_account
   , regular_connections
   , host_login_name
   , host_login_password
   , login_timeout
   , idle_cpu_percent
   , idle_cpu_duration
   , oem_errorlog
   , sysadmin_only
   , email_profile
   , email_save_in_sent_folder
   , cpu_poller_enabled
   , replace_alert_tokens_enabled
)
EXEC msdb.dbo.sp_get_sqlagent_properties;

DECLARE @DatabaseMailProfile nvarchar(255);
DECLARE @AgentMailType int;
DECLARE @ServiceStartMode int;
DECLARE @ServiceAccount nvarchar(512);
DECLARE @AgtGroup nvarchar(512);

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
   , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
   , N'DatabaseMailProfile'
   , @param = @DatabaseMailProfile OUT
   , @no_output = N'no_output';

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
   , N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'
   , N'UseDatabaseMail'
   , @param = @AgentMailType OUT
   , @no_output = N'no_output';

EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
   , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
   , N'Start'
   , @ServiceStartMode OUTPUT;

EXEC master.sys.xp_instance_regread 'HKEY_LOCAL_MACHINE'
   , 'SYSTEM\CurrentControlSet\Services\SQLSERVERAGENT'
   , N'ObjectName'
   , @ServiceAccount OUTPUT;

EXEC master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE'
   , N'SOFTWARE\Microsoft\MSSQLServer\Setup'
   , N'AGTGroup'
   , @AgtGroup OUTPUT;

SELECT Items.*
FROM #tmp_sp_get_sqlagent_properties AS tsgsp
CROSS APPLY (VALUES 
    ('Name', CAST(serverproperty(N'ServerName') AS sql_variant))
   , ('MsxServerName', ISNULL(tsgsp.msx_server_name,N''))
   , ('JobServerType', CONVERT(sql_variant, tsgsp.sqlagent_type))
   , ('SqlServerRestart', CONVERT(sql_variant, tsgsp.sqlserver_restart))
   , ('SqlAgentRestart', CONVERT(sql_variant, tsgsp.monitor_autostart))
   , ('MaximumHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows))
   , ('MaximumJobHistoryRows', CONVERT(sql_variant, tsgsp.jobhistory_max_rows_per_job))
   , ('ErrorLogFile', CONVERT(sql_variant, tsgsp.errorlog_file))
   , ('AgentLogLevel', CONVERT(sql_variant, tsgsp.errorlogging_level))
   , ('NetSendRecipient', CONVERT(sql_variant, ISNULL(tsgsp.error_recipient,N'')))
   , ('AgentShutdownWaitTime', CONVERT(sql_variant, tsgsp.job_shutdown_timeout))
   , ('SqlAgentMailProfile', CONVERT(sql_variant, ISNULL(tsgsp.email_profile,N'')))
   , ('SaveInSentFolder', CONVERT(sql_variant, CAST(tsgsp.email_save_in_sent_folder AS bit)))
   , ('WriteOemErrorLog', CONVERT(sql_variant, CAST(tsgsp.oem_errorlog AS bit)))
   , ('IsCpuPollingEnabled', CONVERT(sql_variant, CAST(tsgsp.cpu_poller_enabled AS bit)))
   , ('IdleCpuPercentage', CONVERT(sql_variant, tsgsp.idle_cpu_percent))
   , ('IdleCpuDuration', CONVERT(sql_variant, tsgsp.idle_cpu_duration))
   , ('LoginTimeout', CONVERT(sql_variant, tsgsp.login_timeout))
   , ('HostLoginName', CONVERT(sql_variant, ISNULL(tsgsp.host_login_name,N'')))
   , ('LocalHostAlias', CONVERT(sql_variant, ISNULL(tsgsp.local_host_server,N'')))
   , ('SqlAgentAutoStart', CONVERT(sql_variant, tsgsp.auto_start))
   , ('ReplaceAlertTokensEnabled', CONVERT(sql_variant, tsgsp.replace_alert_tokens_enabled))
   , ('DatabaseMailProfile', CONVERT(sql_variant, ISNULL(@DatabaseMailProfile,N'')))
   , ('AgentMailType', CONVERT(sql_variant, ISNULL(@AgentMailType, 0)))
   , ('SysAdminOnly', CONVERT(sql_variant, 1))
   , ('ServiceStartMode', CONVERT(sql_variant, @ServiceStartMode))
   , ('ServiceAccount', CONVERT(sql_variant, ISNULL(@ServiceAccount,N'')))
   , ('AgentDomainGroup', CONVERT(sql_variant, ISNULL(suser_sname(sid_binary(ISNULL(@AgtGroup,N''))),N'')))
   )Items(ItemName, ItemValue)
UNION ALL
SELECT Items.* 
FROM (VALUES 
     ('IsCaseSensitive', CAST(case when 'a' <> 'A' then 1 else 0 end AS bit))
   , ('MaxPrecision', @@MAX_PRECISION)
   , ('ErrorLogPath', @ErrorLogPath)
   , ('RootDirectory', @SmoRoot)
   , ('IsFullTextInstalled', CAST(FULLTEXTSERVICEPROPERTY('IsFullTextInstalled') AS bit))
   , ('MasterDBLogPath', @LogPath)
   , ('MasterDBPath', @MasterPath)
   , ('VersionString', SERVERPROPERTY(N'ProductVersion'))
   , ('Edition', CAST(SERVERPROPERTY(N'Edition') AS sysname))
   , ('ProductLevel', CAST(SERVERPROPERTY(N'ProductLevel') AS sysname))
   , ('IsSingleUser', CAST(SERVERPROPERTY('IsSingleUser') AS bit))
   , ('EngineEdition', CAST(SERVERPROPERTY('EngineEdition') AS int))
   , ('Collation', convert(sysname, serverproperty(N'collation')))
   , ('NetName', CAST(SERVERPROPERTY(N'MachineName') AS sysname))
   , ('IsClustered', CAST(SERVERPROPERTY('IsClustered') AS bit))
   , ('ResourceVersionString', SERVERPROPERTY(N'ResourceVersion'))
   , ('ResourceLastUpdateDateTime', SERVERPROPERTY(N'ResourceLastUpdateDateTime'))
   , ('CollationID', SERVERPROPERTY(N'CollationID'))
   , ('ComparisonStyle', SERVERPROPERTY(N'ComparisonStyle'))
   , ('SqlCharSet', SERVERPROPERTY(N'SqlCharSet'))
   , ('SqlCharSetName', SERVERPROPERTY(N'SqlCharSetName'))
   , ('SqlSortOrder', SERVERPROPERTY(N'SqlSortOrder'))
   , ('SqlSortOrderName', SERVERPROPERTY(N'SqlSortOrderName'))
   , ('BuildClrVersionString', SERVERPROPERTY(N'BuildClrVersion'))
   , ('ComputerNamePhysicalNetBIOS', SERVERPROPERTY(N'ComputerNamePhysicalNetBIOS'))
   )Items(ItemName, ItemValue)
ORDER BY Items.ItemName;

對輸出特別感興趣的是：

MaximumHistoryRows

MaximumJobHistoryRows

上述項目應設置為足夠高的數字，以確保您看到所有作業的作業歷史記錄。例如，如果將 MaximumHistoryRows 設置為 10，但您有 50 個作業，您將只能看到最近執行的 50個作業的歷史記錄。我意識到您在問題中提到您已經檢查了這些數字 - 我只想指出，可以以嚴重限制您看到的歷史記錄的方式配置這些值msdb.dbo.sysjobhistory。

引用自：https://dba.stackexchange.com/questions/163889

相關問答

Sql-Server

安排每周代理作業的下次執行日期

  • January 4, 2022
檢視問答
Sql-Server

授予 AD 組編輯所有 MSSQL 代理作業的權限

  • July 25, 2020
檢視問答
Sql-Server

SQL作業忽略IF條件

  • August 30, 2018
檢視問答
Sql-Server

一次性將 SQL 代理輸出添加到所有代理作業的文本文件

  • April 4, 2017
檢視問答
Sql-Server

在 SQL Server 代理中執行 Powershell 腳本時出錯

  • April 21, 2016
檢視問答
Sql-Server

sql server 作業步驟的輸出考慮了什麼？

  • April 14, 2016
檢視問答