locked
msrepl_command RRS feed

  • Question

  • Hi All,

    I am running below command in sql server 

    select distinct DB_NAME(publisher_database_id),COUNT(1) from MSrepl_commands(nolock)
    group by DB_NAME(publisher_database_id)

     It provide information about those user databases which i have never used in replication and even two dbs like 

    ReportServer
    ReportServerTempDB

    so anyone tell me why this command provide information about non replicated database.

    And another question is how can i delete undistributed transaction from distribution db (msrepl_command)

    Thanks

    Friday, March 20, 2015 3:10 PM

Answers

All replies

  • someone at some point in time probably publisher the reportserver and reportserertempdb databases.

    You can delete commands manually which are past the retention period. Note that you might break some virtual subscriptions which are using for immediate syncs.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Friday, March 20, 2015 4:58 PM
    Answerer
  • Hi Hillary,

    Thanks for response

    someone at some point in time probably publisher the reportserver and reportserertempdb databases.

    I have checked there is no publication on reportserver and reportserertempdb . If there is no publication then why data is appear on msrepl_command  because every day deletion of older transaction job(default rentintion period is 72hours) is running on distribution db .

    You can delete commands manually which are past the retention period. Note that you might break some virtual subscriptions which are using for immediate syncs.

    if deletion job is running on daily so what is the cause of data is not delete the data by job.

    What is the reason for occurring for virtual subscription.

    Thanks in advance!!

     

    Friday, March 20, 2015 5:17 PM
  • Yes, there may be no publication NOW, but there might have been one in the passed.

    Also check to ensure that originating_id (of msrepl_commands) is 0. If it is a different value, the distribution database might be used by a different publisher.

    You may have defunct subscriptions/publications which for whatever reason the clean up task is not cleaning up. There were bugs in the past which caused this - you might be running into one or more of them.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    • Proposed as answer by Michelle Li Monday, March 23, 2015 7:57 AM
    • Marked as answer by Michelle Li Thursday, April 2, 2015 12:30 PM
    Friday, March 20, 2015 5:23 PM
    Answerer