locked
Transaction log clear /reuse RRS feed

  • Question

  • Hi We had a DB and was crash due to block corruption. We run DBCC CHECKDB ,DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) . Now I need to know This command will clear the transaction log(ldf) file on the DB? Reason was I need to find cause of this error by reading ldf file through a tool.  .  Secondly when its happened a DB "check point", will that clear the transaction and release freee space?

     


    • Edited by ashwan Friday, June 29, 2018 9:54 AM
    Friday, June 29, 2018 9:45 AM

Answers

  • Thank you very much on the reply. On simple recovery mode only truncate T log by Checkpoint only? Is that correct?

    Yes. But remember, a CHECKPOINT doesn't occur if there's a long-running transaction and there is no space in the log to free up even if the log grows more than 70%. This is one condition where the log is not truncated even if the log goes past 70% full.

    ON FULL REcovery mode, T log can reuse or over write when Tlog backup is perform? is that correct. Other than that will not reuse or overweight t log space.

    Yes. On every log backup, the "recoverable" portion of the Tlog is made "reusable" (aka log truncation) and can be used for subsequent log records. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by ashwan Friday, June 29, 2018 9:32 PM
    Friday, June 29, 2018 12:14 PM

All replies

  • None of the commands will Change the Content of the log file. At a Checkpoint SQL Server write all dirty pages (Change data paiges) from Memory to disk.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 29, 2018 10:01 AM
  • Hi We had a DB and was crash due to block corruption. We run DBCC CHECKDB ,DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) . Now I need to know This command will clear the transaction log(ldf) file on the DB? Reason was I need to find cause of this error by reading ldf file through a tool.  . 

    Secondly when its happened a DB "check point", will that clear the transaction and release freee space? 


    Checkdb when used with repair_allow_data_loss in some cases does changes contents of log file for recovering it but does not truncates the transaction log. In this process it throws out as much content as possible from transaction log to bring database in recovery so you might not really get what caused it. I also doubt the reason for corruption is mentioned in SQL Server transaction log. That is why it is repeatedly said use repair as LAST LAST option. If you have some TP tool there is no point in using repair at all.

    The reason for corruption is most likely bad storage in 99% case this is the issue. Look into event viewer for more details.

    Checkpoint simply forces dirty pages from memory to disk, that is all. A dirty page is one which has changed from what it was on disk before it was brought into memory


    Cheers,

    Shashank

    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 Wiki Articles

    MVP


    Friday, June 29, 2018 10:10 AM
  • Just an addition to the other posts: CHECKPOINT does truncate the log if you are in simple recovery more for the database. But in that mode, you cannot rely on anything being in the log in the first place, of course.

    Tibor Karaszi, SQL Server MVP (Web Blog)

    Friday, June 29, 2018 10:38 AM
  • Hi We had a DB and was crash due to block corruption. We run DBCC CHECKDB ,DBCC CHECKDB (DBNAME, REPAIR_ALLOW_DATA_LOSS) . Now I need to know This command will clear the transaction log(ldf) file on the DB? Reason was I need to find cause of this error by reading ldf file through a tool.  .  Secondly when its happened a DB "check point", will that clear the transaction and release freee space?


    1. All repairs are fully logged. Now, if your DB is running in Auto-truncate mode (Simple recovery) and if the log file is 70% full by the repair operation itself, a CHECKPOINT is triggered and the relevant portion of the log file is truncated.

    2. Yes, a CHECKPOINT does trigger a log file to be truncated if running in Simple recovery model and also, as mentioned above, if the log is 70% full.

    3. No, Log truncation is just a logical operation. No space is released to OS. Shrink, on the other hand, releases space. 

    So it's possible that what you are now looking to read is already gone from the log.

    Hope this helps.


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Friday, June 29, 2018 11:08 AM
  • You will not find the root cause of your problem by looking at the SQL Server transaction log.  Block corruption is 99% of the time caused by hardware problems.   Although possible, I have not seen SQL Server service corrupt a file since SQL 2005 SP3 was released.

    You need to look at your hardware and determine the root cause.

    Friday, June 29, 2018 11:16 AM
  • Hi Mohsin

    Thank you very much on the reply. On simple recovery mode only truncate T log by Checkpoint only? Is that correct? 

    ON FULL REcovery mode, T log can reuse or over write when Tlog backup is perform? is that correct. Other than that will not reuse or overweight t log space .

    many thanks

     

    Friday, June 29, 2018 11:56 AM
  • Hi Mohsin

    Thank you very much on the reply. On simple recovery mode only truncate T log by Checkpoint only? Is that correct? 

    ON FULL REcovery mode, T log can reuse or over write when Tlog backup is perform? is that correct. Other than that will not reuse or overweight t log space .

    many thanks

     

    Mohsin already wrote that and since u asked I will put again in simple recovery trn log is truncated when checkpoint fires or when log file grows 70% of its size. In both cases if there is long running transaction log truncation would be delayed.

    Yes correct for full recovery ONLY when you take transaction log backup the trn log is truncated and again a long running transaction can prevent log truncation like in simple recovery. Whenever  trn log is successfully truncated in both recovery models space will be reutalized.

    Hope that is crystal clear


    Cheers,

    Shashank

    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 Wiki Articles

    MVP

    Friday, June 29, 2018 12:05 PM
  • Thank you very much on the reply. On simple recovery mode only truncate T log by Checkpoint only? Is that correct?

    Yes. But remember, a CHECKPOINT doesn't occur if there's a long-running transaction and there is no space in the log to free up even if the log grows more than 70%. This is one condition where the log is not truncated even if the log goes past 70% full.

    ON FULL REcovery mode, T log can reuse or over write when Tlog backup is perform? is that correct. Other than that will not reuse or overweight t log space.

    Yes. On every log backup, the "recoverable" portion of the Tlog is made "reusable" (aka log truncation) and can be used for subsequent log records. 


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by ashwan Friday, June 29, 2018 9:32 PM
    Friday, June 29, 2018 12:14 PM
  • Hi Shanky

    Thank you for the reply! what do you mean by "TP tool "?

    Friday, June 29, 2018 8:23 PM
  • Hi Shanky

    Thank you for the reply! what do you mean by "TP tool "?

    I would assume he meant Third Party tool.

    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.


    Friday, June 29, 2018 8:38 PM
  • Hi Shanky

    Thank you for the reply! what do you mean by "TP tool "?

    Yes it meant third party tool

    Cheers,

    Shashank

    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 Wiki Articles

    MVP


    Saturday, June 30, 2018 10:08 AM
  • Hi Shashank  I am keen on that tool . Can you provide info on this please
    Monday, July 2, 2018 5:04 AM
  • Hi Shashank  I am keen on that tool . Can you provide info on this please
    Please click on My Technet Wiki Articles and on page that opens you have my mail id. Let us continue this on private mail.

    Cheers,

    Shashank

    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 Wiki Articles

    MVP

    Monday, July 2, 2018 6:36 AM