Re: distribution database does not seem to shrink

Hey Hilary,

Here is some more details

The distribution database that resides on the same server as 4 other published dbs seems to be choking on entries for just one of the publishers.
Did a grouping of commands from the msrepl_commands table and also the min entry_time from msrepl_transactions and it shows almost around 5 million entries for that one particular published db thats hogging the table and min entry time seems to go back exactly 72 hrs which is the default time for subscription expiration in the settings for the publication.

All of the subscribers seem to be current..

I checked another one of our distributors that seem to be on the same version as this and it seems to be up-to-date with the amount of used space just around 10 MB and not 10 GB as in this case..

Any command that I could run to tell me which particular subscription is causing the retention of all those commands... I wish just re-snapshotting is an option...



"Hilary Cotter" <hilary.cotter@xxxxxxxxx> wrote in message news:4127afb6-059e-46b8-9801-453f16ee7196@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Can you obtain the clean up procs from a SQL 2005 sp1 server, create
them on this server with slightly different names, and then use them
to do your clean up?


On Feb 5, 2:59 am, "John Doe" <John...@xxxxxx> wrote:
I dont have peer to peer replication setup per the link . Just
straightforward transactional replication.

I am on 9.0.3161

"Hilary Cotter" <hilary.cot...@xxxxxxxxx> wrote in message

news:be5067cd-3fd6-4d9f-aa88-991a28aec1ba@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Is the cleanup task working?

There is a problem with SP2 with the cleanup task. Microsoft is
advising you revert back to the SQL 2005 sp 1 procs.

Also, have you seen this?

http://support.microsoft.com/kb/947008/en-us

On Feb 3, 2:41 pm, "John Doe" <John...@xxxxxx> wrote:



> Using SQL 2005 transactional replication. The space used in the data > file
> is
> around 10GB and all of our subscribers are up-to-date and the > distribution
> clean up job is running too and yet the size of used space does not seem
> to
> reduce. It used to be that if all transactions made it to the > subscribers
> and if the clean job runs every 5 mins or so, the size should usually be
> in
> the 100 MBs where I have seen it before ( atleast in SQL 2000)

> Let me know how to figure out whats in that database thats consuming all
> the
> space and why is it not clearing off

> Thanks- Hide quoted text -

- Show quoted text -

.

Relevant Pages

  • Re: Configuring a remote server as a subscription server
    ... On the publisher go to Tools, Replication, Configure Publisher, Subscribers, ... and Distribution, click on the Subscribers tab and add it there. ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • Re: 400 remote sites connected via DSL
    ... distribution point server share. ... If the SMS server is getting hit by all the clients at once, ... 2000 roaming clients downloading from remote distribution points. ...
    (microsoft.public.sms.swdist)
  • Re: Deadlock between Distribution Agent and Distribution Agent Cle
    ... The commands are shared by multiple subscribers. ... Note that the delivered commands in distribution db can vary widely as some ... > by the Log Reader Agent. ...
    (microsoft.public.sqlserver.replication)
  • Re: server xloads on my desktop
    ... server xloads on my desktop ... GSSAPIAuthentication yes ... for the addressee and may contain confidential, ... unauthorized review, distribution or other use ...
    (AIX-L)
  • Re: schannel.dll, secur32.dll, and DSCLIENT.EXE redistribution
    ... > include it in the distribution package. ... > when not present in a client attempting to access server. ... >> distributing these DLLs to them, ...
    (microsoft.public.platformsdk.security)