Log file will not shrink

Posted on 2006-05-31
Last Modified: 2012-05-05

I have a log file that has grown huge (someone set it to grow automatically, I know, bad).  Anyway, I'm trying to shrink it using the DBCC SHRINKFILE command in conjunction with the BACKUP LOG command.  When I run the shrinkfile it completes fine but doesn't shrink the file at all.  I ran DBCC LOGINFO and it's returning over 350 rows all with status 2.  The other thing is this DB is being replicated to (it's a subscriber).  Any help would be appreciated.

The results of the DBCC SHRINKFILE is below:

Cannot shrink log file 2 (LiveDW_Log) because all logical log files are in use.
DbId   FileId CurrentSize MinimumSize UsedPages   EstimatedPages
------ ------ ----------- ----------- ----------- --------------
21     2      17810560    128         17810560    128

And when I run the BACKUP LOG:
The log was not truncated because records at the beginning of the log are pending replication. Ensure the Log Reader Agent is running or use sp_repldone to mark transactions as distributed.

(1 row(s) affected)


Question by:mrbass78
    LVL 32

    Expert Comment

    Someone answered this for me a while back and it worked perfectly:
    LVL 3

    Accepted Solution

    Had the same problem. Transactions in the begining of the file are preventing you from truncating the log. Never has time to verify but I think it is due to the fact that alter/new published objects were in the publisher but not in the subscriber. Once I dropped the subscriber the log shrank right away since replication wasn't be done anymore. I will recreate the subscriber if possible. If not use the sp_repldone stored proc to mark the transactions has no longer needed for replication.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    Join & Write a Comment

    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    731 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now