Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Finding an LSN - Blind log shipping Expand / Collapse
Author
Message
Posted Friday, August 17, 2012 9:43 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:12 PM
Points: 72, Visits: 402
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?
Post #1346625
Posted Friday, August 17, 2012 10:30 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 7:28 AM
Points: 20,676, Visits: 32,267
cphite (8/17/2012)
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?


First, if your t-log files are date/time stamped in the filename, wouldn't it make sense to process them in that order?

Second, if you need to look at LSN's, check out this: http://msdn.microsoft.com/en-us/library/ms178536(v=sql.100).aspx.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1346655
Posted Friday, August 17, 2012 11:15 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 4:12 PM
Points: 72, Visits: 402
Lynn Pettis (8/17/2012)
cphite (8/17/2012)
I've been tasked with trying to set up log shipping in a blind scenario... that is, the source server and the target server cannot communicate with one another directly, or even see one another.

So we have log shipping enabled on the source side, and we're creating logs, and then getting them over to our DR site via some snap-mirroring magic.

My idea is to restore the databases on the target side in STANDBY mode, and then create a job that basically does the following:

1. Look for new .trn files in a folder.
2. Determine what .trn file is next in the chain based on the LSN number recorded in the .trn file.
3. Restore that log.
4. Look for the next one.
5. Repeat until there are no more logs.
6. Run again later to look for new logs, etc.

My problem is this... I can find the current LSN for a database by using dbcc log ('<Database>') but it's coming across as a hex value; for example 00000bde:000003cc:0001

My question is, how do I convert 00000bde:000003cc:0001 into something like 3035000000068900057? Or vice-versa? CAST and CONVERT don't seem to be working - I keep getting overflow errors.

Barring that; is there a better way to be doing log shipping in this scenario?


First, if your t-log files are date/time stamped in the filename, wouldn't it make sense to process them in that order?

Second, if you need to look at LSN's, check out this: http://msdn.microsoft.com/en-us/library/ms178536(v=sql.100).aspx.


I know how to get the LSN's from the files; my question is how to get the LSN from the database that is currently sitting in standby mode, and convert it into a format that I can use. I can't just process all of the files in order because it's possible that a file may have been created prior to the backup that I'm restoring from. I need to know which file is first in the chain since the restore point.

Also, while it's true that the file names are date/time stamped, it just seems like an LSN would be a much more specific and reliable way to go with an automated process.
Post #1346679
Posted Tuesday, March 26, 2013 1:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:08 PM
Points: 2, Visits: 19
found a function that will do the conversion. from M$'s knowledge base: http://support.microsoft.com/kb/886839

CREATE FUNCTION dbo.fn_convertnumericlsntobinary(
@numericlsn numeric(25,0)
) returns binary(10)
AS
BEGIN
-- Declare components to be one step larger than the intended type
-- to avoid sign overflow problems. For example, convert(smallint, convert(numeric(25,0),65535)) will fail but convert(binary(2),
-- convert(int,convert(numeric(25,0),65535))) will give the
-- intended result of 0xffff.
declare @high4bytelsncomponent bigint,
@mid4bytelsncomponent bigint,
@low2bytelsncomponent int
select @high4bytelsncomponent = convert(bigint, floor(@numericlsn / 1000000000000000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @high4bytelsncomponent) * 1000000000000000
select @mid4bytelsncomponent = convert(bigint,floor(@numericlsn / 100000))
select @numericlsn = @numericlsn - convert(numeric(25,0), @mid4bytelsncomponent) * 100000
select @low2bytelsncomponent = convert(int, @numericlsn)
return convert(binary(4), @high4bytelsncomponent) +
convert(binary(4), @mid4bytelsncomponent) +
convert(binary(2), @low2bytelsncomponent)
END

however... I still can't correlate the LSNs in my backup files to anything I get out of dbcc log or loginfo... I need to know that so I can know whether any particular file can be loaded (because the LastLSN of the last transaction log dump that was loaded is the same as the FirstLSN of the next I need to load)
Post #1435661
Posted Tuesday, March 26, 2013 1:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, April 26, 2013 12:08 PM
Points: 2, Visits: 19
got it. actually, you can correlate the LSNs in the backup files directly with what's in the msdb..restorehistory. The query at the bottom of this article:

http://www.sqlbackuprestore.com/logsequencenumbers.htm

shows what you need:

SELECT TOP 1 b.type, b.first_lsn, b.last_lsn, b.checkpoint_lsn, b.database_backup_lsn
FROM msdb..restorehistory a
INNER JOIN msdb..backupset b ON a.backup_set_id = b.backup_set_id
WHERE a.destination_database_name = 'AdventureWorks'
ORDER BY restore_date DESC

Post #1435666
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse