July 25, 2002 at 4:04 pm
I have about 50 databases on a SQL Server 2K machine. Most of the databases are under 100MB in size. The data file for one database is 3GB in size. The log file for this database keeps inflating to 1GB in size. I keep shrinking the log but it keeps growing back to the same size. The following command:
DBCC sqlperf(logspace)
reveals the 1GB log file is practically empty! If I try to restrict the growth of the log file, then my transaction log backups (run via the database maintenance plan) fail and nightly complete backups fail.
How can I determine what is causing the log file to increase in size after I shrink it?
Thanks.
July 25, 2002 at 4:10 pm
Run profiler and see.
Steve Jones
July 25, 2002 at 5:10 pm
Usually caused by an index rebuild, or changes to a large table.
Andy
July 29, 2002 at 9:33 am
Both Steve and Andy called it. Using Profiler I realized I had the "Reorganize data and index pages" option set on my database maintenance plan. Each night all indexes for each table in each database was being rebuilt. Thanks for the help.
July 29, 2002 at 9:36 am
Glad we could help. Visit often!
Andy
July 29, 2002 at 9:36 am
Any recommendations on how when or how often the "Reorganize data and index pages" option should be used?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply