December 4, 2006 at 9:42 am
I have some Wizard created optimization jobs on my main DB server. Our "heavy" times are at night and weekends, lighter during week days. That being said, our optimization job fires Monday morning. Most weeks, we end up getting at least one timeout during this time.
My question is why? I thought the maint jobs did not generally interfere with the processing that is going on? Is there something I can watch? Something I can do different? I know I have seen posts stating that some DBA's do not use the wizard for creating the maint. plans. Does anyone have a link to a place I could get a good start on this?
Enviroment: Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DB Size: 4424 MB
Optimazation plan options: Reorganize data and index pages - change free space per page percentage to: 10% / Remove unused space from database files - shrink database when it grows beyond: 50%, amount of free space to remain after shrink: 10% of the data space
TIA
Cory
-- Cory
December 4, 2006 at 11:44 am
Main issue with your maintenance plan is Reorganize pages will Cause the indexes on the tables in the database to be dropped and re-created with the original FILLFACTOR that was specified when the indexes were created.
During this process table will not be available for the user to access which might be cuasing the timeouts...
If you want you can remove that Reorganize option from your MP and you can DBCC DBINDEXDEFRAG which online operation but it is not as effective as DBCC DBREINDEX...
Read BOL for more details..
MohammedU
Microsoft SQL Server MVP
December 4, 2006 at 11:52 am
MANY thanks! I have made the change, and will continue to research the DBCC options you have shown me. Thanks again!
-- Cory
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply