transaction logs

  • After backing up the transaction log (with truncate log option ticked by default) in SQL 2005,

    the log file size is still the same, only once you shrink the transaction log is its size reduced.

    Why is the size not reduced after backup with truncate log ?

    Regards

    Kevin

    sonyt65@yahoo.com

  • After taking the backup of transaction log, all transactions are backed up and log file get empty but size would not reduce.

    That you have to do using this command.

    use database

    go

    DBCC SHRINKFILE ('file_name', size)

    go

    SQL DBA.

  • Yes...truncate frees up the logfile.But shrink command releases that free space to OS.

  • What you did only truncates the virtual log file, a shrink would then reduce the physical size.

    From BOL: "Log truncation does not reduce the size of a physical log file, it reduces the size of the logical log and marks as inactive the virtual logs that do not hold any part of the logical log. A log shrink operation removes enough inactive virtual logs to reduce the log file to the requested size."

    _____________
    Donn Policarpio

  • All been said is true, if you want to automate this process of shrinking logfile soon after SQL Server takes SQL Server log file then do this,

    Create a job, with

    step1 : backup log databasename to disk = 'Path/Location'

    Step2 : dbcc shrinkfile (logfilename, 1)

    As soon as your log file is taken backup, step 2 shrinks log file.

    Hope this helps.

    IM

  • And with all that having been said, you DON'T want to be shrinking the transaction log after every backup. You just force SQL Server to make it grow again when it needs additional space. You should set the size of the transaction log large enough to handle the transaction load on the database between transaction log backups, plus some additional space for slightly higher than expected loads.

    The only time you should consider shrinking the transaction log is after a highly unusual level of activity that has caused you transaction log to grow much more than normal.

  • One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?

    Thanks & Regards,
    Sudeepta.
    http://twitter.com/skganguly

  • try this

    1) dbcc opentran(dbname) - this will show the oldest open transaction

    2) Kill this process

    3) backup log dbname with no_log

    4) use dbname

    5) dbcc shrinkfile(dbname_log,100) - shrinks the log back to 100mb

  • terryj30 (11/4/2008)


    try this

    1) dbcc opentran(dbname) - this will show the oldest open transaction

    2) Kill this process

    3) backup log dbname with no_log

    4) use dbname

    5) dbcc shrinkfile(dbname_log,100) - shrinks the log back to 100mb

    Then backup the entire database because you have broken the recovery chain and you will not be able to do point-in-time restores to any point after the log truncation.

    See - http://www.sqlservercentral.com/articles/64582/

    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
  • The physical size does not reduces when u backup the log. The inactive portion of the log gets truncated and the freed space is available for use to the same log file.

    If you want to reduce the physical size of the log file, use DBCC SHRINKFILE command. this will free up the physical space and release it to the OS for use.

    HTH

    Sanjay.

  • sudeepta.ganguly (11/4/2008)


    One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?

    Hi Sudeepta,

    You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.

    HTH,

    Sanjay.

  • sudeepta.ganguly (11/4/2008)


    One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?

    If it is getting that big, then it needs to be that big. Shrinking the transaction log file will only cause SQL to grow it again when it needs space. I'd increase the frequency of your transaction log backups before I'd consider shrinking the physical size of the transaction log.

  • Sanjay Rohra (11/4/2008)


    sudeepta.ganguly (11/4/2008)


    One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?

    Hi Sudeepta,

    You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.

    HTH,

    Sanjay.

    Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).

  • Lynn Pettis (11/4/2008)


    Sanjay Rohra (11/4/2008)


    sudeepta.ganguly (11/4/2008)


    One of our database writes huge logs as as we have created a Transactional Replication on that. I was trying to automate the process of truncating the logs as it is around 25GB in one night. We are using SQL Server 2000 databases.How should I go about it?

    Hi Sudeepta,

    You can create a sql job which does the job of truncating the log (either by backup or simply truncate) and schedule it to run on a daily basis at the time suitable to you. You can also create a maintenance plan for the same.

    HTH,

    Sanjay.

    Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).

    You'll have to start over with a full backup.

    _____________
    Donn Policarpio

  • Lynn Pettis (11/4/2008)


    Don't simply truncate the transaction log. You will break your log chain, and won't be able to do a restore to a point in time. Also, you won't be able to perform any more transaction log backups until you have completed another full backup (possibly just a differential, but not too sure on that one).

    Diff is sufficient. It just has to be something to base the log chain off. Both full and diff do that. If diff didn't it wouldn't be possible to do the full backup restore, diff backup restore and then the tran logs from there.

    alter database testing set recovery full

    backup database testing to disk = 'D:\DEvelop\testing.bak' with init

    backup log testing with truncate_only

    backup log testing to disk = 'D:\Develop\testing.trn' with init

    /*Msg 4214, Level 16, State 1, Line 1

    BACKUP LOG cannot be performed because there is no current database backup.

    Msg 3013, Level 16, State 1, Line 1

    BACKUP LOG is terminating abnormally.*/

    backup database testing to disk = 'D:\DEvelop\testing_diff.bak' with DIFFERENTIAL

    backup log testing to disk = 'D:\Develop\testing.trn' with init

    -- successful

    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 15 posts - 1 through 15 (of 32 total)

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