> Hello everyone,
>
> I am having a problem with transaction logs on my subscriber server in a
> snapshot/transactional replication environment. My publisher (server A)
> has
> six (6) databases available for subscription. My subscriber (server B)
> pulls
> it's subscription from server A for reporting purposes every two hours.
> The
> log files (db.ldf) and the transaction log backup files are enormous. I've
> attempted truncating the logs, but they won't budge. I have also tried
> skrinking to files, but that doesn't work either. This is my first venture
> into replication, and I finally got it to work correctly, only to find out
> that my reporting server stopped this morning because it was out of space.
>
> For now, I dropped the databases and restored them from a backup (forcing
> the server to recreate new files for the MDF and LDF). This works, but I
> do
> not want to do this every two weeks. Any suggestion on how I can get the
> log
> files truncated on a regualr basis or generally reduced in size overall?
>
> Thanks in Advance.

>I presume this could be done from a script and scheduled with the SQL
>Server
> Agent to occur at the beginning and end of the snapshot replication. For
> instance, set the database to bulk-logged before replication - let the
> replication occur - set the database back to full-recovery model. Is that
> right?
>
> If so, I think I can handle that part. The part I have a question about is
> dropping or dumping the log every 15 minutes. I'm a bit of a novice at
> this
> stuff, so would you mind elaborating a bit on that portion of your
> suggestion?
>
> Lastly, I think I can handle shrinking the log file 2 or more times. I can
> do that from a scheduled task at the completion of the replication event
> and
> just add it to the above series of events, right?
>
> Thanks a bunch for your assistance.
>
> "Hilary Cotter" wrote:
>
>> Hello Lucas. I would advise you to put the subscriber database into bulk
>> logged recovery model when the snapshot is being applied. After that put
>> it
>> in full recovery model and dump the log every 15 minutes or so.
>>
>> You will need to shrink the log 2-4 or possibly more times to get it to a
>> manageable size after putting it in full recover model.
>>
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>>
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>>
>> 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
>>
>>
>>
>> "Lucas Davenport" <Lucas Davenport@discussions.microsoft.com> wrote in
>> message news:DD8789BC-EE71-4B71-8C24-0489ABC004ED@microsoft.com...
>> > Hello everyone,
>> >
>> > I am having a problem with transaction logs on my subscriber server in
>> > a
>> > snapshot/transactional replication environment. My publisher (server A)
>> > has
>> > six (6) databases available for subscription. My subscriber (server B)
>> > pulls
>> > it's subscription from server A for reporting purposes every two hours.
>> > The
>> > log files (db.ldf) and the transaction log backup files are enormous.
>> > I've
>> > attempted truncating the logs, but they won't budge. I have also tried
>> > skrinking to files, but that doesn't work either. This is my first
>> > venture
>> > into replication, and I finally got it to work correctly, only to find
>> > out
>> > that my reporting server stopped this morning because it was out of
>> > space.
>> >
>> > For now, I dropped the databases and restored them from a backup
>> > (forcing
>> > the server to recreate new files for the MDF and LDF). This works, but
>> > I
>> > do
>> > not want to do this every two weeks. Any suggestion on how I can get
>> > the
>> > log
>> > files truncated on a regualr basis or generally reduced in size
>> > overall?
>> >
>> > Thanks in Advance.
>>
>>
>>

