SQL Server 2012 Job History Missing

Question

I have a SQL Server 2012 database with a number of Agent jobs. The jobs run without a problem but the job history is always empty. Why?

I also note that running a job manually (right click and "Start Job at Step...") will run the job but the window showing progress of the job never closes. It is as if the job steps complete but the job itself never completes.

All tasks have been executed as a sysadmin so permissions should not be an issue.

Running the jobs via sp_start_job results in the jobs completing but still no job history.

I've done all the usual checking/changing of jobhistory_max_rows_per_job etc.

Answer 1

You could use the following query to obtain all the details about how SQL Server, including SQL Server Agent, is configured, which might show you where something is configured incorrectly:

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;

Of particular interest in the output would be:

MaximumHistoryRows
MaximumJobHistoryRows

The above items should be set to a number high enough to ensure you see job history for all jobs. If, for instance, MaximumHistoryRows is set to 10, but you have 50 jobs, you'll only see history for the 50 most recently ran jobs. I realize you mention in your question that you've checked these numbers already - I just want to note that it's possible to configure those values in such a way as to severely limit the history you see in msdb.dbo.sysjobhistory.

Answer 2

While this only addresses a small part of your question (and you may already be aware):

When you right-click on a job and choose "Start Job at step...", the window that opens does not close automatically when the job is finished. You have to manually close the window (which can be done before or after the job completes).

Once the "Execute job" line in the window has a status (whether success or error), there should (normally) be an entry in sysjobhistory, even if the window hasn't closed (just confirmed in my system).

Answer 3

Check the "On Success" for the last step of your job. It should say "Quit the Job Indicating Success"

It won't close the window you used to start running the job, but it will commit the job history once all job steps are complete.

If the On Success is already correct it seems likely that one of the steps never completes, but with newer versions of SSMS you should see all completed steps regardless of whether the entire job completes.

Answer 4

Job Step - Advanced Options

^{If you are only missing the job steps in the history, then this solution might be for you.}

When you create/modify a job step, you have an Advanced tab/page that can be configured. Here is a screen shot of what the advanced options look like:

You might have noticed the option Include step output in history. (Well you will have by now.) This option determines if the job step execution is logged in the job history table as described here:

Include step output in history
Select this option to include output from the job step in the job history.

^{Reference: Job Step Properties - New Job Step (Advanced Page) (Microsoft Docs)}

SQL Server Agent Properties - SERVERNAME

If you open up the SQL Server Agent Properties window you can select the History tab/page and are presented with the following window:

Having the wrong values here, can delete the job history faster than you can blink an eye. The default values are as above in the screenshot. Consider entering the default values and try and see if you job starts logging job history again. You will have to stop and start the SQL Server Agent Service to ensure all changes take effect.

Answer 5

The issue was due to the connection to SQL Server being made over the loopback interface. There is a loopback check being performed which causes trusted connection via the loopback adapter to fail.

The solution is to disable the loopback check by adding an entry to the registry as per the article below. https://stackoverflow.com/questions/1538027/sspi-handshake-failed-with-error-code-0x8009030c-while-establishing-a-connection/18053759 (See loopback answer 28/07/2011)

Answer 6

I had a similar issue - the job worked, but no history was recorded

My job has six steps - Looking at John K N's comment above made me look at the Advanced tab and the box Include step output in history

Step 1 - the box was ticked

Steps 2-6 - the box was NOT ticked

So, I went through all the steps ticking that box

Next morning - I had History!

Being an awkward sort of cuss, I went through all six steps unticking

Next morning - I had History!

I ticked the box for Step 1, leaving the others unticked (so that it was back to the original state

Next morning - I had History!

So I suspect that the job had somehow fallen into Schrödinger's History - neither wanted nor unwanted.

Try changing something, maybe even just the text - it saves it and then works fine

Answer 7

I've got the same problem. Since June 16th 2018 there are no protocol entries for my agent jobs anymore. I checked already with your script and it looks fine for me. So, I have no idea what can be the reason. It might be there is a mistake in the SQL server itself, but how can I detect it. I have created a test agent job with an simple SQL statement, also here no protocol. When I check the error protocols then the protocal says that it was written in db sysjobhistory, but nothing happened. So, has someone an idea to solve the problem? Thanks a lot. Cheers Klaus