SQL Server – msdb – log_shipping_monitor_history_detail

Background

Reviewing our databases and looking at their table sizes to find tables that we can prune.

Let us lock into the msdb database.

And, see what we find.

Metadata

Get Largest Tables

Code

Let us find the biggest tables in the msdb database.



use [msdb]
go
 
select
          [server]
            = serverproperty('servername')
 
        , [table]
            = 
                  quotename(tblSS.[name])
                + '.'
                + quotename(tblSO.[name])
 
        , [indexName]
            = tblSI.[name]
 
        , [indexID]
            = tblSI.index_id
 
        , [NumberofRecords]
            = tblSP.[rows]

from   sys.schemas tblSS

inner join  sys.objects tblSO

		on  tblSS.schema_id = tblSO.schema_id
 
inner join  sys.indexes tblSI
 
        on  tblSO.object_id = tblSI.object_id
 
inner join  sys.partitions tblSP
 
        on   tblSI.object_id = tblSP.object_id
        and  tblSI.index_id = tblSP.index_id

/*
	sys.objects.type
		Type = 'U'
*/ 
where  tblSO.[type] = 'U'

/*
	sys.indexes.index_id
		0 is Heap
		1 is Clustered
*/ 
and    tblSI.index_id in ( 0, 1)
 
order by
        tblSP.[rows] desc

Primary Server

census-primary-brushedup

Secondary Server

census-secondary-brushedup

 

Explanation

  1. Secondary Server
    • dbo.log_shipping_monitor_history_detail
      • Close to 2.5 million records

 

System Tables

dbo.log_shipping_monitor_history_detail – Aggregated ( All Databases )

On the Secondary Server, the biggest table is the msdb.dbo.log_shipping_monitor_history_detail table.

Code


use [msdb]
go

select 

			  [server]
				= serverproperty('servername')

			, [database_name]

			, [agent_id]

		    , [agent_type]

			--The type of log shipping job. 0 = Backup, 1 = Copy, 2 = Restore. agent_type is tinyint and cannot be NULL.
			, [agent_type_literal]
				= case([agent_type])

						when 0 then 'Backup'
						when 1 then 'Copy'
						when 2 then 'Restore'
				  end

			--, [log_time]
			--	= min([log_time])

			, [log_time_utc_min]
				= min([log_time_utc])

			, [log_time_utc_min_drift_min_InDays]
				= datediff(day, min([log_time_utc]), getutcdate())

			, [log_time_utc_min_drift_min_InMinutes]
				= datediff(minute, min([log_time_utc]), getutcdate())

			, [log_time_utc_max]
				= max([log_time_utc])

			, [count]
				= count(*)

from   [msdb].dbo.log_shipping_monitor_history_detail tblLSMHD

group by
		    [database_name]
		  , [agent_id]
		  , [agent_type]

order by
		    [database_name]
		  , [agent_type]
		


 

Output

log_shipping_monitor_history_detail-all-aggregated-brushedup

Explanation

  1. Database Name
    • Where Databasename is null, it means that the Database Id is no longer valid, for the databases have been dropped
    • Database csLogins with Type is equal to Restore is the biggest drag

 

dbo.log_shipping_monitor_history_detail – Aggregated – Database ( csLogins )

 

Let us narrow into the csLogins database.

Code




declare @database sysname

set @database = 'csLogins'

select 

			  [server]
				= serverproperty('servername')

			, [database_name]

			, [agent_id]

		    , [agent_type]

			--The type of log shipping job. 0 = Backup, 1 = Copy, 2 = Restore. agent_type is tinyint and cannot be NULL.
			, [agent_type_literal]
				= case([agent_type])

						when 0 then 'Backup'
						when 1 then 'Copy'
						when 2 then 'Restore'
				  end

			--, [log_time]
			--	= min([log_time])

			, [log_time_utc_min]
				= min([log_time_utc])

			, [log_time_utc_min_drift_min_InDays]
				= datediff(day, min([log_time_utc]), getutcdate())

			, [log_time_utc_min_drift_min_InMinutes]
				= datediff(minute, min([log_time_utc]), getutcdate())

			, [log_time_utc_max]
				= max([log_time_utc])

			, [count]
				= count(*)

from   dbo.log_shipping_monitor_history_detail tblLSMHD

