SQL Server Performance Forum – Threads Archive

sp_MSdistribution_cleanup

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

---

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

---

]]>