March 20, 2003 at 4:35 pm
We are reindexing through the maintenance plan, and the optimizations job that does the re-indexing appears to be logging everything. Is there any way to turn off logging of the optimizations job. It takes around 8 hours to run and runs out of our maintenance window. The job blocks every process, and it makes our transaction log grow to 40 GBs even though truncate on checkpoint is set. I just don't get it.
March 20, 2003 at 5:14 pm
Do you have very large tables? How large is your database?
When indexes on a table are being rebuilt, the table becomes unavailable for use by your users. It place shared lock /exclusive lock on table to prevent any table access by your users.
All work done by DBCC DBREINDEX occurs to every tables as a single transaction. Even though you set recovery mmode to simple, the transaction log wouldn't be truncated until the indexes are built completely.
March 20, 2003 at 11:08 pm
1. How much is the Size of your database?
2. How many tables are there in your database?
3. How many Clusterd indexes are there in your database?
4. How many processors are available to SQL Server? And what is the processor?
5. How much is the Memory on this Server? and How much is being used by SQL Server??
I think these factors greatly affect your maintenance plan execution duration.
Regards,
Murali Damera.
.
March 24, 2003 at 3:43 pm
We ran into the same problem. We did two things. First, we created jobs that would reindex the individual tables on different days instead of the entire database. Our largest database is 50 GB so we would run into the same log growth and running out of the maintenance window. While this is not a perfect scenario it has alleviated some of the stress.
Adrian Porter
Adrian Porter
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply