SQL Server Log File Won't Shrink due cause "log are pending replication" on non replicated DB?
- by user796466
I have a non Mission Critial DB 9am-5pm SQL Server database that I have set up to do nightly full backups and log backups every 30 minutes during business hours. The database is in full recovery and normally I have no reason to truncate/shrink logs unless I do some heavy maintenance. Log backups manage the size with no issue. However I have not been at this client for several weeks and upon inspection I noticed that the log had grown to about 10 times the size of the .mdf file. I poked around backups had been running and I had not gotten any severity error alerts (SQL mail). I attempted to put DB in simple recovery and shrink the log, this was no good. I precede to try a log backup and I got:
The log was not truncated because
records at the beginning of the log
are pending replication or Change Data
Capture. Ensure the Log Reader Agent
or capture job is running or use
sp_repldone to mark transactions as
distributed or captured.
Restart SQL Server rinse repeat same thing ...
I said ??? Replication is not nor ever has been set up on this DB or database /server ??? So the log backups have not been flushing the .ldf. So I did a couple hours of research and I found:
http://www.sqlmonster.com/Uwe/Forum.aspx/sql-server/5445/Log-file-is-not-truncated-inspite-of-regular-log-backup
http://www.eggheadcafe.com/software/aspnet/30708322/the-log-was-not-truncated-because-records-at-the-beginning-of-the-log-are-pending-replication.aspx
seems to be some kind of poorly documented bug ??
The solution seems to have been to run exec sp_repldone, more precisley
EXEC sp_repldone @xactid = NULL,
@xact_segno = NULL, @numtrans = 0,
@time= 0, @reset = 1
This procedure can be used in
emergency situations to allow
truncation of the transaction log when
transactions pending replication are
present. Using this procedure prevents
Microsoft SQL Server 2000 from
replicating the database until the
database is unpublished and
republished. ~ MSDN
When I do that I get the following
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.
Which makes sense Because the DB has never been published for replication.
I have several questions:
A) First and foremost is, WTF is going on ? What is causeing this, I am interested in knowing the why here ? Is this genuinley a bug or is there some aspect of the backup that is not functioning properly that cause's the DB to mimick a replicated state ? Someone please edify me on this.
B) Second ... Do I really have to publish / replicate this DB to exec this SP to fix this ??? Sounds crazy or is there some T-SQL that I can put it in a published state exec the proc and be on my way ...
C) Third, if I do indeed have to publish this database to exec the SP to release this unneeded mis replicated/intended log , to get my .ldf file and backup back on track. How do I publish the database without an online host that it is asking for ??? I don't generally do this kind of database administration and need some guidance.
Sorry if this is too verbose but just voicing the question helps me clarify it ...
Thank you in advance for your help
