run_duration to seconds #sqlserver sysjobhistory

Sample code showing how to convert the run_duration column from msdb.dbo.sysjobhistory to seconds.


select
j.job_id
,j.name as job_name
, s.step_name
, s.step_id
-- , s.command
,dbo.agent_datetime(run_date, 0) as step_start_date
,dbo.agent_datetime(run_date, run_time) as step_start_date_time
,h.instance_id
,h.run_duration
,run_duration/10000 as run_duration_hours_only --hours
,run_duration/100%100 as run_duration_minutes_only --minutes
,run_duration%100 as run_duration_seconds_only --seconds
,(run_duration/10000 * 60 * 60) + -- hours as seconds
(run_duration/100%100 * 60) + --minutes as seconds
(run_duration%100 ) as run_duration_total_seconds --seconds
-- , h.*
from msdb.dbo.sysjobs j inner join
msdb.dbo.sysjobsteps s on j.job_id = s.job_id inner join
msdb.dbo.sysjobhistory h on s.job_id = h.job_id and s.step_id = h.step_id
where
1=1
and j.name like 'syspolicy_purge_history'
order by h.instance_id desc

SSRS – Unable to determine if the owner of job has server access [SQLSTATE 42000] (Error 15404))

The job failed. Unable to determine if the owner (unresolveddomain\user) of job 316C6CFE-EB33-4C96-85D6-A593B2CD1C82 has server access (reason: Could not obtain information about Windows NT group/user ‘unresolveddomain\user’, error code 0x54b. [SQLSTATE 42000] (Error 15404)).

SSRS Subscriptions suddenly are not being delivered, and the above error message is found when checking SQL Agent.

When I check a report, it does not seem like the schedule even fires from SSRS, however when I check the SQL Server Agent, it seems several jobs are failing. To isolate the issue, I looked for commonalities in which user account owns the job.

Simply check to see if all the jobs which are failing are based on a single user account.

SELECT
job_id
,j.[name] AS [JobName]
,CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END as [Enabled]
,l.[name] AS 'OwnerName'
FROM
MSDB.dbo.sysjobs j INNER JOIN
Master.dbo.syslogins l ON j.owner_sid = l.sid
ORDER BY j.[name]

Subsequently, update each of these jobs with a user that has the required privilege, or which can be resolved by the system. Test on one job first. Be sure to write the requisite rollback script. In the event you attempted fix does not work, you don’t want to have another issue to investigate.

In this case, a development domain was re-built and this server no longer had access to the domain.

USE [msdb]
GO

DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_name NVARCHAR(128)
DECLARE @enabled NVARCHAR(128)
DECLARE @owner_name NVARCHAR(128)
DECLARE @sql NVARCHAR(4000)
DECLARE c_cursorJobs CURSOR
FOR
SELECT
job_id
,j.[name] AS [JobName]
,CASE WHEN j.Enabled = 0 THEN 'No' ELSE 'Yes' END as [Enabled]
,l.[name] AS 'OwnerName'
FROM
MSDB.dbo.sysjobs j INNER JOIN
Master.dbo.syslogins l ON j.owner_sid = l.sid
WHERE
l.[name] LIKE 'unresolveddomain\user'
ORDER BY j.[name]

FOR READ ONLY
OPEN c_cursorJobs
FETCH NEXT FROM c_cursorJobs INTO @job_id, @job_name,@enabled,@owner_name
WHILE(@@FETCH_STATUS=0)
BEGIN
--SET @sql = 'EXEC msdb.dbo.sp_update_job @job_id=N'''+ CAST(@job_id AS NVARCHAR(500)) + ''', @owner_login_name=N''domain\user'''
--EXEC sp_executesql @sql
--PRINT @sql

SET @sql = 'EXEC msdb.dbo.sp_update_job @job_name=N'''+ CAST(@job_name AS NVARCHAR(500)) + ''', @owner_login_name=N''domain\user'''
EXEC sp_executesql @sql
-- PRINT @sql

FETCH NEXT FROM c_cursorJobs INTO @job_id, @job_name,@enabled,@owner_name
END
CLOSE c_cursorJobs
DEALLOCATE c_cursorJobs

This can be implemented as a SQL Job on the server. However, if you run into this problem outside of your development environment, consider using some change management, or rebuilding your server 😛

How to: Disable or Enable a SQL Server Job Programatically

How to: Disable or Enable a SQL Server Job

Step 1: Find the JobId 

SELECT
job_id,name,description,enabled
FROM
msdb.dbo.sysjobs
ORDER BY
name ASC

Step 2: Update the enabled flag

— Disable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 0

— Enable SQL Job

DECLARE @my_job_id UNIQUEIDENTIFIER
SET @my_job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
EXEC sp_update_job @job_id = @my_job_id , @enabled = 1

 

Consider:

Create a cursor to loop through all SQL Jobs whenever you want to quickly enable/disable SQL Jobs

Consider creating a log of what jobs are enabled on a given day via bcp or SELECT INTO. This way you can store a log of what jobs are active on a given day, and roll back to that state when necessary.

 

DECLARE @sql NVARCHAR(4000)
DECLARE @cmd NVARCHAR(4000)
DECLARE @timestamp VARCHAR(50)
SELECT @timestamp = CAST(datediff(s,'1970-01-01',getutcdate()) AS VARCHAR)

SET @sql = 'SELECT job_id,name,description,enabled INTO dbo.sysjobs_'+ @timestamp + ' FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
PRINT @sql 

-- EXEC sp_executesql @sql 

 

 

SET @sql = 'SELECT job_id,name,description,enabled FROM msdb.dbo.sysjobs ORDER BY name ASC ' 
SET @cmd = 'bcp "'+@sql + '" out "C:\cya\sysjobs.'+@timestamp+'.dat" -n -S server_name -T'
PRINT @cmd

-- EXEC master..xp_cmdshell @cmd 

 

I had one specific job which I wanted to be able to enable/disable during testing. So i created a batch file that accepts 3 parameters

  • action
  • username
  • password

 
Create a batch file called myjobmanager.bat as follows:


REM ## EXPECTS PARAMETERS -- 1. ENABLE/DISABLE 2. Username 3. Password
osql -S servername -d msdb -i %1_myjob.sql -U %2 -P %3 -n >> %1_myjob.log

Create 2 script files

—- disable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- DISABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 0

—- enable_myjob.sql —-

DECLARE @job_id UNIQUEIDENTIFIER
SET @job_id = '1CB89951-9ED1-45F2-A5E8-A20D9164613F'
-- ENABLE JOB
EXEC sp_update_job @job_id = @job_id , @enabled = 1

To enable or disable the job run the script from the command line.

myjobmanager.bat enable username password

myjobmanager.bat disable username password

 

Of course you can then extend this to better suit your own uses by using SQLCMD and more substitution paramaters, such as dynamically putting server name, jobid, or job name….

 

For more ideas, go into your database and look into what other parameters you may want to change. Possibilities are endless. Change the name of the job, the owner, etc.

EXEC msdb.dbo.sp_helptext 'sp_update_job'