Log File keep growing and not getting truncated and shrink even Log backups are running fine.

Question

Log File keep growing and not getting truncated and shrink even Log backups are running fine.

Imran-Yasir

SSCrazy

Points: 2044
More actions
January 8, 2015 at 10:31 am

#314340

Hello Team, I have following situation and will appreciate any help.
Current Status:
Database is in Full recovery mode, log backups are running hourly fine, no any kind of replication running, CDC is disabled. I had database snapshot of a production database but I have dropped that snapshot too under Databases-->Database Snapshots
Issue:
Log file has grown to 120gb and was not getting truncated even successful log backups
I ran following command and found Replication under column log_reuse_wait_desc
SELECT name, log_reuse_wait_desc FROM sys.databases
then I ran EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
and saw that log file has truncated mean it has 99% free space but even than shrink does not work
Ran again following command:
EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time= 0, @reset = 1
Now ran following script to remove replication though I don't have any running on this server:
sp_removedbreplication [dbname]
I ran SELECT name, log_reuse_wait_desc FROM sys.databases again to check if Replication is done but still is there.
Any idea why Replication is not disappearing under the column log_reuse_wait_desc FROM sys.databases
and why I am not able to shrink the database please?
Thank for your time and help
Imran

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Create a transactional replication publication, publish a single article (small table). Set it not to create a snapshot (no point). Once complete, drop the publication. See if that removes the REPLICATION wait

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Imran-Yasir SSCrazy Points: 2044 More actions · Answer 2

Gail, I appreciate your quick response to help me but I will prefer not to do such kind of big changes in production database for testing. Can you suggest any other solution please?

I am a consultant here and don't want to do major changes without approval and I know management will not allow to setup replication.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

It's not permanent replication setup, it's to fix leftover pieces of replication.

What I've seen several times is that sp_removedbreplication throws an error saying the DB's not replicated, but there are replication log waits, and this setup and drop of a tiny article cleans up any leftover bits of replication that didn't get cleaned up properly.

Just first make very, very sure that there really is no replication and no CDC.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Imran-Yasir SSCrazy Points: 2044 More actions · Answer 4

Gail, There is no replication for sure but we had enabled CDC which I have disabled now. There is a client process which create database snapshot twice a week, do you think that database snapshot may cause that replication wait on in log file?

Thank you for your continues replies and help