Monday, August 21, 2006 10:24 PM
I changed a database from replication to non-replicationg, but sys.databases stil shows
log_reuse_wait column =6 (REPLICATION). Consequently the log file keeps growing and am not able to shrink it. Any body know how to fix this ?
Tuesday, August 22, 2006 3:31 PMModeratorHow did you remove replication? If you do exec sp_dboption inside the database, is one of the rows returned equal to "published"? If so, the database is still published for transactional replication, which is the reason your log is still growing.
Tuesday, August 22, 2006 8:33 PM
Using the Man. Studio.
First dropeed subscriptions
Then disabled database for publication
When I run exec sp_dboption 'dbname', 'published' returns
Tuesday, August 22, 2006 8:38 PMModerator
Since you removed replication altogether for the database, you can try sp_removedbreplication, although i don't think it will make a difference at this point.
Did you back up the transaction log? If the database recovery model is set to FULL, you can't reduce the size of the file until you back up the transaction log. If the databse recovery model is set to SIMPLE, but the log size continues to grow, then can you verify you don't have any large transactions running in the background?
Wednesday, August 23, 2006 9:37 PM
The DB is in simple mode. I dont see any log running transaction
I also did do sp_removedbreplication. dbcc open tran (see below) seems to indicate that some how the db is still marked for replication !
sys.datbases also shows is_published=0
dbcc opentran gives
Transaction information for database 'Campaigns'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (926374:31:1)
Wednesday, August 23, 2006 10:11 PMModerator
What build are you using, RTM or SP1? There was a bug similar to what you were describing in RTM that was fixed in SP1, but only happens under certain conditions.
A quick workaround is to re-enable your database for replication, add a publication, then drop the publication, and remove replication. If this doesn't work, let me know and we can try something else.
Wednesday, August 23, 2006 10:50 PM
That seemed to work.
I finally was able to shrink the log file. Now I have to watch if file growth starts again. The log_reuse_wait has also changed to 1(NOTHING).
By the way the build is SP1 (9.00.1399.06) without the hot fixes.
Thanks a whole lot !.
Wednesday, August 23, 2006 11:03 PMModeratorI'm glad it worked, if you can you should upgrade to SP1, there are many other bugs that have been fixed as well. You quoted build 1399.06, with is RTM, SP1 would be build 9.00.2047.
Wednesday, August 23, 2006 11:57 PM
Ah.. yes this is a dev box that did not get updated. Thanks for catching the gaff.
Version 9.00.2153.00 is getting ready for production. Hope there are no issues on that one!!!
Wednesday, March 14, 2007 12:00 AMThis issue still exists in the SP1 hopefully they have fixed it in SP2 lets see. *** it almost killed my prod box.
Tuesday, May 27, 2008 4:43 PMHi All,
I have a problem similar to this one. I can't run my regularly scheduled maintenance backup for this db. The message is log_reuse_wait column =6 (REPLICATION).
This db is technically no longer in replication - the old replication job was deleted, the subcription was removed from the subscriber db, but when I try to delete the subscription and the publication from the publisher db, I get the error:
"The transaction log for db 'mydb' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases."
So - I can't delete the old replication or alter the dboptions since the trans log is full, and I can't shrink the trans log because the db is still listed as being in replcation.
Can someone please help?
Tuesday, May 27, 2008 5:53 PMModeratorif you can't shrink it, can you try increasing the size of the log file then? THen there will be enough space needed to do the other operations that require log space.
Thursday, May 29, 2008 2:18 PMThank you! That worked. After that I was able to delete the subscription. I needed to run "exec sp_removedbreplication 'mydb' to delete the publication.
Wednesday, June 29, 2011 10:36 PMI just had this problem in SQL 2008 with SP2. The answer was to run sp_removedbreplication and then I could shrink the logfile. Thank you so much for this information!!