May 30, 2008 at 12:27 pm
I have a database that is about 45 GB and the transaction log is 18 GB and keeps growing. The recovery model is Full and we do weekly full backups, nightly diff, and hourly transaction log backups on this database.
I've tried to run DBCC Shrinkfile on the transaction log with no success. When I check the log_reuse_wait_desc value in the sys.databases table it has a value of "REPLICATION". I don't understand why it has that value since this database is not, nor has never been, set up for replication.
Also, when I execute DBCC OPENTRAN, I receive this message:
Transaction information for database 'dbname'.
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (42959:110:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Can anyone explain to me why it appears that this database is set up for replication and how I can fix it? Any help is much appreciated.
Thanks!
Wendy Schuman
May 30, 2008 at 12:34 pm
No idea what cuased it.. but try to run the following and see it resolves...
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1
MohammedU
Microsoft SQL Server MVP
May 30, 2008 at 3:58 pm
When I execute that stored procedure, I get the following message:
Msg 18757, Level 16, State 1, Procedure sp_repldone, Line 1
Unable to execute procedure. The database is not published. Execute the procedure in a database that is published for replication.
Do you have any other suggestions?
Thanks.
Wendy Schuman
May 30, 2008 at 4:04 pm
Did you run the procedure in the same db where you have the issue...?
MohammedU
Microsoft SQL Server MVP
May 30, 2008 at 4:14 pm
Yes, I did. I don't really understand what's going on here, but I did find these posts. Although it seems that the bug they're referring to was happening in RTM and SP1. We're on SP2.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=959032&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=654902&SiteID=1
I guess I'll try their workaround. Luckily, we have the same issue on both our QA site and Production sites, so I can test on QA. Although, QA is set up in Simple recovery model. At this point, I don't know what else to do.
Thanks.
Wendy Schuman
June 2, 2008 at 11:11 am
I was able to fix my problem by doing the following:
exec sp_removedbreplication 'dbname'
Backup the log file
Execute DBCC SHRINKFILE
The log_reuse_wait_desc now says 'NOTHING' and I was able to shrink the log file.
It's still a mystery as to how it got set to 'REPLICATION' in the first place though.
Wendy Schuman
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply