April 7, 2010 at 10:06 am
I'm an accidental DBA so bear with me. Our database is down due to a full transaction log. I know how to backup and shrink trans logs but it is not responding. So I tried to do a full backup and got a message indicating that led me to realize that replication is turned on and affecting the log_reuse_wait setting.
Users are urgently walking past my door.
Can anyone help please?
April 7, 2010 at 2:55 pm
Use DBCC opentran to help identify the problem. Replication is likely the culprit.
April 7, 2010 at 2:56 pm
You are right, replication is the problem. But I cannot delete the Publication because of the "transaction log is full" error message. Any ideas?
April 7, 2010 at 3:02 pm
One hack would be to add another log file on a different volume, if you have the space. You can also try to stop the log reader, issue the appropriate sps (repldone and replflush I think). You may need to reinitialize the subscriptions afterwards.
April 7, 2010 at 3:14 pm
Cindy,
is replication actually set up for the database?
If it isn't follow the workarounds in this thread
http://www.sqlservercentral.com/Forums/Topic509307-146-1.aspx
another workaround is to quickly set up a dummy replication and then remove it.
another question, is the log full because the drive is full or it has a max size setting or a growth factor thats too big? as suggested you could add another log on a different drive till you fix this.
If replication is set up are the replication agents running?
---------------------------------------------------------------------
April 7, 2010 at 4:14 pm
I was able to make the max size of the trans log file bigger by 10 GB to give me some room to breathe.
Now I'm working on the replication again. This database is the Publisher to another computer to act as a "hot backup". This database is also a Subscriber to two other replications.
I need to turn off replication so that I can backup/shrink the trans log, correct? That is where I'm heading now.
Thanks,
Cindy
April 7, 2010 at 4:26 pm
Not necessarily. As soon as the transactions have been delivered to the distribution database (assuming it's transactional replication), you should be able to truncate the log.
http://msdn.microsoft.com/en-us/library/ms151740%28v=SQL.100%29.aspx
April 7, 2010 at 4:48 pm
turning off replication won't help, as this database is published you need to identify the transaction preventing the log truncating. Use dbcc opentran whilst connected to the database to do this, then use sp_repldone to clear it.
BTW replication is the least suitable way to provide a hot backup, especially if that is all it is for, a backup for failover and not used in reporting. Investigate logshipping or mirroring instead.
---------------------------------------------------------------------
April 7, 2010 at 5:07 pm
I have run dbcc opentran several times over several minutes with the same results:
Transaction information for database 'WMS'.
Replicated Transaction Information:
Oldest distributed LSN : (467268:6722:6)
Oldest non-distributed LSN : (467268:6965:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Where do I determine the "stuck" transaction.
BTW, thanks for the advise on the logshipping or mirroring. Actually, my advise to the CTO was to hire/contract with a real SQL Server DBA. It is not something that I can squeeze into my already overflowing schedule. But I'll sure pass it onto whomever that turns out to be.
April 7, 2010 at 5:38 pm
OK, a little time with BOL and I ran this.
exec sp_repldone @xactid=NULL, @xact_seqno=NULL, @numtrans=0, @time=0, @reset=1
Then I was able to back up and shrink the trans log.
Now I need to unpublish and republish the database. More BOL for me.
Thanks to everyone who assisted on this endeavor. I really appreciate your help.
Cindy
April 8, 2010 at 3:33 am
Glad we got there.
why the need to unpublish/republish? would a reinitialise not do?
What rates you offering for the job? :-D:-)
---------------------------------------------------------------------
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply