2

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.

  • Can you check the msdb.dbo.sysjobhistory table and see if there are any entries there? Also check the retention of your job execution history – Stijn Wynants Feb 10 '17 at 14:55
  • As I said I've done all the usual checking of jobhistory settings. sysjobhistory contains zero records. It did contain some records until I started messing with the jobhisotory settings - at one stage I turned off recording jobhistory and this emptied sysjobhistory table. I turned it back on, restarted Agent, restared the server etc. Nothing written to that table since, despite multiple jobs actually running and doing what they are expected to do. – cymorg Feb 10 '17 at 15:01
  • In my case the Job History was missing because it was only a job that executed a Maintenance Plan - the information was in the Maintenance Plan history. – Slogmeister Extraordinaire Oct 31 at 14:01
1

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).

  • BTW - just in case you haven't done this, I'd go back in and double-check to make sure that your change to turn job history back on worked, just in case that didn't work for some reason. – RDFozz Feb 14 '17 at 16:49
  • See this link for more detail - too long to post here. – cymorg Feb 15 '17 at 9:54
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.

  • Max - thanks for the nice script - I can certainly see a lot more about the Agent now but unfortunately I don't see anything that explains the behaviour I'm experiencing. I posted another question on the same issue which includes some more log details. – cymorg Feb 17 '17 at 12:08
0

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.

  • The job is set to "On Success" --> "Quit the Job Indicating Success". The job in question is a simple db restore which is definitely completing as the database is restored. The job history that is still not being updated. – cymorg Feb 14 '17 at 15:51
0

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:

SQL Server - Job Step - Advanced Options

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:

SQL Server Agent Properties - SERVERNAME

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.

0

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)

  • I didn't find this answer myself so a big shout out to my colleague KD for finally getting to the bottom of this long running problem. – cymorg Oct 31 at 16:50
-1

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

  • I never resolved this. The problem persists to this day. – cymorg Jul 18 '18 at 10:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.