February 22, 2016 at 10:13 pm
Hi,
I am trying to initialize a subscription from a backup.
I am getting this error:
The transactions required for synchronizing the nosync subscription created from the specified backup are unavailable at the Distributor. Retry the operation again with a more up-to-date log, differential, or full database backup.
allow_initialize_from_backup for the publication is set to 1.
This query:
select min_autonosync_lsn,* from syspublications where name = 'XYZ'
returns:
0x0000027B00001BAF006D
This query:
select min_autonosync_lsn,* from distribution.dbo.mspublications where publication ='XYZ'
also returns:
0x0000027B00001BAF006D
RESTORE HEADERONLY FROM DISK = 'c:\temp\xyz.bak'
returns:
635000000059800001
I convert it into binary with this query:
declare @numericlsn numeric(25,0)
declare @high4bytelsncomponent bigint,
@mid4bytelsncomponent bigint,
@low2bytelsncomponent int
--set the lsn here
set @numericlsn = 93000000070800001
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)
SELECT convert(binary(4), @high4bytelsncomponent) + convert(binary(4), @mid4bytelsncomponent) + convert(binary(2), @low2bytelsncomponent)
and get:
0x0000027B000002560001
I repeat the process of backup/restore and init and get these numbers:
0x0000027B00002169006C
0x0000027B00002169006C
635000000059800001
0x0000027B000002560001
It looks like min_autonosync_lsn keeps changing every few minutes even though allow_initialize_from_backup is set to 1 all the time.
This happens only to one database out of 7 replicated on the server. For other 6 the same process of initializing the subscription from a backup works fine.
I used this link to troubleshoot:
Any ideas?
Thanks
February 23, 2016 at 1:45 am
See if this helps : http://blogs.technet.com/b/warreny/archive/2010/12/27/sql-server-transactional-replication-resynchronization.aspx
-- @distribution Cleanup Agent
December 15, 2023 at 12:43 pm
we have encountered this issue recently . I guess it is due to.. while in command to set up replication using backup file ..sys.sp_addsubscription @backupdevicename='xxx.bak', from backup file mentioned it gets the lastlsn using restoreheaderonly command . if txns after the lsn is not available in the active log, it fails to get them into distribution database. So remedy is stop the backups(log/diffs) in source database after the backup file mentioned in sp_addsubscription and use the command . I took help of below article to get understanding.
UmaShankar
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy