August 26, 2009 at 2:06 pm
This is a QA box - Not sure what happened just noticed that it was low on space so I went to help clean it up. Problem 1 never get involved unless it's yours 🙂
Anyway noticed there was a db with 100gb of log space but set to simple. Okay figure someone saw a problem switch it didnt cleanup.
Tried shrinking back the file(s) why are there two ldf's who knows, couldnt shrink.
Ran an DBCC OPENTRAN
Replicated Transaction Information:
Oldest distributed LSN : (461:15258:4)
Oldest non-distributed LSN : (461:15273:1)
I saw one transaction from replication (this is a subscriber that is pushed to) so I stopped the distributor agent and the transaction ended cleanly.
Tried shrinkfile again on both files no good.
Ran DBCC OPENTRAN - same response.
took the db offline and brought it back online
Same results big tran log file(s)
OPENTran has same results
no spids pointing to the db
What am I missing - I know i had a couple to drink last night but not that much?
John Zacharkan
August 26, 2009 at 7:36 pm
What's the result from DBCC LOGINFO? Are the active virtual log files (status = 2) at the end of the files?
I think the first thing you need to do is shrink the second file with EMPTYFILE - and get rid of it. Once that is done, then you should be able to shrink the first file. Just a guess...
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 26, 2009 at 9:19 pm
Yeah we tried getting rid of either of the files without success. From what i can tell it looks like this is backup of the publisher database while it was configured as a publisher with transactions yet to be delivered to the Distributor.
This is QA not worth much time - I would be curious if anyone else might had ever seen this.
Hilary - thanks for taking time out on this, there's only so much you can do over messager am as mention I don't want to waste anymore of your time.
Cheers all.
John Zacharkan
August 27, 2009 at 7:44 am
If this was a copy of a database that was setup with replication - I would bet anything that it still thinks replication is set up.
I believe the process to clear that is to set it up to replicate, then remove replication again. Once that is done - you should be able to truncate the transaction logs and get rid of them.
I don't have the procedure for doing that right now, but if you search the forums here you should be able to find something.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 27, 2009 at 8:01 am
Create a transactional replication publication with at least one article in it. Once you've gone all the way through the wizard and the publication has been created, drop the publication that you've just created.
Then check log_reuse_wait_descr in sys.databases as well as DBCC OPENTRAN and see if the reference to replication has gone. It should have.
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
August 27, 2009 at 9:33 am
Thanks Gail - that was what I remember from another article, but couldn't remember the steps to be done.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply