Transaction Log Issue -- Please Help

Question

Post reply

Transaction Log Issue -- Please Help

Jeff Mayer

SSCrazy

Points: 2458
More actions
December 6, 2011 at 5:46 am

#261068

I have a Transaction log for one of my databases that after backing it up, only gets to about 40% free and then fills up very quickly. It is currently about 390GB which is way too large. I have other companies that a basically the same databases and the logs there are about 500MB. When I run: SELECT NAME,log_reuse_wait_desc FROM sys.databases I get replication as the result. We have never used replication and my thoughts are this is why I can not completely clear the log after a back up. All other databases either return Nothing or Log Backup. Please help!!!

Viewing 15 posts - 1 through 15 (of 25 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

was this database originally restored from somewhere else?

try running sp_removedbreplication against the database

---------------------------------------------------------------------

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 2

There could be many reasons for it.

Factors That Can Delay Log Truncation

http://msdn.microsoft.com/en-us/library/ms345414.aspx

Jeff Mayer SSCrazy Points: 2458 More actions · Answer 3

According to everyone I have asked, the answer is no! We have not used replication......

george sibbald SSC Guru Points: 104210 More actions · Answer 4

Jeff Mayer (12/6/2011)
According to everyone I have asked, the answer is no! We have not used replication......

SQL thinks you have 🙂

try running sp_removedbreplication against the database

---------------------------------------------------------------------

salum SSCommitted Points: 1598 More actions · Answer 5

check this.

http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx#bm1161456

java[/url]

Jeff Mayer SSCrazy Points: 2458 More actions · Answer 6

I am trying to run sp_removedbreplication, but the log is currently full and I am out of disk space.. I am hoping I can get out IT to give me a little more disk space to try this. I agree... There is a reason it is showing replication!!!!

george sibbald SSC Guru Points: 104210 More actions · Answer 7

are you able to back the log up to free some space within the log?

Once you have some space if removedbreplicaton does not do it try the trick advised by Gail shaw (aka gilamonster) in the link provided by Salum.

---------------------------------------------------------------------

Jeff Mayer SSCrazy Points: 2458 More actions · Answer 8

I did get enough diskspace to run the stored procedure, but it did not clear the entry for log_reuse_wait_desc in sys.databases

george sibbald SSC Guru Points: 104210 More actions · Answer 9

first of all, make sure the growth factor on the log file is not set to something which is going to gobble up all your disk space (ie the default 10%)

try a quick log backup and check log_reuse again. If it still says replication

then:

using the wizard,Set the server up for replication, create a publication in the DB in question. Publish any table (not important which one) and then completely remove replication again.

I would say this database was restored from somewhere with replication at some point.

---------------------------------------------------------------------

Jeff Mayer SSCrazy Points: 2458 More actions · Answer 10

I tried the option of switching the database to simple mode as recommended in the other post. However, that did not resolve the issue. I have never set up replication, so I am a bit nervous about going down that route. Any other suggestions!

george sibbald SSC Guru Points: 104210 More actions · Answer 11

nope, its a fairly well known fix for this issue.

using the wizard setting up replication is very simple, only go as far as setting up a publisher and article, keep it simple and just accept the defaults, you wont be adding any subscribers so its safe, you won't be any worse off than you are now.

Try out the process in test first so you feel comfortable with it. Even better copy the database with the issue to test and try it out, to confirm it removes the replication completely

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

Jeff Mayer (12/6/2011)
I tried the option of switching the database to simple mode as recommended in the other post. However, that did not resolve the issue. I have never set up replication, so I am a bit nervous about going down that route. Any other suggestions!

Simple recovery's not going to help in the slightest if the reason for the log not truncating is replication. You need to address the root cause, which is replication, as George has indicated.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 13

A second vote for Gail's post. You need to determine the reason the log is growing. If it's replication, then you need to clear that or solve the issues before it will clear.

If you are uncomfortable going this route, then hire a consultant to help you. I can help you find remote people if you need it, but trying to make this a simple thing for you to solve might result in more problems. Do it right and determine the root cause and fix that. It probably won't take long, but if you can't do it, then don't make things work. hire someone for an hour or two to help you solve it and teach you a few things.

Jeff Mayer SSCrazy Points: 2458 More actions · Answer 14

Thanks for all the help with this! Unfortunately at this point, my hands are completely tied by IT!!! The log file is now up to 450GB.

Thanks again!!