August 18, 2011 at 3:25 am
Hi,
We had an issue with db inconsistencies that I had to fix using DBCC CHECKTABLE (table, REPAIR_ALLOW_DATA_LOSS). This fixed the errors (albeit whilst losing a row).
However, this morning I noticed that our free space on our log files drive had reduced dramatically. Upon investigating this the log file was almost 30GB in size and had not been truncated since I ran the above process. The database uses the simple recovery model (and the db is in 80 compatibility mode) and as such should automatically truncate at the previous checkpoint.
I first checked for open transactions using DBCC OPENTRANS. This revealed that there were no open transactions.
I then looked at log_reuse_wait_desc. I saw that the two databases that I had ran the process on (test, live) were both showing as REPLICATION. The db does not (and can not) use replication at all.
To fix, I ran sp_removedbreplication against both db's which corrected the issue as per this thread:
http://www.sqlservercentral.com/Forums/Topic695034-357-1.aspx#bm1046961
My question is this though:
What may have caused this to happen in the first place? There have been no restores run, and the only thing i can think of is that I had to set the db to single_user and then back to multi_user in order to repair.
Could that be the issue?
August 18, 2011 at 3:51 am
No, repair would not have caused this.
Is there snapshot replication?
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 18, 2011 at 5:47 am
No replication. sys.databses is_publisher and is_subscriber both = 0
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply