SQL Server Performance

How to truncate log in SQL Server 2008?

Discussion in 'SQL Server 2008 Questions [Archive Only - Closed]' started by pcsql, Oct 1, 2008.

  1. pcsql New Member

    I want to replace the coding Backup Log with Truncate_Only in a sql script. If I understand correctly, I need to do the following:
    1. alter database MyDatabase set recovery simple
    2. checkpoint
    Does setting the recovery model cause a checkpoint or I need to issue checkpoint statement manually?
    After the database is set to simple recovery model, what is the difference between executing checkpoint statement and backup log with truncate_only in terms of SQL Server 2005?
    Thanks.
  2. ghemant Moderator

    Backup log with trucate_only is no longer supported in SQL 2008. If your database is in bulk-logged or full recovery model then schedule T-Log backup on regular interval and it will keep your t-log is shape.
    We can not issue backup log command in simple recovery model.
    What exactly you wants to achieve here? Do you have any issue with disk size!!
  3. pcsql New Member

    Hi ghemant,
    My goal is just to free up log space. The backup log with truncate_only does exactly that.
    I'm doing a mass data manipulation of all tables in a database. A full backup will be done before this task. My understanding is that checkpoint will cause log truncation in simple recovery model. I want to free up log space for reuse after data manipulating of each table so that the log file size won't grow too much. I don't want to depend on the automatic checkpoint to occur so I want to issue checkpoint statement to force checkpoint to occur.
    Does log truncation occur for executing checkpoint statement manually? This old MS article for SQL Server 6.5 and older has this statement: Note that the log will not be truncated when issuing a manual checkpoint command in a database with "truncate log on checkpoint" on. However, I don't think this statement is valid for SQL Server 2000, SQL Server 2005, and SQL Server 2008. I guess it is valid for SQL Server 7.
    Is there any setting that will prevent log truncation on checkpoint in SQL Server 2008?
  4. moh_hassan20 New Member

    It is best to do regular log backup as suggested by ghemant
    in sql 2008 , The transaction log is automatically truncated when the database is using the simple recovery model.
    review the notes for Discontinued Database Engine Functionality in SQL Server 2008 in:
    http://msdn.microsoft.com/en-us/library/ms144262.aspx

    so, if you are in full recovery model , you can run:

    alter database <mydb> set recovery simple
    go

    checkpoint
    go

    alter database <mydb> set recovery full
    go

    backup database pubs to disk = 'c:mydb.bak' with init
    go

    dbcc shrinkfile (N'mydb_log' , 1)
    go



  5. MohammedU New Member

  6. cpace New Member

    USE DATABASE;GOSELECT file_id, nameFROM sys.database_files;GODBCC SHRINKFILE (1, TRUNCATEONLY);
  7. satya Moderator

    PcSQL,
    Few things I need clarification on this regard, is the disk free space is really a problem that you need to SHRINK trans.log on regular basis?
    Also you have to refer to the updated books online for SQL 2008 for the deprecated statements from the previous versions of SQL Server.
  8. ghemant Moderator

    Very true, unless and until one do not have problem with the space, he/she should not shrink the database file(s) as it would cost time and I/O operation when it required to grow further next time, hence one should be very cautious with this.
  9. ajsyx001 New Member

    Below is some code I use in a maintenance place to truncate all transaction logs, which works well
    ====================================================================SET NOCOUNT ON CREATE
    TABLE #TransactionLogFiles (DBName VARCHAR(150), LogFileName
    VARCHAR(150) )DECLARE
    DBList CURSOR FOR SELECT
    name FROM
    master..sysdatabasesWHERE
    NAME NOT IN ('master','tempdb','model','msdb','distribution')DECLARE
    @DB VARCHAR(100)DECLARE @SQL VARCHAR(8000) OPEN
    DBList FETCH
    NEXT FROM DBList INTO @DBWHILE
    @@FETCH_STATUS <> -1 BEGINSET @SQL = 'USE ' + @DB + ' INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''
    + @DB + ''', Name FROM sysfiles WHERE FileID=2'EXEC(@SQL) FETCH NEXT FROM DBList INTO @DB END
    DEALLOCATE DBList DECLARE
    TranLogList CURSOR FOR SELECT
    DBName, LogFileNameFROM #TransactionLogFiles DECLARE @LogFile VARCHAR(100) OPEN
    TranLogList FETCH
    NEXT FROM TranLogList INTO @DB, @LogFileWHILE
    @@FETCH_STATUS <> -1 BEGIN --PRINT @DB +',' + @LogFileSELECT
    @SQL = 'EXEC sp_dbOption ' + @DB + ', ''trunc. log on chkpt.'', ''True'''EXEC
    (@SQL) SELECT
    @SQL = 'USE ' + @DB + ' DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'EXEC
    (@SQL) SELECT
    @SQL = 'EXEC sp_dbOption ' + @DB + ', ''trunc. log on chkpt.'', ''False'''EXEC(@SQL) FETCH NEXT FROM TranLogList INTO @DB, @LogFile END
    DEALLOCATE
    TranLogListDROP
    TABLE #TransactionLogFiles
  10. =JR= New Member

    Perfect for what we needed. However we use a bookkeeping package that numbers the administrations as DB's. For that I have cleaned up the formatting and added appropriate brackets:

    SET NOCOUNT ON

    CREATE TABLE #TransactionLogFiles (
    DBName VARCHAR(150),
    LogFileName VARCHAR(150)
    )

    DECLARE DBList CURSOR FOR
    SELECT name FROM master..sysdatabases
    WHERE name NOT IN ('master','tempdb','model','msdb','distribution')
    AND status & 512 = 0

    DECLARE @DB VARCHAR(100)
    DECLARE @SQL VARCHAR(8000)

    OPEN DBList
    FETCH NEXT FROM DBList INTO @DB WHILE @@FETCH_STATUS <> -1
    BEGIN
    SET @SQL = 'USE [' + @DB + '] INSERT INTO #TransactionLogFiles(DBName, LogFileName) SELECT '''+ @DB + ''', RTRIM(Name) FROM sysfiles WHERE FileID=2'
    EXEC(@SQL)
    FETCH NEXT FROM DBList INTO @DB
    END

    DEALLOCATE DBList

    DECLARE TranLogList CURSOR FOR
    SELECT DBName, LogFileName FROM #TransactionLogFiles
    DECLARE @LogFile VARCHAR(100)

    OPEN TranLogList
    FETCH NEXT FROM TranLogList INTO @DB, @LogFile WHILE @@FETCH_STATUS <> -1
    BEGIN
    --PRINT @DB +',' + @LogFile
    SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''True'''
    EXEC (@SQL)
    SELECT @SQL = 'USE [' + @DB + '] DBCC SHRINKFILE(''' + @LogFile + ''',''truncateonly'') WITH NO_INFOMSGS'
    EXEC (@SQL)
    SELECT @SQL = 'EXEC sp_dbOption [' + @DB + '], ''trunc. log on chkpt.'', ''False'''
    EXEC(@SQL)
    FETCH NEXT FROM TranLogList INTO @DB, @LogFile
    END

    DEALLOCATE TranLogList
    DROP TABLE #TransactionLogFiles

Share This Page