Q

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 commit...

ted.

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

Dig deeper on Microsoft SQL Server Installation

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close