Shrinking a TLog while Mirroring

  • hi

    our log file sizes are becoming huge, And our databases are configured for mirroring. what else way, we can truncate the log files?

    Our system is a automated reconcilation system. where in we will load huge volumes of data from different sources. compare and find the breaks. we will store only the comparision results.. all other loaded datas will be deleted from the system.

    We have two databases. One to store comparision rules and results(say ruledb). another(say tempdb) for temparory datas to load.

    Data in the ruledb are confidential and important. And at the time of any disasters we need those data to be immedietly available at the other end.

    So we enabled mirroring over that database.

    All our comparision procedures will be in the rulesdb, it refers the tables from the tempdb using synonyms and compares it.

    Even then our rulesdb's log file size is getting increased tremendously.

    Please suggest some solutions.

  • Take more frequent transaction log backups and then find the sweet spot for size. e.g. After 2 hours of work it is 10gb so size it to 12gb and keep backing up the transaction log every two hours.

    Where I work we generate 200gb of transaction log per day but our log file is ony 45gb as we backup every 15 minutes and our database is also mirrored.

  • vmssanthosh (4/6/2011)


    hi

    our log file sizes are becoming huge, And our databases are configured for mirroring. what else way, we can truncate the log files?

    Back up the log regularly. 15 minutes, 10 minutes, I've seen systems doing log backups every 5 minutes.

    If the system is important enough to be mirrored then I assume the data is critical. If that's the case, then you need log backups, to disk, retained for long enough that you can always recover from disaster by restoring backups.

    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 3 posts - 16 through 17 (of 17 total)

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