Solved

SQL Server 2005 log size & replication issue

Posted on 2008-06-11
5
395 Views
Last Modified: 2011-10-03
Hi Guys

I have a problem that occur times to times.  My transaction log are full and taking all disk space.  The workaround we find is :
stop transaction log reader agent
run EXEC sp_repldone ...
shink log
restart transaction log reader agent
reinitialize replication

But this take some hours,

My question is What cause this appen and how to prevent is to occur in the future?

If I define 10 transaction log of 10Go each, is I'll be able to shrink some of them while other are locked?

Thanks
0
Comment
Question by:DanielBlais
  • 3
  • 2
5 Comments
 
LVL 2

Expert Comment

by:knowledge_riot
ID: 21766977
Are you taking regular backups of your transaction logs? Backing up a transaction log automatically truncates the inactive transactions.

See: http://support.microsoft.com/kb/873235
You can set up a transaction log backup in Management Studio.

In our databases, the transaction logs are backed up every 15 minutes and stored in a separate directory to allow us to recover the database, should it ever be required,
0
 
LVL 2

Author Comment

by:DanielBlais
ID: 21769382
In this situation it doesn't work.  Putting the database in simple recovery model and truncating log file doesn't work either.  The only way we find it works is to stop the log reader agent and run EXEC sp_repldone...
0
 
LVL 2

Expert Comment

by:knowledge_riot
ID: 21776688
I have never used sp_repldone
From http://msdn.microsoft.com/en-us/library/ms173775.aspx  ....
"If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only be used for troubleshooting replication as directed by an experienced replication support professional."

Did you once have the database set up for replication? Do you need to have it set up for replication? Have you tried taking a full backup of the database and recreating it to see if that resolves the problem?
0
 
LVL 2

Author Comment

by:DanielBlais
ID: 21778660
I know this invalidate the replication.  After we use that we reinitialize all replication.

The database have replication since 3 years but the problem began since 4 or 5 month.  It doesn't occur frequently but cause downtime.

What we know, when this occur, the log file can growth from 1go to 80go in a half of hour!, even if the database is in simple recovery model.  Full backup + log backup and/or and dbcc shrinkfile has no effect (on the log size).

We are not sure but it seem to appear the there a very high number of small transaction on a small period.
0
 
LVL 2

Accepted Solution

by:
DanielBlais earned 0 total points
ID: 23420414
The solution to my problem was, in the Log Reader Agent, to

increase the PacketSize, pollingInterval and ReadBatchSize parameters.

Here my entire log reader agent command :

-Publisher [GWS-SQL-1] -PublisherDB [wws] -Distributor [gws-sql-1] -DistributorSecurityMode 1  -Continuous -PacketSize 16384 -pollingInterval 10 -ReadBatchSize 2000
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Sql Permission 6 52
How to query LOCK_ESCALATION 4 40
Move SQL 2005 Express to Server 2012R2 19 104
Can Unique column have more than one Null? 8 44
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now