First transaction log in the morning is the size of the database

I recently upgraded from SQL Server 7.0 to 2000. I also upgraded the SQL Server Maintenance Plans that are set for transaction log backups on my user databases (8:00 AM - 9:00 PM) and a full database backup at 9:30 PM. All user databases are set to the 'Full' data recovery model. All backups are running fine except that the first transaction log in the morning is the size of the database. I know that no one has logged into that database, so why would it be so big considering the last activity on the database itself is the full DB backup?

    Requires Free Membership to View

    Login

A large transaction log over the evening hours with no processing, I believe is related to having the check boxes enabled in the SQL maintenance plan for the consistency and optimization checks. In a nutshell these configurations execute DBCC CHECKDB and DBCC DBREINDEX respectfully. I believe you should check these configurations and disable these options and review the log size the following day if no scheduling changes are committed.

If the SQL maintenance plan does not have the consistency and optimization checks enabled, then I would research the situation further. It may be worth monitoring the server during the entire night on a first hand basis to ensure unexpected users are not using the system or use a tool like SQL Profiler that ships free with SQL Server and sequentially lists all of the transactions. Another option is to review a product by the name of Log Explorer from Lumigent which provides an intuitive interface to the transaction log and provides additional features not available natively to review the transaction log. I hope this answers your questions.

This was first published in August 2004

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top