where  [database_name] = isNull
							(
								  @database
								, tblLSMHD.[database_name]
							)

group by
		    [database_name]
		  , [agent_id]
		  , [agent_type]

order by
		    [database_name]
		  , [agent_type]
		


Output

log_shipping_monitor_history_detail

 

Explanation

  1. The Agent Type that has the most count has 2.3 million and Agent Type is Restore
  2. Agent Type = Restore
    • We have 2.4 million records and earliest date of 9/9/2016 is 4 days earlier than our current date of 9/13/2016
  3. Agent Type = Backup
    • It has been 231 days since our earliest entry
    • The 231 days likely reflect when we switched this server from Primary to Secondary

 

dbo.log_shipping_monitor_history_detail – Detail

Code



declare @database sysname
declare @agentType int

set @database = 'csLogins'
set @agentType = 2

/*
	Get Earliest data
*/
select top 10 *

from   [msdb].dbo.[log_shipping_monitor_history_detail] tblLSMHD

where  tblLSMHD.[database_name] = @database

and    tblLSMHD.[agent_type] = @agentType

order by 
		tblLSMHD.[log_time_utc] asc

/*
	Get Latest data
*/
select top 10 *

from   [msdb].dbo.[log_shipping_monitor_history_detail] tblLSMHD

where  tblLSMHD.[database_name] = @database

and    tblLSMHD.[agent_type] = @agentType

order by 
		tblLSMHD.[log_time_utc] desc

Output

details-20160913-1014am-brushed-up

 

Explanation

  1. A lot of our entries look like ” Skipped log backup file. Secondary DB: ‘csLogins’, File: ‘\\HRDB\SQLbackups\MSSQL\csLogins\csLogins_20160711165000.trn’
  2. The signature is “Skipped log backup file. Secondary DB…”

 

Read Log Shipping – Secondary – Configuration

Let us review our Log Shipping configuration and see if we get a bit more information as what we might have wrong…

Code


use master
go


/*

	Get Log Shipping Info
		Returns information regarding a secondary database from the monitor tables.
		https://msdn.microsoft.com/en-us/library/ms177639.aspx

*/

declare @agent_id   uniqueidentifier 

declare @AGENT_TYPE_BACKUP int
declare @AGENT_TYPE_RESTORE int

declare @agent_type tinyint

declare @serverName   sysname
declare @database sysname

declare @log varchar(600)

set @AGENT_TYPE_BACKUP = 0
set @AGENT_TYPE_RESTORE = 2

set @agent_type = @AGENT_TYPE_RESTORE

set @serverName = cast(serverproperty('servername') as sysname)
set @database = 'csLogins'

select
		 @agent_id = agent_id
from   [msdb].dbo.log_shipping_monitor_history_detail tblLSMHD
where  [database_name] = @database
and    [agent_type]    = @agent_type


if (@agent_id is null)
begin

	set @log = 'Invalid Log Shipping ' 
					+ ' - Database Name ' + isNull(@database, '')
					+ ' - Agent Type ' + isNull(cast(@agent_type as varchar(30)), '')

	raiserror(@log, 16, 1)					

end

begin tran

	/*
		Get Log Shipping Info from secondary
	*/	
	exec sp_help_log_shipping_monitor_secondary
			  @secondary_server = @serverName
			, @secondary_database = @database

rollback tran


Output

secondary-monitor

 

Explanation

  1. Last Restore Date
    • Our last restore date is 2016-07-11
  2. History Retention Period
    • The History Retention period is set at 5760
    • As that number is in minutes, it translates to ([Number]) / (24 * 60)  in days = 4 days

 

Manually Restore last full backup and transaction log backups taken thereafter

Our dated last restore date of 2016-07-11 is concerning to me, so let us quickly restore last night’s full backup and the subsequent transaction backup.

Code


/*
	Restore Database - Full
*/
restore filelistonly
from disk = '\\DBPROD\f$\SQLBackups\csLogins\csLogins.bak'

restore database [csLogins]
from disk = '\\DBPROD\f$\SQLBackups\csLogins\csLogins.bak'
with 
		  move 'CSLogins_Data' to 'E:\SQLDATA\MSSQL.1\MSSQL\Data\CSLogins_data_Lab.mdf'
		, move 'CSLogins_Log' to 'D:\SQLDATA\MSSQL\Logs\CSLogins_Log_Lab.ldf'
		, standby = 'D:\SQLDATA\MSSQL\Logs\CSLogins_Log.standby'
		,  stats=1

go

set nocount on;

declare @tblFile TABLE
(
	  [id] int identity(1,1)
	, [filename] varchar(600) null
)

declare @id int
declare @iNumberofFiles int
declare @filename varchar(600)
declare @folder	  varchar(600)

declare @filenameFull varchar(600)
declare @sql varchar(1000)

declare @database		 varchar(600)
declare @filenameStandBy varchar(600)
declare @CHAR_QUOTE		 varchar(10)

set @CHAR_QUOTE = ''''
set @database = 'csLogins'
set @folder = '\\DBPROD\f$\SQLBackups\MSSQL\csLogins'
set @filenameStandBy = 'D:\SQLDATA\MSSQL\Logs\CSLogins_Log.standby'

/*
	Get List of Transaction Log Files
*/

insert into @tblFile
(
	[filename]
)
exec master.dbo.xp_cmdshell'dir \\sukhoi\f$\SQLBackups\MSSQL\csLogins /B /OD'

/*
	Prep Local variables
*/
set @id = 1
set @iNumberofFiles = ( select max([id]) from  @tblFile )

/*
	Display List of Files
*/
select *
from   @tblFile
where  [filename] is not null

/*
	Iterate File List
*/
while (@id <= @iNumberofFiles)
begin

	/*
		Get file at position
	*/
	select @filename = [filename]
	from    @tblFile
	where  [id] = @id
	and    [filename] is not null

	/*
		If valid file name
	*/
	if (@filename is not null)
	begin

		/*
			Get full file name
		*/
		set @filenameFull = @folder	+ '\' + @filename

		/*
			Prepare SQL Statement
		*/
		set @sql = ' restore '
					+ ' log '
					+ quotename(@database)
					+ ' from disk = ' + @CHAR_QUOTE + @filenameFull + @CHAR_QUOTE + ' '
					+ ' with '
					+ '		  stats=1 '
					+ '		, standby=' + @CHAR_QUOTE + @filenameStandBy + @CHAR_QUOTE


		/*
			Display SQL Statement
		*/
		print @sql

		/*
			Execute SQL Statement
		*/
		exec(@sql)

		print 'processed ' 
					+ cast(@id as varchar(60))
					+ ') '
					+ @filenameFull


	end -- if (@filename is not null)

	/* Move record pointer */
	set @id = @id + 1

end
go




 

Warnings


processed 22) \\DBPROD\f$\SQLBackups\MSSQL\csLogins\csLogins_20160911014500.trn
 restore  log [csLogins] from disk = '\\DBPROD\f$\SQLBackups\MSSQL\csLogins\csLogins_20160911020000.trn'  with 		  stats=1 		, standby='D:\SQLDATA\MSSQL\Logs\CSLogins_Log.standby'
Msg 4326, Level 16, State 1, Line 1
The log in this backup set terminates at LSN 12975000000551500001, which is too early to apply to the database. A more recent log backup that includes LSN 12975000002135800001 can be restored.
Msg 3013, Level 16, State 1, Line 1
RESTORE LOG is terminating abnormally.

Good

Things are good once we properly set the backup and restore folders and reset our secondary database by restoring the last full backup of our primary server’s database and all the subsequent transaction log backups.

 

Diagnostic

The areas that you want to look at to see if you are having these problems are…

  1. msdb.dbo.log_shipping_monitor_history_detail
    • A big msdb.dbo.log_shipping_monitor_history_detail on the secondary server
    • In the message column, multiple entries bearing the signature of “Skipped log backup file. Secondary DB: ‘csLogins’, File: ‘\\[server-secondary]\[folder]\[filename]

Diagnostic – Skipped log backup file. Secondary DB: ‘<Database>’, File: <Filename>

log_shipping_monitor_history_detail-cslogins

 

Summary

I was really hoping to go a different way with this post.

I was hoping that the problem was more deeper than a simple & obvious configuration error on my side.

More esoteric and clever pathways will have to wait another day.

 

Salutation

Peace & Grace

 

 

 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s