locked
Determining size of Distribution database RRS feed

  • Question

  •  

    From Replication Help:

     

    For transactional replication, after you configure distribution, we recommend that you:

    ·          Size the distribution database appropriately. Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently. For more information about changing the size of a database, see ALTER DATABASE (Transact-SQL).

    My distribution database was created in the wizard with the following settings:

    .mdf - 140MB Initial size; Autogrow by 1MB unrestricted.

    .ldf - 740MB Initial size; Autogrow by 10 percent, restricted growth to 2097152 MB

     

    Question 1: How were these initial sizes picked by the wizard?

    Question 2: Why is the .ldf file not unrestricted growth?

    Question 3: How do I do this: Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently.

    Question 4: Once I figure out how to do Question 3, am I going to be changing the Initial size of the database or the autogrowth settings? And, if replication is already running, can I modify the database size?

     

    Thanks,

    Linda

     

    Monday, March 10, 2008 7:08 PM

Answers

  • Hi Linda

     

    From your message, I think you are planning to upgrade to 2005 in near future. In that case I would suggest that you run the trial setup on 2005 rather than 2000. This will get you a better feel of how replication works in 2005 and help you avoid migration related headaches.

     

    To your question, as Gopal said, the size will depend on number of transactions and retention period.  I would think that the size you estimate for distribution database will not change materially if you change from 2000 to 2005.

     

    Since disk space is ridiculously cheap nowadays I am curious why you are thinking about size so much. What environment (users, locations, transactions) are you planning the replication for? 

     

    Arun

    Tuesday, March 11, 2008 5:18 PM
  • Hi Linda

     

    I would let it autogrow indefinitely from a sufficiently initial high size. Use full recovery model and setup backups to trim off the TxLog. This will reduce the chances the log would need to autogrow.

     

    When you have a globally distributed high throughput system, autogrow (when called) on distributor can present a latency challenge and you would be right to spend time analyzing the upper bounds in that case.

     

    Arun

    Tuesday, March 11, 2008 5:35 PM

All replies

  • Hi Linda

     

    You should setup the replication in a test environment first with default settings for the size of distribution db. Just make sure you let the data and log files autogrow. Now you need to load the test environment with the typical load of the production environment. To get the typical load try the TSQL Replay trace on the production system. Be warned that you might be slowing the system down.

     

    After a few days of this, look at the size of the distribution database and multiply it by 2. That is the size you want to have in the production deployment of replication.

     

    HTH

    Arun

    Tuesday, March 11, 2008 12:21 AM
  •  

    Question 1: How were these initial sizes picked by the wizard?

    The initial size of the database is dependent on your model database configuration. When SQL Server creates a new database it uses the Model database as the template. When using the wizard we just use the defaults

     

    Question 2: Why is the .ldf file not unrestricted growth?

    Same as above, check your model database configuration

     

    Question 3: How do I do this: Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently.

    Please try Arun's suggestion.

     

    Question 4: Once I figure out how to do Question 3, am I going to be changing the Initial size of the database or the autogrowth settings? And, if replication is already running, can I modify the database size?

    Yes you can.

     

    The bottom line is there are two things that control the size of the distribution database. The transaction load and the retention period. The retention period determines how often the cleanup job cleans the distribution database. The default is 3 days. What this should be set to is dependent on your business requirements and how long you think you need to store data for recovery purposes.

    Tuesday, March 11, 2008 12:43 AM
  •  

    Thank you for these answers. I have one more question. SQL 2000 is currently running on this system. Would it make sense to use the SQL 2000 Distribution database size and Growth settings for the SQL 2005 Distribution database? 

    Linda 

    Tuesday, March 11, 2008 2:05 PM
  • Hi Linda

     

    From your message, I think you are planning to upgrade to 2005 in near future. In that case I would suggest that you run the trial setup on 2005 rather than 2000. This will get you a better feel of how replication works in 2005 and help you avoid migration related headaches.

     

    To your question, as Gopal said, the size will depend on number of transactions and retention period.  I would think that the size you estimate for distribution database will not change materially if you change from 2000 to 2005.

     

    Since disk space is ridiculously cheap nowadays I am curious why you are thinking about size so much. What environment (users, locations, transactions) are you planning the replication for? 

     

    Arun

    Tuesday, March 11, 2008 5:18 PM
  • Arun,

    I am thinking about the autogrow setting more than the size. Sorry for that confusion. I have the impression if your autogrow does not grow in big enough increments, your performance will be degraded. Is that incorrect? Should I just set them to a big autogrow / unrestricted number and stop thinking about it?

    Thanks,

    Linda

    Tuesday, March 11, 2008 5:23 PM
  • Hi Linda

     

    I would let it autogrow indefinitely from a sufficiently initial high size. Use full recovery model and setup backups to trim off the TxLog. This will reduce the chances the log would need to autogrow.

     

    When you have a globally distributed high throughput system, autogrow (when called) on distributor can present a latency challenge and you would be right to spend time analyzing the upper bounds in that case.

     

    Arun

    Tuesday, March 11, 2008 5:35 PM
  • Please rate YES if the post helps

     

    Tuesday, March 11, 2008 8:32 PM
  •  Linda Cornerstone wrote:

     

    From Replication Help:

     

    For transactional replication, after you configure distribution, we recommend that you:

    ·          Size the distribution database appropriately. Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently. For more information about changing the size of a database, see ALTER DATABASE (Transact-SQL).

    My distribution database was created in the wizard with the following settings:

    .mdf - 140MB Initial size; Autogrow by 1MB unrestricted.

    .ldf - 740MB Initial size; Autogrow by 10 percent, restricted growth to 2097152 MB

     

    Question 1: How were these initial sizes picked by the wizard?

    Question 2: Why is the .ldf file not unrestricted growth?

    Question 3: How do I do this: Test replication with a typical load for your system to determine how much space is required to store commands. Ensure the database is large enough to store commands without having to auto-grow frequently.

    Question 4: Once I figure out how to do Question 3, am I going to be changing the Initial size of the database or the autogrowth settings? And, if replication is already running, can I modify the database size?

     

    Thanks,

    Linda

     

    Monday, March 17, 2008 1:00 PM
  •  

    i m unable to log from restricted to unrestrictd

     

    pls advice

    Monday, March 17, 2008 1:03 PM
  • I don't understand this question.

     

    Monday, March 17, 2008 4:50 PM