How to Efficiently Document SQL Server Agent Jobs

Question

My team is pretty much all new and we inherited several automated jobs which we have little or no documentation on (nothing in the job description either). We have hundreds of automated jobs that run on the SQL Server Agent and various servers so I wanted to start documenting some basic information (When are these jobs scheduled?, How long do they usually take?, How often do they fail?, Is the length of execution time static or growing? If it is an SSIS package, where is it located and what does it do?, If it is a report, can it be migrated to SSRS scheduler?).

We have a college in the area that has asked us if we could utilize any interns. Although we have plenty of work, we are not able to grant access to sensitive data to nonemployees. I was considering restoring the database to another server and truncating the sensitive data tables leaving the system databases in tact for purposes of documenting the scheduled tasks. I am not sure this is a good idea since I might end up spending as much time “securing” the data as the actual task would take to complete. Does anyone have any creative solutions to this type of issue?

Maybe something like:

http://bradsruminations.blogspot.com/2011/04/documenting-your-sql-agent-jobs.html http://www.elsasoft.org/forum/topic.asp?TOPIC_ID=164

I would like to hear any feedback that others who have already overcome these types of challenges. I appreciate your help!!!

Answer 1

I have never used this tool myself, but I have heard some good things about Dataedo. https://dataedo.com/

Answer 2

All of this information is stored in tables by MSSQL you can query these system tables to retrieve the information The following applies to SQL 2012 onwards and you can use the list tables below to query jobs, schedules etc. Now MSDN is going to be your friend here.

• dbo.sysalerts: Contains one row for each alert.
• dbo.syscategories: Contains the categories used by SQL Server Management Studio to organize jobs, alerts, and operators.
• dbo.sysdownloadlist: Holds the queue of download instructions for all target servers.
• dbo.sysjobactivity: Contains information about current SQL Server Agent job activity and status.
• dbo.sysjobhistory: Contains information about the execution of scheduled jobs by SQL Server Agent.
• dbo.sysjobs: Stores the information for each scheduled job to be executed by SQL Server Agent.
• dbo.sysjobschedules: Contains schedule information for jobs to be executed by SQL Server Agent
• dbo.sysjobservers: Stores the association or relationship of a particular job with one or more target servers.
• dbo.sysjobsteps: Contains the information for each step in a job to be executed by SQL Server Agent.
• dbo.sysjobstepslogs: Contains information about job step logs.
• dbo.sysnotifications: Contains one row for each notification.
• dbo.sysoperators: Contains one row for each SQL Server Agent operator.
• dbo.sysproxies: Contains information about SQL Server Agent proxy accounts.
• dbo.sysproxylogin: Records which SQL Server logins are associated with each SQL Server Agent proxy account.
• dbo.sysproxysubsystem: Records which SQL Server Agent subsystem is used by each proxy account.
• dbo.sysschedules: Contains information about SQL Server Agent job schedules.
• dbo.syssessions: Contains the SQL Server Agent start date for each SQL Server Agent session. A session is created each time the SQL Server Agent service starts.
• dbo.syssubsystems: Contains information about all available SQL Server Agent proxy subsystems.
• dbo.systargetservergroupmembers: Records which target servers are currently enlisted in this multiserver group.
• dbo.systargetservergroups: Records which target server groups are currently enlisted in this multiserver environment.
• dbo.systargetservers: Records which target servers are currently enlisted in this multiserver operation domain.
• dbo.systaskids: Contains a mapping of tasks created in earlier versions of SQL Server to Management Studio jobs in the current version.

BIG CAVEAT: This wont tell you what a jobs does if that job runs a SQL script. You will need to look at the jobs decypher the scripts and then add the description later. This is why you are diligent and pay attention to detail the first time around.

Answer 3

I took help of below blogs to come up with the script for documenting job information. I made few modifications and I am applying filter to get details about a specific job.

https://mikesdatawork.wordpress.com/2015/06/30/convert-last_run_date-and-last_run_time-in-sysjobsteps/#comment-2720

http://bradsruminations.blogspot.com/2011/04/documenting-your-sql-agent-jobs.html

DECLARE @job_name sysname = 'YourJobNameHere'
SELECT 'Job Detailed Information' AS TypeofData
;WITH job_details AS
(
SELECT j.name
, j.job_id
, jb.step_id
, jb.step_name
,jb.subsystem
, jb.command
,jb.additional_parameters 
--, 'last run literal' = dateadd(millisecond, jb.last_run_time,convert(datetime,cast(nullif(jb.last_run_date,0) as nvarchar(10))))
, 'last run day' = datename(dw, dateadd(millisecond, jb.last_run_time,convert(datetime,cast(nullif(jb.last_run_date,0) as nvarchar(10)))))
, 'last run date' = convert(char, dateadd(millisecond, jb.last_run_time,convert(datetime,cast(nullif(jb.last_run_date,0) as nvarchar(10)))), 9)
FROM dbo.sysjobs as j 
INNER JOIN dbo.sysjobsteps as jb
ON j.job_id = jb.job_id
WHERE j.name = @job_name
)
SELECT * FROM job_details order by step_id ;
SELECT 'Schedule Detailed Information' AS TypeofData;
WITH job_schedules AS
(
select schedule_id
      ,sysschedules.name as schedule_name
      ,SchedDesc=TimeOfDay+Frequency+EffDtRange   
from dbo.sysschedules 
cross apply 
  --Translate the dates and times into DATETIME values
  --And translate the times into HH:MM:SSam (or HH:MMam) strings
  (select StDate=convert(datetime
                        ,convert(varchar(8),active_start_date))
         ,EnDate=convert(datetime
                        ,convert(varchar(8),active_end_date))
         ,StTime=convert(datetime
                        ,stuff(stuff(right(1000000+active_start_time
                                          ,6)
                                    ,3,0,N':')
                              ,6,0,N':'))
         ,EnTime=convert(datetime
                        ,stuff(stuff(right(1000000+active_end_time
                                          ,6)
                                    ,3,0,N':')
                              ,6,0,N':'))
  ) F_DtTm
cross apply
  --Translate the times into appropriate HH:MM:SSam or HH:MMam char formats
  (select replace(replace(replace(substring(lower(convert(varchar(30),StTime,109))
                                           ,13,14)
                                 ,N':000',N'')
                         ,N':00a',N'a')
                 ,N':00p',N'p')
         ,replace(replace(replace(substring(lower(convert(varchar(30),EnTime,109))
                                           ,13,14)
                                 ,N':000',N'')
                         ,N':00a',N'a')
                 ,N':00p',N'p')
  ) F_Tms(StTimeString,EnTimeString)
cross apply 
  --What Time of Day? Single Time or Range of Times/Intervals
  (select case 
            when freq_subday_type=0
            then N''
            else case 
                   when freq_subday_type=1
                   then N'At '
                   else N'Every '
                       +convert(nvarchar(10),freq_subday_interval)
                       +' '
                       +case freq_subday_type
                          when 2 then N'Second'
                          when 4 then N'Minute'
                          when 8 then N'Hour'
                        end
                       +case 
                          when freq_subday_interval=1 then N'' else N's' end
                       +N' From '
                 end
                +StTimeString
                +case
                   when freq_subday_type=1
                   then N''
                   else N' to '+EnTimeString
                 end
                +N' '
          end
  ) F_Tm(TimeOfDay)
cross apply
  --Translate Frequency  
  (select case freq_type
            when 1
            then N'One Time Only'
            when 4
            then N'Every '
                +case freq_interval 
                   when 1
                   then N'Day'
                   else convert(nvarchar(10),freq_interval)+N' Days'
                 end
            when 8
            then N'Every '
                +case freq_recurrence_factor
                   when 1
                   then N''
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Weeks on '
                 end
                +stuff(case when freq_interval& 1<>0 then N', Sunday' else N'' end
                      +case when freq_interval& 2<>0 then N', Monday' else N'' end
                      +case when freq_interval& 4<>0 then N', Tuesday' else N'' end
                      +case when freq_interval& 8<>0 then N', Wednesday' else N'' end
                      +case when freq_interval&16<>0 then N', Thursday' else N'' end
                      +case when freq_interval&32<>0 then N', Friday' else N'' end
                      +case when freq_interval&64<>0 then N', Saturday' else N'' end
                      ,1,2,N'')
            when 16
            then N'Every '
                +case freq_recurrence_factor 
                   when 1
                   then N'Month '
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Months '
                 end
                +N'on the '
                +convert(nvarchar(10),freq_interval)
                +case 
                   when freq_interval in (1,21,31)
                   then N'st'
                   when freq_interval in (2,22)
                   then N'nd'
                   when freq_interval in (3,23)
                   then N'rd'
                   else N'th'
                 end
                +N' of the Month'
            when 32
            then N'Every '
                +case freq_recurrence_factor 
                   when 1
                   then N'Month '
                   else convert(nvarchar(10),freq_recurrence_factor)+N' Months '
                 end
                +N'on the '
                +case freq_relative_interval 
                   when  1 then N'1st '
                   when  2 then N'2nd '
                   when  4 then N'3rd '
                   when  8 then N'4th '
                   when 16 then N'Last '
                 end
                +case freq_interval 
                   when  1 then N'Sunday'
                   when  2 then N'Monday'
                   when  3 then N'Tuesday'
                   when  4 then N'Wednesday'
                   when  5 then N'Thursday'
                   when  6 then N'Friday'
                   when  7 then N'Saturday'
                   when  8 then N'Day'
                   when  9 then N'Weekday'
                   when 10 then N'Weekend Day'
                 end
                +N' of the Month'
            when 64
            then N'When SQL Server Agent Starts'
            when 128
            then N'Whenever the CPUs become Idle'
            else N'Unknown'
          end
  ) F_Frq(Frequency)
cross apply
  --When is it effective?
  (select N' (Effective '+convert(nvarchar(11),StDate,100)
         +case  
            when EnDate='99991231'
            then N''
            else N' thru '+convert(nvarchar(11),EnDate,100)
          end
         +N')'           
  ) F_Eff(EffDtRange)
WHERE schedule_id IN (SELECT s.schedule_id 
FROM dbo.sysjobschedules as s
INNER JOIN dbo.sysjobs as j 
on j.job_id = s.job_id 
WHERE j.name = @job_name)
)
SELECT * FROM job_schedules;