Post reply

Delete Transaction Logs?

  • December 13, 2007 at 8:32 am

    #377460

    We have a situation where a transaction log has grown to the point where a backup is impossible because there isn't enough space on any local drive to run a backup. Although I know deleting the transaction log is not a good idea, this isn't a critical application (it is hardly used) and we have backups to tape and from Live State anyway. How can this newbie simply delete the existing log and create a new, empty log? Any advice on other pre- or post-steps would be appreciated.

  • December 13, 2007 at 8:45 am

    #759874

    First change the recovery model to SIMPLE. If you aren't going to run transaction log backups then don't set recovery to FULL. Next, use the command BACKUP LOG [databasename] WITH NO_LOG to truncate the log. Your next step is to shrink the log file to a more suitable size.

    Francis

  • December 13, 2007 at 8:45 am

    #759875

    One solution is to change to simple recovery mode:

    alter database mydatabase set recovery simple

    then shrink the logfile (usually 2 is the log file id, check by looking into sysfiles)

    DBCC SHRINKFILE (2)

    then change back to full or bulk recovery mode, and do take a backup

    Regards,

    Andras

    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

  • December 13, 2007 at 8:58 am

    #759885

    Wow good post Andras, we must have been typing our reply simultaneously

    Great Minds think alike

    or is it Fools don't differ

    I can never remember those aphorisms

    😛

    Francis

  • December 13, 2007 at 9:04 am

    #759889

    Many thanks for the responses! These were a great help!

    Rich

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

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