> Thanks for all your help - I really appreciate it. In your last reply, you
> mentioned not shrinking the file too much on SQL 2000. I forgot to mention
> in
> my original post tha I am doing replication between SQL 2000 (Server A)
> and
> SQL 2005 (Server B). That doesn't change anything you mentioned, does it?
>
> Again, thanks for all your help.
>
>
> "Hilary Cotter" wrote:
>
>> the command to dump the log is
>>
>> Backup log DatabaseName to disk='test.bak'
>>
>> TO change the recovery model you can do an
>> alter database databaseName
>> set RECOVERY FULL
>>
>> alter database databaseName
>> set BULK_LOGGED
>>
>> You don't want to shrink the log too much in SQL 2000. Shrink it to the
>> point where it doesn't autogrow between dumps.
>> --
>> Hilary Cotter
>> Director of Text Mining and Database Strategy
>> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
>>
>> This posting is my own and doesn't necessarily represent RelevantNoise's
>> positions, strategies or opinions.
>>
>> 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
>>
>>
>>
>> "Lucas Davenport" <LucasDavenport@discussions.microsoft.com> wrote in
>> message news:14B7EE07-0074-461F-BB04-6851E9A91C0B@microsoft.com...
>> >I presume this could be done from a script and scheduled with the SQL
>> >Server
>> > Agent to occur at the beginning and end of the snapshot replication.
>> > For
>> > instance, set the database to bulk-logged before replication - let the
>> > replication occur - set the database back to full-recovery model. Is
>> > that
>> > right?
>> >
>> > If so, I think I can handle that part. The part I have a question about
>> > is
>> > dropping or dumping the log every 15 minutes. I'm a bit of a novice at
>> > this
>> > stuff, so would you mind elaborating a bit on that portion of your
>> > suggestion?
>> >
>> > Lastly, I think I can handle shrinking the log file 2 or more times. I
>> > can
>> > do that from a scheduled task at the completion of the replication
>> > event
>> > and
>> > just add it to the above series of events, right?
>> >
>> > Thanks a bunch for your assistance.
>> >
>> > "Hilary Cotter" wrote:
>> >
>> >> Hello Lucas. I would advise you to put the subscriber database into
>> >> bulk
>> >> logged recovery model when the snapshot is being applied. After that
>> >> put
>> >> it
>> >> in full recovery model and dump the log every 15 minutes or so.
>> >>
>> >> You will need to shrink the log 2-4 or possibly more times to get it
>> >> to a
>> >> manageable size after putting it in full recover model.
>> >>
>> >> --
>> >> Hilary Cotter
>> >> Director of Text Mining and Database Strategy
>> >> RelevantNOISE.Com - Dedicated to mining blogs for business
>> >> intelligence.
>> >>
>> >> This posting is my own and doesn't necessarily represent
>> >> RelevantNoise's
>> >> positions, strategies or opinions.
>> >>
>> >> 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
>> >>
>> >>
>> >>
>> >> "Lucas Davenport" <Lucas Davenport@discussions.microsoft.com> wrote in
>> >> message news:DD8789BC-EE71-4B71-8C24-0489ABC004ED@microsoft.com...
>> >> > Hello everyone,
>> >> >
>> >> > I am having a problem with transaction logs on my subscriber server
>> >> > in
>> >> > a
>> >> > snapshot/transactional replication environment. My publisher (server
>> >> > A)
>> >> > has
>> >> > six (6) databases available for subscription. My subscriber (server
>> >> > B)
>> >> > pulls
>> >> > it's subscription from server A for reporting purposes every two
>> >> > hours.
>> >> > The
>> >> > log files (db.ldf) and the transaction log backup files are
>> >> > enormous.
>> >> > I've
>> >> > attempted truncating the logs, but they won't budge. I have also
>> >> > tried
>> >> > skrinking to files, but that doesn't work either. This is my first
>> >> > venture
>> >> > into replication, and I finally got it to work correctly, only to
>> >> > find
>> >> > out
>> >> > that my reporting server stopped this morning because it was out of
>> >> > space.
>> >> >
>> >> > For now, I dropped the databases and restored them from a backup
>> >> > (forcing
>> >> > the server to recreate new files for the MDF and LDF). This works,
>> >> > but
>> >> > I
>> >> > do
>> >> > not want to do this every two weeks. Any suggestion on how I can get
>> >> > the
>> >> > log
>> >> > files truncated on a regualr basis or generally reduced in size
>> >> > overall?
>> >> >
>> >> > Thanks in Advance.
>> >>
>> >>
>> >>
>>
>>
>>

Paul Ibison wrote:
> Lucas,
> if you have tried truncating the log and there's no change in space used
> then there's definitely an issue and running DBCC OPENTRAN will let you know
> if there is an open transaction which is blocking the truncate. If there is,
> then run DBCC INPUTBUFFER to see what it is doing and make a decision
> whether to remove it (KILL) or not. After that, i If you aren't bothered in
> point-in-time recovery then I'd advise Simple Recovery Mode to make your
> life easy. If you need point in time recovery, schedule a frequent log
> backup and this should be enough.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .