First transaction log backup as big as full backup

  • Hello,

    Recently, we switched from SQL Server 7 to SQL Server 2000. In this new situation the first transaction log backup after a full backup is almost as large as the full backup. The following transaction log backups are at the expected low size. In SQL Server 7 we did not notice this behaviour.

    The time between the full backup and the first transaction log backup is one day, with almost no transactions during that day.

     

    Can anyone explain this to me, and if possible give a solution?

     

    Thanks,

    Erik

     

     

  • in SQL2K, the full backup does not clean transaction log. So the time between full backup and transaction backup is irrelevant.

    Only transaction log backup clean out transaction log.

    Add more transaction log backup between the last trans backup (before full backup) and the first trans backup (after full backup) might be a solution.

  • I found the problem.

    Just prior to the full backup I did a rebuild of the indexes (with the sqlmaint utility). This was the reason for the large transaction log backup.

    Adding a truncate log step before the full backup solved the problem.

     

  • This is usually true of 99% of threads relating to your transaction log problem.

    To reduce the size of the transaction log backup, switch to bulk logged mode before you start the index rebuild and then switch back to full afterwards.

    You may want to think about your indexing startegy and put in an appropriate fill factor for the indexes. You won't then need to rebuild them every night. An alternative is to spilt the indexes into seven groups and then rebuild one group each night, effectively rebuilding each index once weekly.

    This will save you a lot of space if you require.


    ------------------------------
    The Users are always right - when I'm not wrong!

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

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