SQLServer – Log Shipping – Monitoring – log_shipping_monitor_secondary

Background

Here I am trying to make sure that our DR solution is working as it should.  In this case, the box is on MSSQL v2005 and it is configured to use Log Shipping.

Working Papers

There are a couple of public postings by Manvendra Singh  that we are going to use and rest on.

They are:

  1. Change SQL Server log shipped database from Restoring to Standby Read-Only
  2. Read SQL Server Transaction Log Backups to Find When Transactions Occurred

 

How to get a quick status of Log Shipping

msdb.dbo.log_shipping_monitor_secondary

From Manvendra Singh, here is one way yo get a reading of LS:

Code:


SELECT 
	  secondary_server
	, secondary_database
	, primary_server
	, primary_database
	, last_copied_file
	, last_copied_date
	, last_restored_file
	, last_restored_date
from msdb.dbo.log_shipping_monitor_secondary
order by last_restored_date desc

Output:

log_shipping_monitor_secondary

Explanation:

  1. I am a bit concerned that the last_restored_date on the last two databases lags
  2. As can be seen the last_copied_date is recent

 

SQL Server Agent – Job History

On the target, reviewed the Log Shipping Restore Job

Grid

JobRestore-LSRestore-DB

Textual:

  1. 2016-03-05 12:10:01.30 Skipped log backup file. Secondary DB: ‘nettraffic_db’, File: ‘\\Mikoyan\SQLbackups\MSSQL\NetTraffic_db\nettraffic_db_20160305190001.trn’
  2. 2016-03-05 12:10:01.31 Skipped log backup file. Secondary DB: ‘nettraffic_db’, File: ‘\\Mikoyan\SQLbackups\MSSQL\NetTraffic_db\nettraffic_db_20160305191005.trn’

 

What is going on?

Somehow our backup files are been skipped

 

Let us review the backup files

Let us go take a look at the backup files and make sure that they are not corrupt:

Non-Working DB

Code:


declare @filename sysname

set @filename = N'F:\SQLBackup\MSSQL\NetTraffic_db\nettraffic_db_20160305201001.trn'


SELECT [Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]
FROM fn_dump_dblog 
(	
	  NULL
	, NULL
	, N'DISK'
	, 1
	, @filename
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
)

GO

Output:

fn_dump_dblog_empty

Explanation:

Our log file is empty.

 

Working DB

Code:


declare @filename sysname

set @filename = N'F:\SQLBackup\MSSQL\csLogins\csLogins_20160303140005.trn'

SELECT top 5
		[Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]
FROM fn_dump_dblog 
(	
	  NULL
	, NULL
	, N'DISK'
	, 1
	, @filename
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
)

GO

Output:

fn_dump_dblog_inuse

Code:

  1. On the working databases, we see that the log files have something to show

 

Review Backup File

Another way of reviewing back files is to use “Restore filelistonly from disk



declare @filenameNetTraffic		 sysname
declare @filenameCSLogins		 sysname
declare @filenameCSSchoolContent sysname

declare @sql			    varchar(600)

declare @header table 
( 
    BackupName varchar(256),
    BackupDescription varchar(256),
    BackupType varchar(256),        
    ExpirationDate varchar(256),
    Compressed varchar(256),
    Position varchar(256),
    DeviceType varchar(256),        
    UserName varchar(256),
    ServerName varchar(256),
    DatabaseName varchar(256),
    DatabaseVersion varchar(256),        
    DatabaseCreationDate varchar(256),
    BackupSize varchar(256),
    FirstLSN varchar(256),
    LastLSN varchar(256),        
    CheckpointLSN varchar(256),
    DatabaseBackupLSN varchar(256),
    BackupStartDate varchar(256),
    BackupFinishDate varchar(256),        
    SortOrder varchar(256),
    CodePage varchar(256),
    UnicodeLocaleId varchar(256),
    UnicodeComparisonStyle varchar(256),        
    CompatibilityLevel varchar(256),
    SoftwareVendorId varchar(256),
    SoftwareVersionMajor varchar(256),        
    SoftwareVersionMinor varchar(256),
    SoftwareVersionBuild varchar(256),
    MachineName varchar(256),
    Flags varchar(256),        
    BindingID varchar(256),
    RecoveryForkID varchar(256),
    Collation varchar(256),
    FamilyGUID varchar(256),        
    HasBulkLoggedData varchar(256),
    IsSnapshot varchar(256),
    IsReadOnly varchar(256),
    IsSingleUser varchar(256),        
    HasBackupChecksums varchar(256),
    IsDamaged varchar(256),
    BeginsLogChain varchar(256),
    HasIncompleteMetaData varchar(256),        
    IsForceOffline varchar(256),
    IsCopyOnly varchar(256),
    FirstRecoveryForkID varchar(256),
    ForkPointLSN varchar(256),        
    RecoveryModel varchar(256),
    DifferentialBaseLSN varchar(256),
    DifferentialBaseGUID varchar(256),        
    BackupTypeDescription varchar(256),
    BackupSetGUID varchar(256)

	/*
		+v2012
	    CompressedBackupSize varchar(256),        
	    Containment varchar(256)
	*/

    , Seq int NOT NULL identity(1,1)

); 

