• alex_zilberman (11/19/2012)


    Is this will not truncate the log and if it do , what statement will do work that I need?

    No, backup will not truncate the log, nor it will shrink the log file.

    This will shrink the log file if conditions from my previous post are met:

    -- find logical name of the log file

    sp_helpdb 'MyDbName'

    -- Shrink the log to 1000 MB

    CHECKPOINT

    DBCC SHRINKFILE('MyTransactionLog', 1000)

    This could also be useful:

    -- log file size and percentage free

    DBCC SQLPERF (LOGSPACE)

    -- Why the log is not truncated

    select name, log_reuse_wait_desc, recovery_model_desc from sys.databases

    -- oldest active transaction

    DBCC OPENTRAN

    HTH,

    Vedran

    _____________________________________________________
    Microsoft Certified Master: SQL Server 2008
    XDetails Addin - for SQL Developers
    blog.sqlxdetails.com - Transaction log myths