Ask the Expert

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

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: