locked
log file space is not getting freed up even after log backup happened in log shipping RRS feed

  • Question

  • Log shipping is configured in my environment & sec. db is in restoring mode.

    There is one db issue whose log space is not free even after log backup. everything is OK. log backup is also happend at   regular interval & log is also applied on sec databases. then why log size of database on PROD is remain same as it cause a issue, when i checked in sys.databases then log_reuse_wait _desc column shows "replication" but replication is not configured.

    I fired checkpoint also but no luck.

    what is the reason why log is not getting freed.

    Wednesday, September 10, 2014 12:31 PM

Answers

  • Try to run sp_removedbreplication on that particular database.   Remember it will entirely wipe out all publications on that particular DB.   See if it helps. Also check what percent of log is actually using by the database when compared to actual allocated space.  If it is not using entire allocated space,   try to shrink it manually.
    • Marked as answer by Mayur-DEW Monday, September 22, 2014 11:32 AM
    Wednesday, September 10, 2014 9:59 PM
  • Thanks everyone for helping me, it was really surprised  that though replication was not there still it shows REPLICATION. even i ran sp_repldone but its not working. finally

    SP_removedbreplication works, i don't know how? but after executing it REPLICATION is gone from log_reuse_wait column. Then I took manual log backup & shrunk the log file. My problem resolved.

    Thanks Bhanu .. this is the resolution of my problem.

    • Marked as answer by Mayur-DEW Thursday, April 28, 2016 1:06 PM
    Monday, September 22, 2014 11:35 AM

All replies

  • If the log_reuse_wait_desc says replication, then some remnants of transactional replication is still there and it will wait for the log reader agent to scan the records from transaction log before it can be truncated. Since you are not using replication, you can issue the following command against the DB in question, to clear it:

    exec sp_repldone @xactid = NULL, @xact_seqno = NULL, @numtrans = 0, @time = 0, @reset = 1


    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 10, 2014 12:56 PM
  • does it affecting my current PROD server & current log shipping. because when i read this on MSDN it says below., that's why i am little bit afraid to execute this & there  no any replication was configured.

    If you execute sp_repldone manually, you can invalidate the order and consistency of delivered transactions. sp_repldone should only be used for troubleshooting replication as directed by an experienced replication support professional

    Wednesday, September 10, 2014 1:09 PM
  • Sp_repldone will not adversely affect Log Shipping. If you are using replication (have active publications), then it is definitely not recommended to run it but since you cited that you are not using replication, you can safely it.

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 10, 2014 1:13 PM
  • i executed it but it gives message "unable to execute procedure, database is not published"
    Wednesday, September 10, 2014 1:28 PM
  • There is no good solution that I know of, other than to enable your DB for publication  by executing the following and then run sp_repldone and then disable it again (perhaps Hilary Cotter or others can suggest here). You may also have to configure the distributor if not already done before enabling this DB for publication.

    use master; 
    exec sp_replicationdboption @dbname = 'DBName', @optname = 'publish', @value = 'true'
    GO


    Satish Kartan http://www.sqlfood.com/


    Wednesday, September 10, 2014 1:40 PM
  • as i said earlier there is no replication cofigured only log shipping is there. I have put this DATAbase is SIMPLE recovery but still log size remain same. its really surprising why this happened.

    why its not releasing space.? pls help

    Wednesday, September 10, 2014 2:53 PM
  • You either had CDC enabled or replication setup temporarily. As far as I know, you won't be able to release the space unless you address this by running sp_repldone

    Satish Kartan http://www.sqlfood.com/


    Wednesday, September 10, 2014 3:04 PM
  • I think you guys are mixing up different AH solutions. Log shipping has nothing to do with replication.

    Mayur, the log files of a database are ALWAYS truncated after a log backup, but you need to shrink the file manually in order to release the free space to the operating system. Otherwise the freed space will remain allocated for the ldf file and used as needed.

    There are two ways to truncate the log. The first one is by using the management studio interface. Right-click the database and click on tasks -> Shrink -> Files. Select "log" under file type, ensure "free unused space" is selected and click on "ok".

    The other is through t-sql:

    DBCC SHRINKFILE (N'logical_file_name' , 0, TRUNCATEONLY)

    But you need to know the logical file name you want to truncate. This can be obtained by querying the following system catalog, where the name column is the logical name:

    select * from sys.database_files

    Hope to have helped.


    OBS.: Do NOT attempt to do this with a data file in a production environment during comercial hours, as it will generate a lot of I/O throughput and index fragmentation.
    Wednesday, September 10, 2014 3:07 PM
  • I think you guys are mixing up different AH solutions. Log shipping has nothing to do with replication.

    Mayur, the log files of a database are ALWAYS truncated after a log backup,

    .

    Samir,

    I dont believe in nitpicking from the answer and pointing, so consider this as edit to your answer. But just to add a point to answer since you emphasized on always. Its not always guaranteed that log backup would truncate logs. If a long running transaction uis running especially one with begin tran and commit it would hold the log hostage and would not allow it to truncate. This happens quite often when you do huge delete operation or index rebuild.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles


    Wednesday, September 10, 2014 3:14 PM
  • The log_reuse_wait_desc is showing REPLICATION - this is reason the virtual log files within the T-Log file are not being able to be reused - so you won't be able to shrink it unless you address this issue (the originator mentioned that the DB is in simple recovery mode and he already issued a manual checkpoint). 

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 10, 2014 3:34 PM
  • its not working guys even simple recovery model not freed up space, when i checked log reuse desc column shows still "replication"  its really surprised why its there if replication is not there & if model is change to simple
    Wednesday, September 10, 2014 4:20 PM
  • Since you mentioned that there is no replication configured in this database, I suspect that this is a bug. I've seen something similar in SQL Server 2005 but have not seen it in SQL Server 2008 and later versions. Here's what you need to do. Configure the database as a replication publisher just for the purpose of marking it for replication. You can use the wizard to do this and just select a small table; keep it as simple as you possibly can. After the database has been configured for replication, delete the publication. This will inform SQL Server that the database is no longer marked for replication. Run DBCC LOGINFO on your database to verify that the VLFs have been marked as inactive.

    Edwin Sarmiento SQL Server MVP | Microsoft Certified Master
    Blog | Twitter | LinkedIn
    SQL Server High Availability and Disaster Recover Deep Dive Course


    Wednesday, September 10, 2014 4:20 PM
  • As I suggested before, enable the DB for publication, run sp_repldone, disable the DB publication, then you should be able to shrink:

    There is no good solution that I know of, other than to enable your DB for publication  by executing the following and then run sp_repldone and then disable it again. You may also have to configure the distributor if not already done before enabling this DB for publication.

    use master; 
    exec sp_replicationdboption @dbname = 'DBName', @optname = 'publish', @value = 'true'
    GO


    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 10, 2014 4:24 PM
  • I think you guys are mixing up different AH solutions. Log shipping has nothing to do with replication.

    Mayur, the log files of a database are ALWAYS truncated after a log backup,

    .

    Samir,

    I dont believe in nitpicking from the answer and pointing, so consider this as edit to your answer. But just to add a point to answer since you emphasized on always. Its not always guaranteed that log backup would truncate logs. If a long running transaction uis running especially one with begin tran and commit it would hold the log hostage and would not allow it to truncate. This happens quite often when you do huge delete operation or index rebuild.


    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles


    Shanky,

    I'm sorry but I don't understand what you mean by nitpicking. I believe that to have a productive discussion about a subject, we need to be willing to exchange different opinions right?

    As for your observation, you are correct. It's not guaranteed that log backup will truncate the entire log because there could be running transactions, but it will always truncate the inactive virtual log files. I believe what you mean by holding the log hostage by a long running query is because the transaction spans over all existing VLFs in the log files, and therefore there are no inactive VLF to be using in a truncation operation even if there are entries for comitted transactions in them.

    Back to the subject, as I had emphasized before, a log backup will always truncate the log (this is why I think its counter-productive to view the issue from the perspective "hey, the log is not shrinking"), but yeah, only for the inactive VLFs, so if the VLFs are really marked for replication, they won't be counted as inactive. Run DBCC LOGINFO to confirm it. This may be a bug.

    But then again, I didn't see any evidence that Mayur has already tried to actually shrink the file. What happens when this is done?

    Wednesday, September 10, 2014 6:14 PM
  • Do not confuse the physical size of the log file with the log file reusability.

    The physical size of any file never decreases, unless you manually shrink it or have auto-shrink enabled (which is not recommend).

    Wednesday, September 10, 2014 6:16 PM
  • Mayur - Some folks are assuming that you did not run shrink file command (DBCC SHRINKFILE). Can you clarify? I believe you are not able to shrink it because of the reason in log_wait_reuse column, which is REPLICATION. So, we need to address this as I mentioned before.

    Satish Kartan http://www.sqlfood.com/

    Wednesday, September 10, 2014 9:55 PM
  • Try to run sp_removedbreplication on that particular database.   Remember it will entirely wipe out all publications on that particular DB.   See if it helps. Also check what percent of log is actually using by the database when compared to actual allocated space.  If it is not using entire allocated space,   try to shrink it manually.
    • Marked as answer by Mayur-DEW Monday, September 22, 2014 11:32 AM
    Wednesday, September 10, 2014 9:59 PM
  • I'm sorry but I don't understand what you mean by nitpicking. I believe that to have a productive discussion about a subject, we need to be willing to exchange different opinions right?

    As for your observation, you are correct. It's not guaranteed that log backup will truncate the entire log because there could be running transactions, but it will always truncate the inactive virtual log files. I believe what you mean by holding the log hostage by a long running query is because the transaction spans over all existing VLFs in the log files, and therefore there are no inactive VLF to be using in a truncation operation even if there are entries for comitted transactions in them.

    Back to the subject, as I had emphasized before, a log backup will always truncate the log (this is why I think its counter-productive to view the issue from the perspective "hey, the log is not shrinking"), but yeah, only for the inactive VLFs, so if the VLFs are really marked for replication, they won't be counted as inactive. Run DBCC LOGINFO to confirm it. This may be a bug.

    But then again, I didn't see any evidence that Mayur has already tried to actually shrink the file. What happens when this is done?

    By nitpicking i mean I dont pick a word from users reply and try to argue on that about its correctness but just wanted to make sure OP is aware. I agree to fact that in your previous answer you meant in all correct way. This portion of your answer is more correct as you mentioned about truncation of inactive log portion.

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My Technet Articles

    Thursday, September 11, 2014 9:28 AM
  • Thanks everyone for helping me, it was really surprised  that though replication was not there still it shows REPLICATION. even i ran sp_repldone but its not working. finally

    SP_removedbreplication works, i don't know how? but after executing it REPLICATION is gone from log_reuse_wait column. Then I took manual log backup & shrunk the log file. My problem resolved.

    Thanks Bhanu .. this is the resolution of my problem.

    • Marked as answer by Mayur-DEW Thursday, April 28, 2016 1:06 PM
    Monday, September 22, 2014 11:35 AM