Problem solve Get help with specific problems with your technologies, process and projects.

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?

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.

Dig Deeper on Microsoft SQL Server Installation

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.