March 9, 2008 at 12:06 pm
I have a large database (137 GB) that for which we recently set up transactional replication to a single subscription. The second machine is configured as the distributor and the subscriber. The transaction log for this database has now grown to 171 GB. Periodic transaction log backups with "truncate the transaction log" selected, followed by a log file shrink do not free space (that used to work just fine); the log just keeps growning and we will (shortly) run the machine out of disk space. The symptoms look like inactive transactions are not being truncated or none of the transactions are become inactive...? Replication Monitor is not reporting any errors and performance is listed as "Excellent" with latency 1-4 seconds. Q: is there something I missed in the setup of transactional replication that should be freeing up transactions so the transaction log can be backed-up, truncated and shrunk?
Very much appreciate suggestions; I'm at the point of having to shut down replication to prevent the transaction log from running out of space and halting my production database.
March 9, 2008 at 12:12 pm
jwilley (3/9/2008)
I have a large database (137 GB) that for which we recently set up transactional replication to a single subscription. The second machine is configured as the distributor and the subscriber. The transaction log for this database has now grown to 171 GB. Periodic transaction log backups with "truncate the transaction log" selected, followed by a log file shrink do not free space (that used to work just fine); the log just keeps growning and we will (shortly) run the machine out of disk space. The symptoms look like inactive transactions are not being truncated or none of the transactions are become inactive...? Replication Monitor is not reporting any errors and performance is listed as "Excellent" with latency 1-4 seconds. Q: is there something I missed in the setup of transactional replication that should be freeing up transactions so the transaction log can be backed-up, truncated and shrunk?Very much appreciate suggestions; I'm at the point of having to shut down replication to prevent the transaction log from running out of space and halting my production database.
No errors is good, but is Replication actually happening? Test this to make sure (change something at the source, then go to the target and see if it shows up).
I mention this because the only thing that I can think of in Replication that would cause this is if the Log Reader is not running.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 9, 2008 at 8:55 pm
replication working great -- latency reported by Replication Monitor is about 4 seconds. My measured preformance was a bit more than that but records added to the primary are definitely replicating to the secondary database just fine.
March 10, 2008 at 9:35 am
Do you have any open transactions? It can only truncate up to the more recent uncommitted transaction.
dbcc opentran
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 10, 2008 at 5:29 pm
hmm -- never heard of dbcc opentran before -- it gives the following:
Transaction information for database 'A64-Prod'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (76368:64:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I confess I don't know what this output means (not a first with DBCC, I must say). In a busy database, aren't there likely to always be transactions in the process of being distrubuted? Do I have to shutdown/suspend Replication to truncate my log?
March 10, 2008 at 6:25 pm
Is that possibly backwards?!?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 10, 2008 at 6:40 pm
Does this command give you any messages?
Backup Log [A64-Prod] With Truncate_Only
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 2:17 pm
I am having the same problem. My log database has grow 93 GB in a day and a half. I using transaction replication to replicate the execution of INSERT, UPDATE, and DELETE stored procedures.
I executed:
DBCC OPENTRAN(my_data)
Here are the results:
Replicated Transaction Information:
Oldest distributed LSN : (122116:136899:30)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
I recently upgraded to SQL 2005 from SQL 2000. I did not have this probelm in 2000.
December 30, 2008 at 2:20 pm
I believe that this means that the Log Reader is not running or is not running against your database.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 2:24 pm
If the log reader wasn't running, then data wouldn't be replicated...correct? The data is being replicated all my subsribers are receiving all insert, update, and delete executions.
December 30, 2008 at 2:47 pm
Hmm, must be an old open transaction then.
Execute this command:
select *
from master..sysprocesses
where dbid = db_id(N'your DB')
Look at the "Last Batch" time for any processes with open transactions. Do any of them look excessively old?
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
December 30, 2008 at 3:30 pm
Are you backing up you transaction logs ?
* Noel
December 30, 2008 at 4:11 pm
My last batch is 2 minutes old.
I back up my transaction log every night.
December 30, 2008 at 4:15 pm
cpailma (12/30/2008)
My last batch is 2 minutes old.I back up my transaction log every night.
you should do it more often!
* Noel
December 30, 2008 at 4:21 pm
How often should I back it up? What are the implications to my system? Will the user experience any slowness or timeouts while it the log backup is running?
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply