January 22, 2010 at 5:36 pm
Consider the following scenario:
You copy a database from SQL 2000 and restore to 2008. When the database was on 2000, it was published for replication, but when you restored it on 2008, you did not restore with keep_replication options. Now the log is growing and will not shrink. SELECT name, log_reuse_wait_desc FROM sys.databases shows replication is the reason for the log reuse not happening, but you've not setup replication on this DB yet.... maybe you've not even setup replication on this system period!
Here's how to fix. Follow the following procedure:
1. change the compatibility mode to 2008
ALTER DATABASE [foo] SET COMPATIBILITY_LEVEL = 100
2. sp_dboption 'foo-database name','published','true'
If this fails with The Distributor has not been installed correctly , go to step 5. If successful, go on. (this means replication has never been setup on your server... but no worries..we will do that in step 5)
3. Run these:
sp_repldone NULL, NULL, 0, 0, 1
sp_removedbreplication
4. Shrink the log as you would normally
Run: sp_removedbreplication
5. Setup a new publication based on the database. Choose a small article and use snapshot.
6. Now delete the publication you just setup
7. Shrink the log as your normally would and regrow it appropriately
I didn't find much on this exact issue, so I hope this helps if you have this problem.
January 25, 2010 at 7:31 am
Thanks, but who names a database foo???
January 25, 2010 at 7:49 am
Have you encountered this on 2000 systems that were not participating in a replication setup? Or was this just a former publisher?
I've done a lot of 2005 to 2008 upgrades, and I've done a couple 2000 to 2008 upgrades, but I haven't run across this yet, but I'm going to book mark it just in case.
Twitter: @SQLBalls
Blog: http://www.SQLBalls.com
Channel: https://www.youtube.com/@Tales-from-the-Field
January 25, 2010 at 10:45 am
I have not seen this exact problem on SQL 2000 restores. I have encountered a similar problem in 2000 where when you restore the database that was published, it shows as being published, but no detail appears in repliation monitor. in this case I had to completely remove all publications, remove replication and thus delete the distribution database.
The other problem I've had in 2000 is where all agents, publications, and subscribers are running fine but I still get red X's in replication monitor. This happens when you use the “Preserver the Replication settings” (AKA WITH KEEP_REPLICATION). To solve, I had to do the following:
run sp_MSload_replication_status against the distribution database. You may have to run it more than once.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply