Shrinking log file on publisher DB

Question

Shrinking log file on publisher DB

chandrakant_gaurav

Right there with Babe

Points: 799
More actions
April 15, 2010 at 5:06 am

#138484

FOR SQL SERVER 2000 (SP4).
In production environment, with transactional replication configured I am unable to shrink the log size which has grown beyond acceptable size. Disabling the replication, which publishes to subscriber every hour, is not an option.
I am practicing these on locally restored copy of the prod DB.
Here are relevant details:
BACKUP LOG safehelp_data WITH TRUNCATE_ONLY;
error message is - "The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed."
DBCC OPENTRAN
o/p - "Replicated Transaction Information:
Oldest distributed LSN : (3277:75993:1)
Oldest non-distributed LSN : (3277:76013:2)"
There is a coution on BOL about sp_repldone that says "Caution If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions."
Does this mean that the consistency of the subscriber and publisher DBs will be at risk by doing it? I dont know, cant risk, production is at stake.
Is there a workaround, so that, i dont have to disable replication, integrity of data remains intact on publisher as well as subscriber and logfile's size is reduced.
I have run out of all resources to do this, if any of you veterans can help me on this, it will be highly appreciated.
Thanks a lot.

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

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

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Shrinking log file is a bad option.

Make sure you have regular transactional Log back ups. From what I see in your message, there are still some commands that needs to be replicated. you can use sp_browsereplcmds to find out what are the commands that are pending.

-Roy

chandrakant_gaurav Right there with Babe Points: 799 More actions · Answer 2

sp_browsereplcmds is not a recognised command in SQL server 2000. It works on 2005.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

Check sp_replshowcmds on BOL. It should help you

-Roy

chandrakant_gaurav Right there with Babe Points: 799 More actions · Answer 4

I had been unable to accomplish this, but as a work around I created the replication environment in my local on restored production DB backup. As here too i was getting the same error as in production, in order to find no uncommited/unreplicated transactions I went to

Enterprise manager -> Replication Monitor.

As publisher agent was scheduled to run every 1 hour, I waited for it to just complete (kept on refreshing to see its status). Once it succeeded, Immediately I ran the below script

DBCC SHRINKFILE(<Log_file_name>, 1)

BACKUP LOG <DB_name> WITH TRUNCATE_ONLY

DBCC SHRINKFILE(<Log_file_name>, 1)

GO

And it succeeded in shrinking.

This is not the best method for high traffic databases, but this worked for me by chance.

Anyone having a better idea please suggest as I want to create a new job for this shrinking operation.

Thanks

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Do you have Log shipping set up? If not set it up. That would mean that you will be taking log back ups and this will make sure that your log file will not grow. Do you really need to shrink the log file?

-Roy

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 6

chandrakant_gaurav (4/15/2010)
sp_browsereplcmds is not a recognised command in SQL server 2000. It works on 2005.

Refer-->http://msdn.microsoft.com/en-us/library/aa239422(SQL.80).aspx

chandrakant_gaurav Right there with Babe Points: 799 More actions · Answer 7

Yes .

I really need to shrink the log file, it is fast becoming a performance issue.

how will log shipping help me to achieve this?