Results 1 to 5 of 5

Thread: how to truncate transaction log

  1. #1
    elena Guest

    how to truncate transaction log


    SQL Server 6.5
    Hi!
    We have trans.log 200MB in total and 71 MB free space.
    I run DBCC OPENTRAN and it shows no active transactions exist.
    I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
    What to do to get space back?

    Thank you,
    Elena.

  2. #2
    IVO Guest

    how to truncate transaction log (reply)

    DUMP TRAN <database name> WITH NO_LOG
    This should run clearly.


    ------------
    elena at 8/1/01 10:57:59 AM


    SQL Server 6.5
    Hi!
    We have trans.log 200MB in total and 71 MB free space.
    I run DBCC OPENTRAN and it shows no active transactions exist.
    I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
    What to do to get space back?

    Thank you,
    Elena.

  3. #3
    Steve Guest

    how to truncate transaction log (reply)

    IVO

    I am having to truncate my log file about every three to four days.

    When running the command that you posted, will this truncate the log of all the transactions. The command that I have in my schedule task:

    DUMP DATABASE FieldProProduction TO FieldProProductionBULog WITH NOUNLOAD , STATS = 10, INIT , NOSKIP

    If I add the WITH NO LOG, will this truncate properly.

    Thanks, I am truely a beginner in this.
    Steveb


    ------------
    IVO at 8/2/01 1:31:34 AM

    DUMP TRAN <database name> WITH NO_LOG
    This should run clearly.


    ------------
    elena at 8/1/01 10:57:59 AM


    SQL Server 6.5
    Hi!
    We have trans.log 200MB in total and 71 MB free space.
    I run DBCC OPENTRAN and it shows no active transactions exist.
    I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
    What to do to get space back?

    Thank you,
    Elena.

  4. #4
    IVO Guest

    how to truncate transaction log (reply)

    No Steve, you must add next schedule task with command DUMP TRAN FieldProProduction WITH NO_LOG. But one thing - do you regular backup of transaction log ? Do you need it ? If not, you should set an database option truncate log on checkpoint to true and you have no further problems with traansaction log. But this will be ok only if you don&#39;t need to do up-to-time restores.


    ------------
    Steve at 8/2/01 12:05:17 PM

    IVO

    I am having to truncate my log file about every three to four days.

    When running the command that you posted, will this truncate the log of all the transactions. The command that I have in my schedule task:

    DUMP DATABASE FieldProProduction TO FieldProProductionBULog WITH NOUNLOAD , STATS = 10, INIT , NOSKIP

    If I add the WITH NO LOG, will this truncate properly.

    Thanks, I am truely a beginner in this.
    Steveb


    ------------
    IVO at 8/2/01 1:31:34 AM

    DUMP TRAN <database name> WITH NO_LOG
    This should run clearly.


    ------------
    elena at 8/1/01 10:57:59 AM


    SQL Server 6.5
    Hi!
    We have trans.log 200MB in total and 71 MB free space.
    I run DBCC OPENTRAN and it shows no active transactions exist.
    I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
    What to do to get space back?

    Thank you,
    Elena.

  5. #5
    Sunny Guest

    how to truncate transaction log (reply)

    when you truncate log in 6.5 you will geet an incorrect report in the EM, through NT performance monitor or sp_spaceused.YOu will have to run DBCC CHECKTABLE (syslogs). read the result. then check . YOU rEM will show the correct space. Read the article Q183100 in TechNet or MSDN.

    Hope this helps.


    ------------
    elena at 8/1/01 10:57:59 AM


    SQL Server 6.5
    Hi!
    We have trans.log 200MB in total and 71 MB free space.
    I run DBCC OPENTRAN and it shows no active transactions exist.
    I run DuMP TRANSACTION .... WITH TRUNCATE_ONLY and it doesny clean log also.
    What to do to get space back?

    Thank you,
    Elena.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •