Database Maintenance Plan Question

Question

Database Maintenance Plan Question

Bob Orallo

Valued Member

Points: 73
More actions
October 28, 2004 at 12:57 pm

#161764

I recently inherited and manage a billing database that is 50GB in size with over 200 tables. I am told that this database started out at 2Gb two years ago and it grew exponentially when they added an asset tracking system database to it. No maintainance has ever been done on it since they first installed SQL 2000 over three years ago. I tried to run a Database maintenance plan last weekend and it ran for over 12 hours. The "Optimization" Step (data and index reorganization) was where it was stuck without any indication that it was completed. I eventually stopped the process. As far as the actual application itself, the "reconciliation" module that this folks run everynight runs between 15 minutes to 1 1/2 hours each night. It gives no indication why it runs fast one night and slow the next night even though the transactions are equally the same. I feel that there might be tables that are being checked even though there's no data on them (archievable tables) and probably needs to be marked archived. Any assistance you can provide would be really appreciated at this time. Thanks you again!
SQLMnky

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

jbrewer Default port Points: 1453 More actions · Answer 1

My guess is that all of your jobs are not all running successfully when it only runs for 15 minutes. Go into your database maintenance plan, right click and choose properties, choose the reporting tab. Hopefully there is a check on write history to table. Click on view history. The job will appear to run successfully in the sql server agent but if there are any jobs running against the database, the rebuild index does not run. The history will tell you if the job actually ran. Choose rebuild indexes under activity to cut down on the jobs that will display.

If there is not a check on write history to table. Check it and view it the next time your job runs. I would also limit the rows to about 500.

I hope this helps

sunjiulu Ten Centuries Points: 1232 More actions · Answer 2

the optimizing table and index will take longer when there are many fragmentation in tables and indexes, dbcc showcontig will tell you how much fragmentation you have. this operation will have lock issue when there are other activities on this database.

After optimizing job finish, you will see dramatic performance gain, depending on how much fragmentation you have in tables and indexes.

Tito Orallo SSC Enthusiast Points: 118 More actions · Answer 3

jbrewer,

Thanks so much for responding to my inquiry! Another question please: Is there a way to just do the optimizing(Step 1) in DMP to a few tables rather than to hundreds of them to minimize the time frame it takes to run it. The more often I run the DMP Step 1 (Optimization) the shorter time I hope it will get. Also, if I have to kill the job in the middle of a run, is there a way I can tell which table or index it stopped the rebuilding and pick it back up from where it left of? Thanks again for the response!

SQLMnky

Tito Orallo SSC Enthusiast Points: 118 More actions · Answer 4

I have been running the DMP during off peak hours (between 10:00pm to 9:00am the next day. Would you advise to run it otherwise during peak hours (9:00am to 6:00pm)?? Thanks again for the support!

SQLmnky