What does stats state information means in the replication monitor??

Question

Hi there,

We have started the subscription process between two production machines and the subscription process is happening for 18 hrs,and in the replication monitor i am seeing information being printed for every 5min like this ,

<stats state="2" fetch="1519" wait="581" cmds="797856" callstogetreplcmds="1"><sincelaststats elapsedtime="300" fetch="300" wait="0" cmds="223970" cmdspersec="746.000000"/></stats>
What does this means?
Also i tried to keep track of the transactions from the table dbo.MSdistribution_status i am not seeing any improvement for the past 10 hours,
The DeliveredCommandsInDistDB is not showing any change its remainking the same, my suspection from the replication monitor is that the data is getting transfered to subscription database if that is the case then why the UndeliveredCOmmandsInDistDb column from MSdistribution_status is not getting reduced?

Can anyone please do give a quick response.

Thanks

Answer 1

Are you sure your replication is working, How can you say your transactions are updating @ subscriber side...

Can you leave apart the GUI Monitor and try to dig with T-SQL commands what is happening .. I would suggest to follow the step by step troubleshooting as below mentioned blog post...

After this you would be in good shape and better position to grab the neck of the issue..

http://blogs.msdn.com/b/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx

Do let me know afterwards whats happening 

Cheers

Praveen

Answer 2

Answering to you question in Caption 

<stats state="2" fetch="1519" wait="581" cmds="797856" callstogetreplcmds="1"><sincelaststats elapsedtime="300" fetch="300" wait="0" cmds="223970" cmdspersec="746.000000"/></stats>

Stats State = 2 ....Reader Thread has wait for Writer Thread

Cmds = Cmds No's

fetch="300" wait="0"  Fetch waited for 0 secs

Cmdpersec= Delivery rate..

For troubleshooting its important to Stats State = 1 with Complete Info

Like Reader Thread

Writer thread etc etc..

Examaple- With complete info

<stats state="1" work="14798" idle="2035"><reader fetch="19798" wait="191"/><writer write="19373" wait="9808"/><sincelaststatselapsedtime="424" work="415" cmds="296900" cmdspersec="713.000000"><reader fetch="415" wait="9"/><writer write="377" wait="296"/></sincelaststats></stats>

reader thread WAITED only 9 Sec for WRITER
writer thread WAITED 296 Sec 

Mark as Helpful, If it helps , Mark as answer if it resolves your issue.

Answer 3

Praveen,

Do you mean that there is a deadlock state happening here?

reader thread WAITED only 9 Sec for WRITER
writer thread WAITED 296 Sec

Answer 4

Nope, 

It means Your logreader thread waited for 9 Sec to read..and Writer thread which is actually writing to your Distribution table and which took 296 Sec....It totally depends on the kind of activity and IOPS of your system.

Hope it would help you to make you clear on this now.

Cheers

Praveen

---------Mark as answer if it solves your problem-------

Answer 5

Can you check to confirm your verbose level of your log reader agent.

What you need to do is launch replication monitor, drill down on your problem publication, in the right hand pane select the agents tab, and then right click on your log reader agent and select agent profile and make sure you are using the Default agent profile.

If you are then you need to confirm that you are not using any special command line switches. Right click on your log reader agent again and select properties. Then click steps and Run agent. Then click Edit.

It should look something like this:

-Publisher [WINDOWS7] -PublisherDB [SeanPub] -Distributor [WINDOWS7] -DistributorSecurityMode 1  -Continuous

If not post the string you see back here.

Then you need to confirm that there has been no large batch update/insert/delete process the the log reader agent is processing.

The best way to determine this is to issue the following query:

USE <published database>

dbcc opentran

Replicated Transaction Information:         Oldest distributed LSN     : (54:4986:1)         Oldest non-distributed LSN : (55:4803:2)

--Count number of pending to replicated log entries per transaction

select  [Transaction ID], count([Transaction ID]) as 'Count log entries' from::fn_dblog('55:4803:2',null)   -- Oldest non-distributed LSN : (55:4803:2) where [Description] = 'REPLICATE' group by [Transaction ID] GO

Does the transaction which has the highest count have several thousand enteries?

This query is from

http://blogs.msdn.com/b/repltalk/archive/2010/10/25/troubleshooting-logreader-timeout-executing-sp-replcmds.aspx

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

Answer 6

Praveen that was very helpfull,i understood better now.

Hilary,

I have noticed that the job agent run is having this step,

-Publisher [MachineA] -PublisherDB [RegressionTestDB] -Distributor [MachineB] -DistributorSecurityMode 1
-Output D:\user\senthilp\logreaderagent\RegressionDBReplicationStatus\ReplicationTestLogStatus.txt -Outputverboselevel 1 -Continuous -MaxCmdsInTrans 1000

And my logreader agent profile was set to use a user defined profile with ReadbatchSize set to 1,QueryTimeout set to 36 and also all the other properties were set to the same as default profile.

Please let me know whether any of these properties might degrade the replication process?

Thanks,

Aish

Answer 7

This is bad - ReadbatchSize - set it to 100 or 500.

You do not need to do continuous logging, I would remove the output settings, ie

-Output D:\user\senthilp\logreaderagent\RegressionDBReplicationStatus\ReplicationTestLogStatus.txt

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941