Logfile Ballons during Optimization
-
Several weeks ago, I upgraded one of my application databases with a script from the software manufacturer. Ever since I am unable to run the Optimizations Job to completion, as it will grow even bigger than the database file each time and then crash when the disk runs out of room. Sure, I could upgrade the drive, but I would have thought that 16GB of log files was enough to hold this busy, but not that busy database.
Vendor changes include more use of clustering than they had previously, some additional keys, but nothing that I though would do this.
Optimizations job is standard one-DB job as generated by Maintenance Wizard. On general principles, I re-generated all batch maintenance by re-using the wizard whie I was trying to solve this but no effect.
System is SQL 2000 sp2A running on Win2K3 fully patched.
Batch log file is next to useless (Last step to run was step 1). I did not see anything usefull in the SQL Server logs except that the transaction log is full.
Any suggestions?
thanks in advance
tx
-
What size are the tables in the database? If you have a very large table with a clustered index you will need approx 120% of the used space for that table for reindeing operations.
If the maintenance job performs any reindexing, try turning it off and run DBCC INDEXDEFRAG manually.
I generally remove maintenance plans. Their all or nothing approach to maintenance is often more trouble than its worth. I setup regular maintenance to only reindex tables when they become fragmented. You can check the fragmentation level by running DBCC SHOWCONTIG.
--------------------
Colt 45 - the original point and click interface
-
Wonderful - i suspected the answer was something like that. Tables are, indeed, quite large.
I will investigate waht you suggest.
tc
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply