-1

i made a mirror (High-Performance Mode) for sql database, after few days i noticed that the transaction log file(ldf) of database is not growing up. i did not know way. can anyone explain why that is happened?

2
  • 5
    why do you want the log file to grow? The log file shouldnt grow and it is a good thing and I am guessing you have some sort of transaction log backup job setup. The log file will grow if you are not taking any transaction log backups.
    – M.Ali
    Jan 7 at 13:36
  • i made transaction log backup and restore it in mirror server, but this is the first time happened that transaction log is not growing up. everytime i made the mirror the transaction log is continue growing, this time stopped and that made me confused Jan 7 at 13:58
2

To clarify what was mentioned in the comments, your Transaction Log only grows when new transactions occur in an amount and at a rate which outpaces when your Transaction Log backups run. Whenever your Transaction Log backup runs, it marks the Transaction Log file such that it can be overwritten with new data (since SQL Server now knows those transactions are backed up). Your Transaction Log should only grow when it needs to.

For example, if your Transaction Log file is 1 GB big, and you take Transaction Log backups every 1 hour, but one day your database generates 2 GB of transactions within a given hour, then the Transaction Log file will need to grow to 2 GB big to accommodate that amount of transactions. Then the backup will run, and will mark the Transaction Log file for reuse, but the file will now stay 2 GB big (unless you manually shrink it after).

If in the same above scenario, your database never generates more than 1 GB of transactions in any given hour, then the Transaction Log backup will always mark the file for reuse at a rate that's conducive to the rate at which your database generates transactions, and the Transaction Log file will never grow.

So your mirrored database either is not generating any transactions, or they're being generated at a slower rate than which your Transaction Log backups run. Or your Transaction Log file is already much larger than the amount of transactions your mirrored database has generated so far, and you haven't hit the growth point yet.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.