| Author |
Topic |
|
Humate
Posting Yak Master
101 Posts |
 Posted - 2011-02-14 : 06:28:27
|
| Hi All,I am having trouble with a replicated database. In replication monitor the number of commands waiting to be applied to the subscriber is constantly increasing, it is now at 1,800,000 and the latency has increased dramatically. Previously this replicated database had been working without any issues for 3 months or so. There are only 5 tables being replicated, and a few hundred thousand rows, so I have no idea why so many commands. I have changed the immediate_sync setting to false but this hasn't had any noticable effect.The main problem and reason I started looking into this, is that the tables at the subscriber database are locked and I cannot run queries against the data. Can anyone offer any help?Thanks |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-02-14 : 09:33:29
|
| Why are the tables at the subscriber locked? Is it the replication that keeps them locked or some other process...? The latency will keep increasing until the locks have been released and the replication can resume.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
 |
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2011-02-14 : 09:44:22
|
| It looks like the tables are locked by the replication task which is completing an update (when I run sp_who2 this is the information I can see). If I run a simple select query the command is blocked by the replication task - unless I specify the nolock option in the query. There are only 200,000 rows to be replicated in total. Since the initial syncronisation only one or two transactions have updated to the subscriber (in a matter of several hours). It was working virtually instantly until a few days ago. |
|
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2011-02-14 : 15:45:34
|
| Just a further update, the latency has now increased to nearly 8 hours, and 11m uncommitted transactions. The distributor database is 3700MB. I have no idea why the latency is so high all of a sudden. I have changed the distribution clean up jobs to only run each hour instead of every ten minutes incase this was affecting performance, however the jobs only take a few seconds to complete. The server has recently had RAM increased to 16GB from 8GB, so wasn't expecting such poor performance. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-02-14 : 17:26:29
|
| What changed a few days ago?May need to drop the subscription and rebuild it.Are you doing mass deletes then repopulating the publisher?By the way, have you dropped a tracer token? Sometimes replmon shows undistributed commands when there really aren't any. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-02-14 : 17:27:26
|
| you might want to fire up profiler and see if there are some statements that are stuck.. or even whats getting blocked and by who?Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
Humate
Posting Yak Master
101 Posts |
Posted - 2011-03-01 : 06:10:59
|
| I eventually found the answer. There was a problem with the application code, so needed to get one of the web developers to fix it. The table insert/update query had been written incorrectly, so every record in the table was being updated, instead of the unique customer record! So effectively, the table update never finished. |
|
|
|
dinakar
Master Smack Fu Yak Hacker
2507 Posts |
Posted - 2011-03-01 : 12:25:32
|
| I see this happen often when application developers write TSQL Code... all TSQL Code needs to be reviewed by DBAs before its deployed into production.Dinakar Nethi************************Life is short. Enjoy it.************************http://weblogs.sqlteam.com/dinakar/ |
|
|
|
|
Replicated database - high undistributed commands