Querying SQL Server Agent Job History Data

Problem

Often times we are tasked with having to programmatically come with a list of durations per SQL Server Agent Job to trend the run times and order the results by date. Unfortunately Microsoft has not made that very easy in the way the data is stored in the system tables in the MSDB database. In this tip I will explain how to use the system tables to get the data into the correct format for dates and job durations.

Solution

SQL Server stores job information in the MSDB system tables. The table that stores historical job executions is msdb.dbo.sysjobhistory and the date/time information as well as the duration of the job execution is stored a little different than what you will see in most system tables. Let's dive into the tables to learn how to perform the calculations.

MSDB.dbo.sysjobhistory Table - run_date and run_time Columns

In the msdb.dbo.sysjobhistory table, the date (run_date column) and time (run_time column) are stored as two different columns and the columns are an INT data type as seen in the screenshot below.

If you were to query the sysjobhistory (joined to sysjobs of course) this is what the raw data would look like. Note run_date appears to be in YYYYMMDD format but the time is a little trickier. It is in format HHMMSS and not always 6 numbers long as you can see in this example the times are 3:30 AM represented by 33000 in the run_time column.

The result set in the picture above was generated by running the following T-SQL script:

select 
 j.name as 'JobName',
 run_date,
 run_time
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, run_date, run_time desc

This can be very confusing when trying to analyze these results and more importantly trying to order results by date and time, which as a common practice for DBA's. There are many ways to get the date/time into a true DATETIME format and I have seen some very creative scripts over the years. Starting in SQL Server 2005 there is an system scalar function located in MSDB that will convert this for you. I say it is undocumented as I could not find a BOL article for it. The function name is MSDB.dbo.agent_datetime(run_date, run_time) and will return a nicely formatted DATETIME column that is much easier to use programmatically. Below is the same query as the first one above with just an additional column that is a call to this function.

select 
 j.name as 'JobName',
 run_date,
 run_time,
 msdb.dbo.agent_datetime(run_date, run_time) as 'RunDateTime'
From msdb.dbo.sysjobs j 
INNER JOIN msdb.dbo.sysjobhistory h 
 ON j.job_id = h.job_id 
where j.enabled = 1  --Only Enabled Jobs
order by JobName, RunDateTime desc

Below are the results that this query produces and the nicely formatted DATETIME column at the end. This makes sorting easy as well as date range queries against your SQL Agent Jobs.

 

How to monitor backup and restore progress in SQL Server

Below script can give us a summary of current backups and restores which are happening on the server:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

SELECT r.session_id AS [Session_Id]     ,r.command AS [command]     ,CONVERT(NUMERIC(6, 2), r.percent_complete) AS [% Complete]     ,GETDATE() AS [Current Time]     ,CONVERT(VARCHAR(20), DATEADD(ms, r.estimated_completion_time, GetDate()), 20) AS [Estimated Completion Time]     ,CONVERT(NUMERIC(32, 2), r.total_elapsed_time / 1000.0 / 60.0) AS [Elapsed Min]     ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0) AS [Estimated Min]     ,CONVERT(NUMERIC(32, 2), r.estimated_completion_time / 1000.0 / 60.0 / 60.0) AS [Estimated Hours]     ,CONVERT(VARCHAR(1000), (             SELECT SUBSTRING(TEXT, r.statement_start_offset / 2, CASE                         WHEN r.statement_end_offset = - 1                             THEN 1000                         ELSE (r.statement_end_offset - r.statement_start_offset) / 2                         END) 'Statement text'             FROM sys.dm_exec_sql_text(sql_handle)             )) FROM sys.dm_exec_requests r WHERE command like 'RESTORE%' or  command like 'BACKUP%'

Source:

https://blog.sqlauthority.com/2017/03/12/get-status-running-backup-restore-sql-server-interview-question-week-113/

 

How to get a backup SQL database history

This article will review how to get information on your backup SQL database history, including the metadata in MSDB that can be queried, as well as value-added tools and features to group, sort, report, and export this critical information

It is essential for each organization to define the backup policy in its environment. Such policies help to recover the database with minimum loss of the data and minimum downtime. SQL Server maintains a backup history in the system database msdb. We might be taking different kinds of backups to the main minimum restoration time. In the case of any disaster, we must know exactly how to get the data from this internal table and prepare the restoration plan accordingly. In this article, we will understand the way to retrieve the database backup history to meet these critical business requirements.

Below are tables in the msdb database for the database backups.

Backupfile

This table contains the row for each data or log file of the database for which we executed the backup SQL database. In the following image, we can see essential columns of this table:

