Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Our SQL Server 2000 used to run a Sunday morning 3am job of optimizing the database. Somehow it was turned off and hasn't run in a year. We have noticed slow access to the database.

We turned it back on and it will run for about 4 hours and complete. Our issue is when it runs on its scheduled Sunday at 3am it will cause the applications that connect to stop working as long as it runs. Before it was shut off the application would continue to work but I believe since it has been off for a year and the job only ran once it wasn't enough to clean the DB up enough.

Should we run it three times in a row? Or what is the best way to fix this?

thanks in advance!

share|improve this question
Jonathan - Leaving this as a comment instead of answer because it isn't so helpful - but - Upgrading is your friend. You should be able to relatively easily migrate to SQL Server 2008 and still run in 2000 compat mode to minimize the impact to you. SQL Server 2008 isn't just better toolswise than 2000, the process of upgrading/migrating will even be a good exercise.. That said I've upvoted the answers from Shawn and Sebastian - they offer good advice here. – Mike Walsh Oct 28 '13 at 23:16

A lot can have happened in a year to cause performance issues. The fact that your "clean up job" did not run might or might not be the primary reason.

Before you start optimizing performance, the first thing you need to do is finding out what your biggest problem is and then address that. (Independent of the SQL Server version, or really independent of the system you are working with.)

SQL Server 2000 provides a limited set of tools you can use for this investigation. Start by tracing batch and procedure executions to see which of them take the longest and do the most reads. It is not unlikely at all that over the year the data changed in a way that a new index is now required.

If in the end you come to the conclusion that the indexes in place are the best for your workload, you can look at this "clean up job" again.

First you need to figure out (on a per table basis) if a reorganization or a rebuild is required. Reorganization usually is faster but has a smaller improvement impact. after a year of "neglect" you most likely need to execute a rebuild on all tables. See for more details, particularly the DBCC DBREINDEX vs. DBCC INDEXDEFRAG section.

As Shawn mentioned, each table rebuild/reorganize action requires a table lock in SQL Server 2000. To mitigate that there are two options:

  1. Upgrade to a more modern version of SQL Server that supports online index maintenance

  2. Clean up one table at a time and then take a significant break to allow other processes to be worked on. Depending on how many tables you have do one a night, or a few with an hour break in between each. In the following nights address other tables. You could also, each night address the 3 - 5 most fragmented tables. DBCC SHOWCONTIG can show you how fragmented a table is. (See above link for details.)

share|improve this answer

My guess since your application is having problems when it runs is it is doing index maintenance. The following link provides the options in the optimization tab of the maintenance plan in SQL Server 2000.

The Reorganize data and index pages option tells SQL Server to go through the database and rebuild the indexes. All of this activity will cause locks on the table until it is completed. It can also take some time to run depending on the size of the database and indexes.

There is no way around it not causing your application some issues. It is just something to determine on the business end if your application can allow for the outage to perform the work that is needed, and yes it is needed.

share|improve this answer
Thank you! But it seems like running the job once was enough to clean it all up. Do you have any recs. for example running it 2 or 3 times in a row make a difference? Thank you! – Jonathan Oct 28 '13 at 20:40
Running it in frequent succession will not make to much of a difference. You are somewhat limited on solutions that are free to the public because none that I know of support SQL Server 2000. You can get a better understanding of index fragmentation here: – Shawn Melton Oct 28 '13 at 20:43
Thank you. The reason for my concern is when we run reports against the database it is slow to generate. How many weekends in a row do you think it would need to run until It gets back to normal? Thank you – Jonathan Oct 28 '13 at 21:01
I cannot help you there. There are to many things that can play into your reports being slow. I think you might want to look into getting a professional in there to evaluate your system or start your research on performance tuning. – Shawn Melton Oct 28 '13 at 21:53

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.