SQL Server Replication Troubleshooting
Monday 23 March 2015
Monday 25 April 2011
Fixing Missing Rows error in transactional replication
Fixing Missing Rows error in transactional replication:
· Set up transactional replication
http://sqlserverreplicationtroubleshooting.blogspot.com/2011/04/setting-up-transactional-replication-in.html
· Once it is done.
o Delete one row of data from subscriber.
o Update the same row in publisher
Go to replication monitor to view the error:
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000015000001B9000400000000, Command ID: 1)
(Transaction sequence number: 0x00000015000001B9000400000000, Command ID: 1)
Same error you can view from following table
Select * from msrepl_errors
You can see that there are 2 items mentioned in error one is transaction sequence number: 0x00000015000001B9000400000000 and another is command id:1
System Procedure sp_browsereplcmds takes the values transaction sequence number and command id as parameters and outputs the command that caused the errors.
Transaction sequence number:
Identifier for the transaction which contains commands.
Command ID:
Each command have a unique ID within the scope of the transaction sequence. The command starts at 1 and increment by 1 for each new command. Command should be updated in same sequence if it is more than one command.
Now How to fix the error:
· Get the article id and publisher database id from following query on distributor database using,
select *
from msrepl_commands (nolock)
where command_id = 1 and xact_seqno = 0x00000015000001B9000400000000
· Use the output of above query in following statemet on distributor DB,
sp_browsereplcmds @article_id = 1, @command_id = 1 , @xact_seqno_start = '0x00000015000001B9000400000000', @xact_seqno_end = '0x00000015000001B9000400000000',@publisher_database_id = 1
· Copy the command column from above statement output,
{CALL [dbo].[sp_MSupd_dboTestTable] (,N'testDelete',2,0x02)}
check for the sp_helptext on the proc
Create sql statement as mentioned in Procedure
SQL Server creates 3 procedure on subscriber which starts with sp_MS
[sp_MSdel_dboTestTable]
[sp_MSins_dboTestTable]
[sp_MSupd_dboTestTable]
· You can see from above statement that for ID – 2 update statement is failed
· Go to subscriber and insert the row for ID – 2
INSERT INTO [xyz].[dbo].[TestTable]
([id]
,[text])
VALUES
(2
,'Anydata')
GO
· Go and verify the replication monitor error is gone.
· Get the MaxID of the Article on the subscriber from below :
Get the Range of missing Rows:
By connecting to the subscriber Get the count
select COUNT(*) from TestTable(nolock) where id between 4 and 6 --1
Get the Missing count by doing
select (Get the count from publihser from above)- (get the count from subscriber from above)=
Inserting the missing rows manually into subscriber:
SELECT A.* FROM TestTable_ReplIssue__1 A (nolock)
Left Join TestTable (nolock)B On A.id = B.id
Where B.id Is null
COMMIT
If there is any problem with Linked server while inserting the records follow the steps below
select * from sys.servers
If there are mutiple rows are missing then follow the below steps:
· Get the MaxID of the Article on the subscriber from below :
select MAX(id) from TestTable
Get the Range of missing Rows:
By connecting to the subscriber Get the count
Get the missing ID from the procedure call ,Missing ID (in subscriber) and the MaxID if the article(in subscriber), Get the count
select COUNT(*) from TestTable(nolock) where id between 4 and 6 --1
By connecting to the publisher Get the count
select COUNT(*) from TestTable(nolock) where id between 4 and 6 --3
Get the Missing count by doing
select (Get the count from publihser from above)- (get the count from subscriber from above)=
Select 3 - 1 2 (Missing Rows Count)
Inserting the missing rows manually into subscriber:
· Create a temp table in the subscriber with same structure as the article(table being replicated)
select * INTO TestTable_ReplIssue__1 FROM TestTable (nolock) where 1=0
· Insert the rows into the temporary table by selecting the data from publisher using the linked server.
INSERT INTO TestTable_ReplIssue__1
select * FROM [MININT-8JHDRAI].TestDB.dbo.TestTable
where ID between 4 and 6
· Insert the missing rows into the article by comparing with the temp table created above.
BEGIN TRAN
INSERT INTO TestTableSELECT A.* FROM TestTable_ReplIssue__1 A (nolock)
Left Join TestTable (nolock)B On A.id = B.id
Where B.id Is null
COMMIT
If there is any problem with Linked server while inserting the records follow the steps below
select * from sys.servers
sp_dropserver 'MININT-8JHDRAI', 'droplogins';
EXEC sp_addlinkedserver
'MININT-8JHDRAI',
N'SQL Server'
GO
Setting up transactional Replication in SQL Server
PULL Replication Setup on 3 database servers. Where Publisher, Distributor and Subscriber all are in different servers
1) To set up distributor
· Go to distributor server
· Click on replication folder
· Click configure distribution
2) To set up publisher of distributor (Password setting which will be used while creating publication on publisher Instance)
· Go to distribution server
· Click on replication folder
· Click distributor properties
· Click on publishers
· Add the publisher you want to add
· And provide password
3) Creating publication and article on publisher Instance
· Go to publisher server
· Click on replication folder
· Click on local publications
· Click on new publication
· Select database and publication type
· Select article
· Provide security
4) Setting up subscription on Subscriber
· Go to subscription database
· Click on replication folder
· Click on local subscription
· Click on new subscriptions
· From publisher combo box
· Select Find sql server publisher
· Slect the sql server publication instance
Setting Up Distributor
/****** Scripting replication configuration. Script Date: 4/3/2011 10:01:20 PM ******/
/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/
/****** Installing the server as a Distributor. Script Date: 4/3/2011 10:01:20 PM ******/
use master
exec sp_adddistributor @distributor = N'AJAY-PC\MSSQLSERVER2', @password = N''
GO
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2\MSSQL\Data', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER2\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\AJAY-PC\ReplData1', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'\\AJAY-PC\ReplData1', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = N'AJAY-PC\MSSQLSERVER2', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'\\AJAY-PC\ReplData1', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
Adding publication on Publication server
use [AdventureWorks]
exec sp_replicationdboption @dbname = N'AdventureWorks', @optname = N'publish', @value = N'true'
GO
-- Adding the transactional publication
use [AdventureWorks]
exec sp_addpublication @publication = N'Ajay_publication', @description = N'Transactional publication of database ''AdventureWorks'' from Publisher ''AJAY-PC\MSSQLSERVER1''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
exec sp_addpublication_snapshot @publication = N'Ajay_publication', @frequency_type = 1, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @job_login = null, @job_password = null, @publisher_security_mode = 1
use [AdventureWorks]
exec sp_addarticle @publication = N'Ajay_publication', @article = N'Contact', @source_owner = N'Person', @source_object = N'Contact', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Contact', @destination_owner = N'Person', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_PersonContact', @del_cmd = N'CALL sp_MSdel_PersonContact', @upd_cmd = N'SCALL sp_MSupd_PersonContact'
GO
SCRIPT FOR SUBSCRIBER
-----------------BEGIN: Script to be run at Publisher 'AJAY-PC\MSSQLSERVER1'-----------------
use [AdventureWorks]
exec sp_addsubscription @publication = N'Ajay_publication', @subscriber = N'AJAY-PC', @destination_db = N'NewTestSubscription', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO
-----------------END: Script to be run at Publisher 'AJAY-PC\MSSQLSERVER1'-----------------
-----------------BEGIN: Script to be run at Subscriber 'AJAY-PC'-----------------
use [NewTestSubscription]
exec sp_addpullsubscription @publisher = N'AJAY-PC\MSSQLSERVER1', @publication = N'Ajay_publication', @publisher_db = N'AdventureWorks', @independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0
exec sp_addpullsubscription_agent @publisher = N'AJAY-PC\MSSQLSERVER1', @publisher_db = N'AdventureWorks', @publication = N'Ajay_publication', @distributor = N'AJAY-PC\MSSQLSERVER1', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20110403, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'AJAY-PC'-----------------
How to see the replication error details from Distributor databse in SQL Server
We had multiple databases and I was assigned task to see the replication error on those sql server instances.
It was very time consuming task to go to replication monitor and find replication error from each and every server.
Then I created following query which will give you the error details and the databases involved in replication activity.
use distribution
It was very time consuming task to go to replication monitor and find replication error from each and every server.
Then I created following query which will give you the error details and the databases involved in replication activity.
use distribution
select msrepl_errors.time,error_code, error_text,msrepl_errors.xact_seqno ,command_id,name , publisher_db,publication,subscriber_db from MSdistribution_history
join msrepl_errors on msrepl_errors.id=MSdistribution_history.error_id
join [MSdistribution_agents] on MSdistribution_history.agent_id = [MSdistribution_agents].id
where
msrepl_errors.time between
DATEADD(hh, -24, getdate())
and GETDATE()
join msrepl_errors on msrepl_errors.id=MSdistribution_history.error_id
join [MSdistribution_agents] on MSdistribution_history.agent_id = [MSdistribution_agents].id
where
msrepl_errors.time between
DATEADD(hh, -24, getdate())
and GETDATE()
This will give error in last 24 hours. You can increase of decrease the value of hours based on you requirement.
Subscribe to:
Posts (Atom)