Post reply

Regarding LOG file.

  • August 25, 2008 at 5:34 am

    #382396

    Dear All,

    I have createda job for taking the backup of our database. It also deletes the backup files older than 10 days.

    But I have not done anything for the LOG files. Can anyone guide me how to manage the LOG files, I mean how to take the backup of LOG and how to clear it. Also how often to take the LOG backup idealy etc;

    Thanks in advance,

    Santhosh Nair.

  • August 25, 2008 at 6:05 am

    #862471

    For all of my databases, I do two things: 1 - backup the transaction log and then move it to another server. 2- Truncate the log to conserve disk space. See the code below (substituting your database name in the code).

    Note the size of the logfile after performing the DBCC truncate is 128. Choose your logfile size keeping in mind how much AUTOGROW you might want to avoid be presizing your logfile.

    --

    -- SQL BASED BACKUP

    -- BackupLog.sql

    -- DATE VER PROGRAMMER REASON FOR MAKING CHANGES

    -- 03/07/04 1.0 JWS Initial development

    --

    -- Check for backup currently running

    declare @RET as varchar(15)

    select @RET = cmd from sys.sysprocesses where cmd = 'BACKUP DATABASE'

    if @RET = 'BACKUP DATABASE'

    BEGIN

    RAISERROR('Database Backup already in progress. Skipping transaction log backup', 16, 1)

    RETURN

    END

    -- Backup Transaction Log

    BACKUP LOG [YOUR_DATABASE_NAME] TO DISK = ''D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\YOUR_DATABASE_NAME.TRN

    -- Truncate transaction log

    dbcc shrinkfile(YOUR_DATABASE_NAME_log, 128)

  • August 25, 2008 at 6:38 am

    #862485

    For performance it is better to NOT shrink the LOG-file. It takes lots of I/O for the log-file to expand each time. When a full- or logfile-backup is taken, completed trancactions are cleared from the log-file but the diskspace is reserved. New entries will be added in this reserved space.

    It depends on your business requirements how often a (log-)backup has to be taken. When the database is heavaly used and log-file is growing rapidly, you must decrease the time between backups. This will prevent the log-file from growing too large.

    Keep in mind that space in a log-file can not be free'd after the start of any uncommitted transaction.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • August 25, 2008 at 8:47 am

    #862565

    Thanks for guidance..

  • August 25, 2008 at 10:33 am

    #862642

    HanShi (8/25/2008)

    For performance it is better to NOT shrink the LOG-file. It takes lots of I/O for the log-file to expand each time.

    In addition it causes internal log fragmentation (lots of little virtual log files) which can make the backups slower.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply