February 21, 2016 at 5:30 pm
Hi All ,
I have an issue with my database transaction log . it increases very quick , only a few days and FULL ( 99% )
This database involved in replication
What I did to reduce the log file is by doing this :
1. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 ( Marking Replication as Read Only )
2. BACKUP LOG I3_IC TO DISK='NUL:'
3. Change recovery mode from FULL to SIMPLE
4. Shrink
5. Re-initialize Replication
It was successfully reduce the log file but I need to find out the cause / Culprit of this issue .
The profile of the database is :
Autogrowth
-Data file , by 500 MB Unrestricted growth
-Log file , by 500 MB Restricted growth to 2TB
I am wondering the cause of this issue
Any feedback are highly appreciated !
Thank you
February 22, 2016 at 2:04 am
http://www.sqlservercentral.com/articles/Transaction+Logs/72488/
My first guess would be full recovery model, no log backups.
This may also be useful
http://www.sqlservercentral.com/articles/Administration/64582/
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
February 22, 2016 at 3:59 am
Is replication failing? If so, even if you have log backups, that would cause the log to fill. Do you have log backups? That wasn't mentioned. In addition to reading Gail's links, you might want to read this one[/url].
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 28, 2016 at 4:16 pm
Hi guys
Many thanks for the reply . Recovery mode is FULL
Log backup is running well every 3 hours and I notice the replication is failed . Log is full again yesterday and I need to run these statement again :
1. EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1 ( Marking Replication as Read Only )
2. BACKUP LOG I3_IC TO DISK='NUL:'
3. Change recovery mode from FULL to SIMPLE
4. Shrink
5. Re-initialize Replication
Below are the errors that I got from log history :
2016-02-28 22:04:31.281 Status: 4096, code: 20024, text: 'Initializing'.
2016-02-28 22:04:31.281 The agent is running. Use Replication Monitor to view the details of this agent session.
2016-02-28 22:04:31.281 Status: 0, code: 20011, text: 'The process could not execute 'sp_repldone/sp_replcounters' on XYZ.'.
2016-02-28 22:04:31.281 The process could not execute 'sp_repldone/sp_replcounters' on XYZ.
2016-02-28 22:04:31.281 Status: 0, code: 18752, text: 'Only one Log Reader Agent or log-related procedure (sp_repldone, sp_replcmds, and sp_replshowcmds) can connect to a database at a time. If you executed a log-related procedure, drop the connection over which the procedure was executed or execute sp_replflush over that connection before starting the Log Reader Agent or executing another log-related procedure.'.
2016-02-28 22:04:31.281 Status: 0, code: 22017, text: 'The process could not set the last distributed transaction.'.
2016-02-28 22:04:31.281 Status: 0, code: 22037, text: 'The process could not execute 'sp_repldone/sp_replcounters' on XYZ.'.
Any feedback are much appreciated
February 29, 2016 at 1:59 am
The broken replication is your problem. You need to fix or remove it, or the log will carry on growing.
And stop messing with the recovery model of the DB. Every time you do that you break the log chain and limit your ability to restore the DB should you need to. The problem is replication, so go fix the replication.
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
February 29, 2016 at 8:25 pm
Thanks Gail . I will take a look if i can fix or remove it
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply