March 27, 2012 at 6:37 pm
Our OLTP database Tlog grown up to 100gb on UAT. Even though recovery model is simple. the database is used for snapshot replication. I was trying to shrink but is fails(i tried in small increments). does any one faced same type of situation??
March 27, 2012 at 7:22 pm
What does the log_reuse_wait gives you if you query sys.databases?
_____________
Donn Policarpio
March 27, 2012 at 7:39 pm
its giving replication 😉
March 27, 2012 at 8:08 pm
Look for open transactions in your database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
March 27, 2012 at 8:13 pm
yes we have open transactions from more than a month
Transaction information for database 'test’.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (43831:51:1)
March 27, 2012 at 8:25 pm
You may want to script out your publication in case you would need to recreate it again. I would drop the publication and disable (sp_replicationdboption) publishing of the db. Then check sys.databases again for the log reuse wait. I've had some cases where running sp_repldone was the last option i had which fixed the problem.
_____________
Donn Policarpio
March 27, 2012 at 8:59 pm
March 28, 2012 at 2:09 am
my question is if we run the sp_repldone on db will it create any other issues like log agents will not pick ?
If you have snapshot replication then you don't have a log reader and the solution to your problem is as the msdn blog post describes
Is there a way without removing the replication can we shrink the log?
Yes, the method described in the blog post that you referenced.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply