Saturday 13 February 2010

P2P Transactional "Query Timeout Expired" for Distributor to Subscriber

I thought I'd add a post to detail a problem I just had with replication where transactions were just not arriving at the subscriber database, resulting in the Replication Monitor listing the following errors (repeatedly every 30 mins or so...).

Query timeout expired
The process is running and is waiting for a response from the server

and then terminating with this error...
Agent 'xxxx' is retrying after an error, nn retries attempted

(apologies for scrubbing out sensitive names)

The statement that was run on the publisher was an update that affected 25k rows. It was only
to update the value of a 'bit' from 1 to 0, but each row was large, some 800k as it contained varchar(max) data.

Now it appears that even though it was just a small amount of data that had changed, the whole row is transmitted over, 800k and all. This meant that when the the system issued the sys.sp_MSget_repl_commands procedure things took too long....longer than 1800 seconds (1800 you say, why did you not just say 30 minutes like normal people do, you freak). Well...

Theres a parameter that the distributor and log agents run with
-QueryTimeout 1800 and the default setting (in seconds) appears to be...yes 1800.

So, i upped this to some 50000 seconds...and wahay things started to 'unbung' themselves, rather than timing out.

How do you change this setting? Well in SSMS I....
1) right click Replication
2) click distributor properties, choose general
3) click the 'Profile Defaults...' near bottom right
4) choose 'Distribution Agents' on left
5) click 'New..' to create a new default agent profile
6) choose 'Default Agent Profile' from the list displayed, (to copy this)
7) Choose a nice name (meaningful now, come on!), and amend the QueryTimeout in right column
8) Save, and then 'check' this profile as being 'Default for New'
9) Click 'Change Existing Agents', Confirm 'Yes'

Repeat from 4 to 9 for the 'Log Reader Agents'

Then I restarted 'SQL Server Agent' on the publisher machine.

Things are now chugging fine.


  1. Very useful article...

  2. Awesome article! Thanks!!

  3. Can this be done during business hours, or do you recommend doing this during a maintenance window?

  4. What would you say the top online marketing channel is right now? white house market