SQL Server Performance

sp_MSdistribution_cleanup

Discussion in 'Performance Tuning for SQL Server Replication' started by Jon M, May 27, 2005.

  1. Jon M Member

    Hi,
    Our reporting SQL server service was deliberately stopped yesterday by one of the staff thinking that it will resolve blocking processes caused by one of the subscriptions when it didn't. To remedy this, I stopped the synchronization of the subscription and re-initialize it. Everything went fine afterwards.

    Today, I checked on my distributor and executed "sp_who2" and "dbcc inputbuffer". Below is something which I think is weird:
    SPID 60
    Status sleeping
    Login myLogin
    Hostname myhostname
    BlkBy.
    DBNamedistribution
    CommandDELETE
    CPUTime2263156
    DiskIO27086196
    LastBatch05/26 10:35:00
    ProgramNameSQLAgent - TSQL JobStep (Job 0xD0295D7FDBDBE34DA6CFDB8275F951F9 : Step 1
    SPID60

    dbcc inputbuffer(60)
    EventTypeLanguage Event
    Parameters0
    EventInfoEXEC dbo.sp_MSdistribution_cleanup @min_distretention = 1, @max_distretention = 24

    It seems to me that the distribution cleanup has been running for almost 1 day now. Can I kill this process (ID 60)? Or should I let it finish?

    Also, some of the tables in the distribution database doesn't have any index keys. Is there any MS fix for this or MS created it this way?

    Thanks,
    Jon M
  2. gangulyarindam New Member

    Hi Jon,

    I think some Transactions were still staying on the Distribution Database while replication was going on. As it was stopped deliberately, the Distribution Push Agent was unable to trace the Subscriber and is continuing to remain in indeterminant stage.

    Ensure that the Distributor Push Agent is not running and then kill the Process.

    Thanks.

    Arindam Ganguly

Share This Page