set @filenameNetTraffic = 'F:\SQLBackup\MSSQL\NetTraffic_db\nettraffic_db_20160305205001.trn'
set @filenameCSLogins = N'F:\SQLBackup\MSSQL\csLogins\csLogins_20160303140005.trn'
set @filenameCSSchoolContent = N'F:\SQLBackup\MSSQL\csSchoolContent\csSchoolContent_20160306012000.trn'

set @sql = 'restore headeronly from disk = ''' + @filenameNetTraffic + ''' '
insert into @header
exec(@sql)

set @sql = 'restore headeronly from disk = ''' + @filenameCSSchoolContent + ''' '
insert into @header
exec(@sql)

set @sql = 'restore headeronly from disk = ''' + @filenameCSLogins + ''' '
insert into @header
exec(@sql)


select 
		  tblH.DatabaseName
		, tblH.BackupDescription
		, tblH.BackupSize
		, tblH.FirstLSN
		, tblH.LastLSN
		, [differentialLSN]
			= cast(tblH.LastLSN as decimal(32, 0))
				- cast(tblH.FirstLSN as decimal(32, 0))
		, tblH.HasBackupChecksums
		, tblH.IsDamaged

from   @header tblH


Output:

RestoreHeaderOnly

Explanation:

  1. On empty backup files the differential LSN, lastLSN – FirstLSN is 0
  2. It is comforting to know that IsDamaged is 0

 

Simulator

 

Let us create artificial records and see if in forcing transactions on the otherwise silent databases, we can generate log data.

 

Create Table

 


set noexec off
go

use [nettraffic_db]
go

if object_id('[_simulator]') is not null
begin

   set noexec on

end
go

create table [_simulator]
(
   [id] bigint not null
      identity(1,1) 

  , [addedBy] sysname not null
	constraint [contraintDefaultAddedBy] 
	  default system_user

  , [dateAdded] datetime not null
	constraint [contraintDefaultDateAdded] 
		default getdate()

  , [hostname] datetime not null
	constraint [contraintDefaultHostname] 
		default host_name()

  , constraint [PK_simulator]
	primary key
	(
  	   [id]
	)

)
go

set noexec off
go

 

Check DB Log Backup File

Code



declare @filename sysname

set @filename = 'F:\SQLBackup\MSSQL\NetTraffic_db\nettraffic_db_20160305205001.trn'

SELECT top 5
[Current LSN], [Transaction ID], [Transaction Name], [Operation], [Begin Time], [PartitionID], [TRANSACTION SID]
FROM fn_dump_dblog 
(	
	  NULL
	, NULL
	, N'DISK'
	, 1
	, @filename
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
	, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT, DEFAULT
)

GO

Output

fn_dump_dblog_WasEmpty

Check msdb.dbo.log_shipping_monitor_secondary

Code



SELECT 
	--secondary_server,
	secondary_database,
	--primary_server,
	--primary_database,
	last_copied_file,
	last_copied_date,
	last_restored_file,
	last_restored_date,
	[diffInMin]
	= datediff(mi, last_restored_date, getdate())

from msdb.dbo.log_shipping_monitor_secondary

where  secondary_database not in ('DBBackup')

order by last_restored_date desc

Output

log_shipping_monitor_secondary_nettraffic

Explanation:

Our NetTraffic db has now caught up.
The problem all along was the fact that the database is mostly dormant.

 

Database State – “Restoring” Versus “StandBy and ReadOnly”

In all honesty, our concern on Friday night was that our database states was marked “Restoring“.

The specific database we are targeting is nettraffic_db.

ObjectExplorer-Before

We wanted it to be marked “Standby and Read-Only“.

The transition was not occurring as their was never a transaction log filled with data that could bring about the change.

Here is what we look like now…

ObjectExplorer-After

 

Listening

Was a party last weekend.

The kid went Retro as he was fitted with a “Kid and Play” High-Top Fade.

KidAndPlayHighTopFade

Wanted to ask him what he know about it…

But, thought again, why steal his shine.

Me, myself, and I” being listening:

Jean Grae – “Soda & Pop” (feat. Masta Ace)

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