SQL Server Performance

Re indexing Job Fails every week

Discussion in 'Performance Tuning for DBAs' started by rahulk_005, Oct 3, 2008.

  1. rahulk_005 New Member

    Hi, We have an optimizations job ( reindexing and data integrity check) set up to run every sunday afternoon for a database which is 300GB. And that server had a 133GB drive for the transaction log. So, whenver the job runs, it starts fine,, runs for 4-5 hours and then the transaction log gets filled up completely and the job fails. Is there a way to automatically shrink the log files while the job is running ?? or what else can be done ?? this is SQL 2000 sp4 on windows 2003 server.regardsrahul
  2. satya Moderator

    Are you Reindexing all the tables in the database?
    May be thats not a good idea taking the database size into consideration!
  3. rahulk_005 New Member

    Yes, we are re-indexing all the tables. As per the maintainance plan, it selects the database. So i Think it means its reindexing all the tables...right ? Is there a way to select only certain tables ??
  4. satya Moderator

  5. rahulk_005 New Member

    Thanks for the information. But that was for SQL 2005. I am on SQL 2000.rahul
  6. rohit2900 Member

    Hi Rahul,
    What I'll suggest you is first identify the tables which are having most of inserts and updates. Considering the database size its obvious that it will eat up all the transaction log space when ever you are running that plan.
    As whenever you are done with identifying such tables then create a job for each table and then run it one by one on weekends. And apart from this can you confirm if their any specific requirement to rebuild all indexes as it's an offline operation, I'll suggest you figure out a fill factor for tables which are having most inserts and updates and then just go with index reorganize in weekly maintenance. You can refer to below links for more information.
    http://www.sql-server-performance.com/tips/rebuilding_indexes_p1.aspx
    http://blog.sqlauthority.com/2007/12/22/sql-server-difference-between-index-rebuild-and-index-reorganize-explained-with-t-sql-script/
    http://msdn.microsoft.com/en-us/library/ms139858.aspx
    http://msdn.microsoft.com/en-us/library/ms137718.aspx
    http://msdn.microsoft.com/en-us/library/ms141243.aspx
    Hope this helps.

Share This Page