Post reply

Transaction Log

  • August 20, 2007 at 11:22 pm

    #153762

    Hi Every body,

    In my database Transaction log file size is increasing how can decrease plz help

    Thanks

    subu

  • August 20, 2007 at 11:58 pm

    #728192

    Hi

    Take Transaction Log backups frequently. These can be scheduled according to your needs. This will make sure that your TLog file does not grow too big. Combined with other type of backups you have scheduled (Full\Differential) this will be a good disaster recovery mechanism.

    If you want to reduce your TLog size then shrink the database. Steps for this are

    Take a Full db Backup. Make sure no one else is connected to the database.

    Change the recovery model to simple

    Shrink database - you can do this from management studio. Right click on the database, select Shrink\Database.

    Change the recovery model to what it was before .

    "Keep Trying"

  • August 21, 2007 at 12:15 am

    #728193

    Hi,

    Not necessary to take the database in simple recovery in all scenarios. If the backup not properly scheduled for the database, take a full backup and directly you can shrink the log file.

    DBCC Shrinkfile(tlogfile)

    or

    DBCC Shrinkfile(tlogfile,targetsize)

    some time the above will help to reduce only less amount of size. If you want to remove all commited/inactive transactions, you can try the following too. Before proceed this, ensure the Full or appropriate Tlog backup is available for the database.

    backup log dbaname with truncate_only

    dbcc shrinkfile(Tlogfile,truncateonly)

    the above will remove all commited/inactive transactions from the log file and keep the active transactions available.

     

     

    Thanks
    Jay
    http://www.sqldbops.com

  • August 21, 2007 at 2:03 am

    #728208

    Hi Krishna &Others,

    Thank u for reply, but i can done it. but again it will some prob it will not reduse

    Some amount of data will be reduse i think it will take some backup into another device it will reduse

    Is it correct or not

    In case i have taken this type of backup any prob in my DB or Tlogfile plz give guidence

  • August 21, 2007 at 4:31 am

    #728235

    can u tell us what exactly you did.... did not understand clearly ...

    "Keep Trying"

  • August 21, 2007 at 11:09 pm

    #728483

    Hi Shiv,

    Kindly look into the below MS KB, where you can find a more info.

    http://support.microsoft.com/kb/907511

    For your reference read the below

    http://msdn2.microsoft.com/en-us/library/ms189493.aspx

    If you have any issue after following these steps, post the full scenario in which you are facing the issue.

     

    Thanks

    Kumar

     

     

Viewing 6 posts - 1 through 6 (of 6 total)

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