SELECT [filegroup_name]
      ,[backed_up_page_count]
      ,[file_type]
      ,[file_size]
      ,[logical_name]
      ,[physical_name]
  ,State
      ,[state_desc]
      ,[backup_size]
  ,[differential_base_lsn]
 FROM [msdb].[dbo].[backupfile] 

filegroup_name: It shows the filegroup name of the data or log file. We do not have any filegroup for the log file therefore; this column can contain NULL value.

backed_up_page_count: It shows the page count backed up.

file_type: We can get the file type using this field. It can have following values.

• D: SQL Server data file
• L: SQL Server log file
• F: Full-text catalog
• S: Memory optimised file

Logical_name: Logical name of the database file.

Physical_name: backup physical location.

State: It gives the state information of particular file. We can have following values for this.

• 0 = ONLINE
• 1 = RESTORING
• 2 = RECOVERING
• 3 = RECOVERY PENDING
• 4 = SUSPECT
• 6 = OFFLINE
• 7 = DEFUNCT
• 8 = DROPPED

backup_size: It gives the backup SQL database size in bytes.

differential_base_lsn: SQL Server takes a differential backup for the extents having LSN greater than differential_base_lsn.

backupmediafamily

In this table, we get the information about the logical and physical backup SQL database file name along with the device type in which backup was taken:

select logical_device_name ,physical_device_name,device_type from backupmediafamily

In the above screenshot, we can have the following device types:

• 2 = Disk
• 5 = Tape
• 7 = Virtual device
• 9 = Azure Storage
• 105 = A permanent backup SQL database device

Backupset

In this table, we can get information about the successful backup for each database:

select name,user_name,first_lsn,last_lsn,database_backup_lsn,backup_start_date,backup_finish_date,
  type,database_name,server_name,machine_name from backupset

• Name: backup SQL database set description
• User_name: We can get a user who executed this backup from this column
• Fist_lsn,last_lsn and database_backup_lsn: We get the backup LSN information from this column. It can help us to identify the backups after a particular LSN or preparing a restoration plan for a database
• Backup_start_date and backup_finish_date: It gives the backup start and ends time
• Server_name: It shows the instance name of the server
• Machine_name: we can get the name of the machine from which we took the backup
• Type: We can get the backup type using this column value. Here we have the following values

  D: Full database backup.

  L: log backup

  I: Differential database backup

We need to join these multiple tables to get the relevant backup information. For example, we can get the backup history from yesterday using the below query:

SELECT 
CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
msdb.dbo.backupset.database_name, 
msdb.dbo.backupset.backup_start_date, 
msdb.dbo.backupset.backup_finish_date, 
CASE msdb..backupset.type 
WHEN 'D' THEN 'Database' 
WHEN 'L' THEN 'Log' 
When 'I' THEN 'Differential database'
END AS backup_type, 
msdb.dbo.backupset.backup_size, 
msdb.dbo.backupmediafamily.physical_device_name, 
msdb.dbo.backupset.name AS backupset_name
FROM msdb.dbo.backupmediafamily 
INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id 
WHERE (CONVERT(datetime, msdb.dbo.backupset.backup_start_date, 102) >= GETDATE()-1 ) 
ORDER BY 
msdb.dbo.backupset.backup_finish_date desc

We might want to get a list of the most recent full backup of all the databases:

SELECT  
   CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS Server, 
   msdb.dbo.backupset.database_name,  
   MAX(msdb.dbo.backupset.backup_finish_date) AS last_db_backup_date 
FROM   msdb.dbo.backupmediafamily  
   INNER JOIN msdb.dbo.backupset ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id  
WHERE  msdb..backupset.type = 'D' 
GROUP BY 
   msdb.dbo.backupset.database_name

It is also equally important to identify if there are any databases for which we are not taking any backups. We can run the below query to get a list of such databases:

SELECT      
      S.NAME AS database_name,  
      'Nobackups' AS [Backup Age (Hours)]  
FROM 
   master.dbo.sysdatabases S LEFT JOIN msdb.dbo.backupset B
       ON S.name  = B.database_name 
WHERE B.database_name IS NULL AND S.name <> 'tempdb'
ORDER BY  
   B.database_name

In my test instance, you can see many databases having no database backups:

It does become difficult to get the backup information each time based on the requirement. We need to write the code and get the data accordingly. We might be having different kinds of environments having different database needs, and if we have a large database environment, it becomes difficult to get this data. We might report backup failures or backup reports to the management. In such a case, we need a central place from which we can get any backup information for any database in the instance.

In SQL Server, you can do it using the central management server with the combination of SSIS, SSRS tools however it requires a good level of knowledge of the development of the information using these code but again if requirement changes, we need to make changes at all places to reflect the change.

Source:

https://solutioncenter.apexsql.com/how-to-get-a-backup-sql-database-history/