Distribution database became large

Hi,
I have noticed that distribution database which is used for transactional
replicatiton became large. Can I shrink the database? Transaction log is the
same size as the data file. I run dbcc loginfo(distribution), status field
shows that there are a lot of unused parts. Can I shrink transaction log at
least?
I would make sure the distribution database is in Full recovery model and
dump it frequently.
It is wise to try to size the transaction logs to prevent frequent
autogrows, and it is also wise to try to keep them as small as possible. You
will have to experiment to find the ideal size. 10% of the size of the data
files is frequently as good guess for smaller databases.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
> Hi,
> I have noticed that distribution database which is used for transactional
> replicatiton became large. Can I shrink the database? Transaction log is
> the
> same size as the data file. I run dbcc loginfo(distribution), status field
> shows that there are a lot of unused parts. Can I shrink transaction log
> at
> least?
>
|||Hi, Hilary,
1. Why do you suggest having distribution database in Full recovery model?
2. Is it worth to shrink transaction log of the distribution database? I
guess it has grown up in special case when there was very large transaction
to be delivered.
"Hilary Cotter" wrote:

> I would make sure the distribution database is in Full recovery model and
> dump it frequently.
> It is wise to try to size the transaction logs to prevent frequent
> autogrows, and it is also wise to try to keep them as small as possible. You
> will have to experiment to find the ideal size. 10% of the size of the data
> files is frequently as good guess for smaller databases.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:ADDC4A92-A5A4-4EE5-877A-754D307AC251@.microsoft.com...
>
>
|||1. because I like to maintain the size of my tlogs as much as possible. Full
does this. You can use bulk which offers best performance (except for
deletes) but you have no recoverability. Likewise you can use simple with no
recoverability but the transaction log can grow and as the log grows you get
performance degradation. With Full Recovery model you can set the size to
something and get recoverability and minimize auto grow.
2) Once it is sized adequately you should not have to shrink it. Right now I
think its a must to conserve space and to get a very slight performance
improvement.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Elena" <Elena@.discussions.microsoft.com> wrote in message
news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...[vbcol=seagreen]
> Hi, Hilary,
> 1. Why do you suggest having distribution database in Full recovery model?
> 2. Is it worth to shrink transaction log of the distribution database? I
> guess it has grown up in special case when there was very large
> transaction
> to be delivered.
> "Hilary Cotter" wrote:
|||Thanks, Hilary,
Your answers were very helpful.
Elena
"Hilary Cotter" wrote:

> 1. because I like to maintain the size of my tlogs as much as possible. Full
> does this. You can use bulk which offers best performance (except for
> deletes) but you have no recoverability. Likewise you can use simple with no
> recoverability but the transaction log can grow and as the log grows you get
> performance degradation. With Full Recovery model you can set the size to
> something and get recoverability and minimize auto grow.
> 2) Once it is sized adequately you should not have to shrink it. Right now I
> think its a must to conserve space and to get a very slight performance
> improvement.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Elena" <Elena@.discussions.microsoft.com> wrote in message
> news:CBCAAB13-58C6-407A-A338-040B37BA5961@.microsoft.com...
>
>