Shrinking of the ldf file in a transactional replicated environment.

Question

Shrinking of the ldf file in a transactional replicated environment.

BertusJ

SSC Enthusiast

Points: 112
More actions
March 30, 2010 at 4:36 am

#386604

We have set up transactional replication in a SQLServer 2005 standard edition between two locations.
Everything work fine except that the .ldf file at the subscriber site is growing and growing at this moment the size is more then 80GB?
My question is how can I shrink the size of this file.
The information which I can find at internet is for a standalone databases and does not work in our situation.
By the way the data is available for the users at the subscriber site. I think that the ldf should be a few MB, or am I wrong?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 1

BertusJ (3/30/2010)
We have set up transactional replication in a SQLServer 2005 standard edition between two locations.
Everything work fine except that the .ldf file at the subscriber site is growing and growing at this moment the size is more then 80GB?
My question is how can I shrink the size of this file.
The information which I can find at internet is for a standalone databases and does not work in our situation.
By the way the data is available for the users at the subscriber site. I think that the ldf should be a few MB, or am I wrong?

Hello,

When you say that the data is available for the users at the subscriber site --> it means that there there are replicated transactions in the transaction log that are no longer useful or valid !

For this you can use sp_repldone command to tell the publisher that the transactions are sent at the distributor.

For more reference see BOL:

http://msdn.microsoft.com/en-us/library/ms173775%28SQL.90%29.aspx

This will solve your problem to truncate log " but use with caution and test before you roll it in production "

HTH,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

mangeshd Right there with Babe Points: 744 More actions · Answer 2

Have you setup regular log backups at the subscriber site? If you set log backups, then the space in the transactions log will be reused and will stop the Log file autogrowth. As far as subscriber is concerned, the changes flowing in from distributor are logged as normal DML operations so regular backup strategy should take care of this issue.

Also keep in mind the suggestions given in the earlier post.

BertusJ SSC Enthusiast Points: 112 More actions · Answer 3

Thanks for your responses.

The problem we have with the backup is caused by de size of the transaction log file and the limited disk space.

I ‘m working on the solution of Mr or Mrs. 500 but I haven't a test environment were I can simulate the error.

(by the way I have 13 years experience as a Oracle dba and also 13 years experience with replication, but our

SQLserver dba has left the company and I should take over his work, so my experience with SQLserver is not so much)

So far as I can see is that the solution will drop all transactions in the transaction log file historical transactions and even the actual transactions. The risk is that we will lose the actual transactions, is this correct?

Here a briefly explanation of our setup

The distributor and publisher are connected by a satellite. So the communication is not always stable.

I think that sometimes the feedback to the distributor fails and that the log file is not clean up correctly.

Does some have the same experience and found a solution?

mangeshd Right there with Babe Points: 744 More actions · Answer 4

I will suggest not to try the LOG TRUNCATE_ONLY option. Rather, find a disk with enough space and take subsriber db backup and then shrink it. After this, set up an effective backup strategy and you will be fine.

Also, you Oracle experience will help, don't worry, replication is not that difficult to understand.

BertusJ SSC Enthusiast Points: 112 More actions · Answer 5

Problem has been solved now.

Before I started I made a full backup of both databases.

Then I have carried out the instruction of Mr or Mrs. 500 Thanks for that.

First I verified that all the actual transaction was processed.

After execute the sp_repldone procedure and use the shrink instruction of the ldf file

The size decreased from 80GB to 76 GB, not spectacular.

Then I made a full backup and did the shrink job again, now the file was indeed decreased to 30MB which is acceptable.

Now we have enough HDD space again for the backup.

Thanks for your assist

Bert

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 6

Hello,

Thanks for posting back as this can help others having similar problem to get resolved.

Glad could help !

Thanks,

\\K

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