July 9, 2009 at 5:45 pm
We have a SQL Server 2000 database that is pretty small in size (7GB) that has a transaction log that is over 17GB in size.
Running DBCC OPENTRAN returns the following information: (Before AND after detach/attach)
DBName OPENTRAN
REPL_DIST_OLD_LSN (315290:1539:114)
REPL_NONDIST_OLD_LSN (315290:1615:1)
I have ran into this problem before which was resolved with sp_removedbreplication, but this time that step did not work.
I was able to detach and re-attach the database - how did a transaction live through that?
This database was copied from a replicated database, but has never itself been replicated.
I remain puzzled...
Suggestions are welcomed, since this is not a production database deleting it IS an option. 🙂
Thanks!
July 9, 2009 at 9:07 pm
Hello Doug,
(As you mentioned it is not a production DB) Have you tried an sp_repldone?
Regards,
John Marsh
www.sql.lu
SQL Server Luxembourg User Group
July 9, 2009 at 9:18 pm
First use : sp_repldone
This will mark the transactions as complete .
after that check the status column in dbcc loginfo .see if there are any 2's at the bottom .Plus run dbcc opentran .If that transaction still exists , rebuild your T-Log .
in 2005 , You can rebuild your t-log only after taking your database to emergency.
-> Stop SQL Server .
-> rename the log file .
-> Start SQL Server.
-> if it creates the new log file on its own ,your issue is resolved.
-> Of not then ,take the database to emergency [alter database set emergency.]
-> detach the database[without emergency it wont allow you].
-> attach the database using create database with option attach_force_rebuild_log option
You are done .
Regards
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 9, 2009 at 10:54 pm
sp_repldone does nothing - since the database is not replicated, it only generates an error.
If the answer was simple, I would not have spent the time posting.
Let's just say I have more experience then you suspect.
Any other suggestions?
July 10, 2009 at 12:25 am
Doug Elwood (7/9/2009)
sp_repldone does nothing - since the database is not replicated, it only generates an error.Any other suggestions?
This happens usually when u restore replicated database. Try setting up replcation once again on that database and then remove it. Once it's successfully removed, backup log will be able to truncate the logs.
July 10, 2009 at 12:38 am
Pradeep,
That is using a bigger hammer then I had planned, but it did work.
Sadly, Microsoft doesn't have a reasonable solution to this. Since the database had never really been replicated, I am certain there was a setting in a system table that I could have modified that would have fixed this.
We have this problem many times over. Nearly all of our production databases are transactionally replicated. When a backup is restored to make a working copy, this problem repeats itself.
Thanks for the help, it resolved the issue.
July 10, 2009 at 12:44 am
Glad it worked 🙂
there must be some setting(s) but i'm unaware. I'd seen a similar situation in the past and this trick worked. sp_repldone too wasn't helping a bit.
July 10, 2009 at 1:49 am
Generally , if we do not use keep_replication option while restoring it should not be an issue .
Sad thing is that in 2005 , you will not see this option in GUI and i have a feeling that its enabled by default .So it takes the replication stuff .Or its a bug if it is not enabled by default .
In 2008 this option is visible so if you dont check it , replication bits should not be available ...I hope 🙂
Abhay Chaudhary
Sr.DBA (MCITP/MCTS :SQL Server 2005/2008 ,OCP 9i)
July 29, 2009 at 2:03 pm
Have you tried changing the category value for that database under sysdatabases to 0. It must be non-zero as you said you were getting errors related to "replication is enabled" on your database.
MJ
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply