September 8, 2009 at 7:44 am
Hi
we have a database which is published for replicition.
The database is about 13 Gb. The transaction log is currently 131Gb with 31% free space.
There is e neat maintenance plan, backup runs, but the transaction log does not shrink.
When i use t-sql DBCC SHRINKFILE (nav_sepia_Log, 1000); it doesn't want to because he says the files is in use.
When we do not longer publish the database the maintenance plan runs just fine.
But setting up replication everytime costs us also time.
Anyone got a clue what could be the solution ?
Kind regards
El jefe
JV
September 8, 2009 at 8:14 am
The log backup cannot let the transactions be removed until they move to the distributor and subscriber. I'm not sure which log you're talking about, but if you have log backups, then the log space should get reused. A backup does not shrink the transaction log.
And you should not be shrinking the transaction log. Find out how large it needs to be between backups (log backups) and set it there with some pad.
September 8, 2009 at 8:20 am
In the maintenance plan a full back up is made, but no space is freed.
It's now running up to 131Gb
We make a backup once a day.
When is it moved to the distributor ? When the replication is finished ?
JV
September 8, 2009 at 8:45 am
You first need to read this about transaction logs: . A full backup does not clear your transaction log. You need to run a log backup, not a full backup. You need both, but a log backup marks the space in the transaction log as free.
Depending on how often you run a log backup, that will determine how large your transaction log needs to be. I'd suggest you run them every hour or two, find the largest size of the backup across a few days, and then set your log size to be a touch larger.
It should not grow or shrink during normal operations.
If the transactions make it to the distribution database, the log space can get reused. If not, the log will grow.
September 8, 2009 at 9:17 am
ok, thx !!
i'm already running a trasnaction log backup
Thought a full backup did it !
Many thanx
JV
September 8, 2009 at 9:57 am
Hi
Did the log backup now.
It's about 75Gb.
Transaction logs remaing 141Gb with about 30% free
What now ?
JV
September 8, 2009 at 2:43 pm
I seem to recall a similar problem with someone else. I think they had unreplicated transactions, so the transactions could not be removed from the log.
September 8, 2009 at 3:37 pm
SELECT name, log_reuse_wait_desc FROM sys.databases ORDER BY name;
That will show you what is keeping log records active.
Let us know what it says.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 12:41 am
This is the result :
distribution NOTHING
master NOTHING
model NOTHING
msdb NOTHING
NAV_FIN_SEPIA ACTIVE_TRANSACTION
NAV_SEPIA REPLICATION
ReportServer NOTHING
ReportServerTempDB NOTHING
TEMP_BO NOTHING
tempdb CHECKPOINT
JV
September 9, 2009 at 12:44 am
jvElecenter (9/9/2009)
NAV_SEPIA REPLICATION
So, records which need replicating are keeping the log active.
Is replication working and up to date from this database?
(I read back to post 1 to find the name of the database, and can't be bothered to check again for replication)
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 12:57 am
Paul
It is the right database and my guess was indeed that it was due to replication.
As it seems so the replication runs well.
Snapshot has completed and is not running. Status is ok.Don't know where to look for anything else for this problem but I know it has to be due to replication. Because when we stop publishing everything seems to be working just fine.
Kind regards
J
JV
September 9, 2009 at 1:01 am
Yes but it is transactional replication - not snapshot replication.
Have changes on the source (since the snapshot was applied) been successfully applied to the subscriber(s)?
Use replication monitor to check this, look for any errors, undistributed commands, and the latency.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
September 9, 2009 at 1:23 am
as pointed out in previous posts, you need to ensure the log reader agent is continuously bringing the transactions to the distribution database. Unless the log reader agent brings the transactions to the distribution db, you wont be able to truncate the log.
log_resuse_wait_descr column clearly stated that it was replication what was preventing the log file to be truncated.
September 9, 2009 at 1:24 am
Paul
Yes it is applied to :
Delivered snapshot from the 'unc\SQL02SEPIA_NAV_SEPIA_NAVISION DATA TO TSD_DAILY\20090908220061\' sub-folder in 20236000 milliseconds
Applied the snapshot to the Subscriber.
No errors to report.
JV
September 9, 2009 at 1:28 am
ps (9/9/2009)
as pointed out in previous posts, you need to ensure the log reader agent is continuously bringing the transactions to the distribution database. Unless the log reader agent brings the transactions to the distribution db, you wont be able to truncate the log.log_resuse_wait_descr column clearly stated that it was replication what was preventing the log file to be truncated.
Thanks! I was running out of ways to say it :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply