DanielBlais
asked on
SQL Server 2005 log size & replication issue
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
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
ASKER
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...
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?
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?
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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,