none
Large Distribution Database with Few Rows

    Question

  • I have a distribution database that is growing very large in size (50g+), but there are only a couple hundred rows in msrepl_Commands.  I rebooted the server last week and the size went down to 500 mg.  After a day, it started growing again.  

    I noticed Ghost Cleanup has been running since it was rebooted, which makes me think the records are being marked for deletion but not being physically deleted?

    We're running SQL 2005 on the production server, where the distribution database is growing.  SQL 2008 on a dedicated ETL server that does the CDC transforms.  And SQL 2005 on a reporting server where the target tables are located.

    In sp_who2, I'm seeing locks every 5 minutes or so that last for a couple minutes.  It's a delete command on distribution with a wait type of LCK_M_IX.  The application is the data serves engine on the ETL server.  It's being blocked by the same application with a wait type of PAGEIOLATCH_SH.

    I'm pretty sure the locks and distribution growing are related.  Any idea's on what's causing this?  Thanks.

    Monday, February 20, 2012 6:23 PM

Answers

  • I disabled the distribution cleanup job because it was running without completing and using the available space. 

    There are only a couple hundred records in msrepl_Commands anyway.  Our ETL server does the deletes, so Distribution Cleanup is really not needed.

    That's the weird thing, I can't figure out why the row count of msrepl_Commands never grows, but the size of the database does.

    • Marked as answer by Richard498 Sunday, March 11, 2012 4:41 AM
    Tuesday, February 21, 2012 8:51 PM

All replies

  • use sp_who2 to identify the blocking spids and then use dbcc inputbuffer(spid) to see what they are doing.

    The ghost clean up process does remove deleted items from tables and indexes. Expect to see this run continually if you are doing large numbers of deletes.


    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

    Tuesday, February 21, 2012 3:43 PM
  • This session is blocking about 10 others,

    DELETE FROM  MSrepl_commands  WHERE  publisher_database_id = 1   AND article_id = 314   AND   xact_seqno <= 0x001F5F20000041320033  

    The others are the same command with different article_id's.  This is one that's being blocked.

    DELETE FROM  MSrepl_commands  WHERE  publisher_database_id = 1   AND article_id = 297   AND   xact_seqno <= 0x001C2B000001AA2E0004 

    Tuesday, February 21, 2012 5:43 PM
  • Kill these commands.

    They are likely comeing from the distribution clean up agent.

    You may want to run these commands manually like this:

    set rowcount 10000

    declare @rowcount int

    DELETE FROM  MSrepl_commands  WHERE  publisher_database_id = 1   AND article_id = 314   AND   xact_seqno <= 0x001F5F20000041320033 

    set @rowcount=@@Rowcount

    while (@rowcount=10000)

    begin

    DELETE FROM  MSrepl_commands  WHERE  publisher_database_id = 1   AND article_id = 314   AND   xact_seqno <= 0x001F5F20000041320033 

    set @rowcount=@@Rowcount

    end


    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

    Tuesday, February 21, 2012 6:28 PM
  • I disabled the distribution cleanup job because it was running without completing and using the available space. 

    There are only a couple hundred records in msrepl_Commands anyway.  Our ETL server does the deletes, so Distribution Cleanup is really not needed.

    That's the weird thing, I can't figure out why the row count of msrepl_Commands never grows, but the size of the database does.

    • Marked as answer by Richard498 Sunday, March 11, 2012 4:41 AM
    Tuesday, February 21, 2012 8:51 PM
  • I have an update.  We've found the problem and corrected it.  Turned out to be very simple. 

    We had our distributor agent using the verbose history agent profile.  That had a parameter, HistoryVerboseLevel, which was set to 2. 

    I'm not exactly sure what that does, but I changed all existing distribution agents to use the default agent profile which has HistoryVerboseLevel set to 1. 

    It's been a day now, and the version ghost records are being cleared out and database size is remaining small.

    Friday, April 06, 2012 5:05 PM