November 5, 2003 at 12:05 pm
I have a SS2K database that I run the maintenance plan optimizations on every weekend. lately, when the job runs, the transaction log grows to over 25 GB and only stops then because the disk is full. The database file itself is only 35 GB. The job does not finish and has to be cancelled. Anyone have any ideas as to why this is happening? The database is in use during the optimizations but this has not been a problem in the past. Any help would be appreciated.
November 5, 2003 at 12:16 pm
What is the size of largest table in your database?
November 5, 2003 at 1:29 pm
The application creates tables dynamically everyday so the data is spread out fairly evenly. The database has over 5500 tables currently. I'm wondering if that is part of the problem since the optimazations rebuilds all of the indices. The largest table currently is about 10 MB.
November 5, 2003 at 2:06 pm
How do you create table dynamically? What is database recovery model and how do you backup database transaction log? Rebuild indexes will increase the transaction log usage, if you backup/truncate transaction log more frequently, you shouldn't see large transaction log file. DBCC DBREINDEX rebuild all of the indexes for a table in one transaction and the completed transation can be backed up or truncated to ensure transaction log file doesn't grow too large.
November 5, 2003 at 2:40 pm
The tables are created by an third-party application, a new set of tables in created everyday. The recovery model is Full. Does that sound right that rebuilding of even 12,500 indexes should make the log grow so large? I can schedule frequent transaction log backups during the time that the optimizations are running if I have to.
November 5, 2003 at 7:41 pm
I would schedule for frequent log backups during the optimization job.
November 5, 2003 at 7:42 pm
I would schedule for frequent log backups during the optimization job.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply