Solved

SQL 2005 SP2 Long Running Maintenance Job

Posted on 2010-11-12
19
259 Views
Last Modified: 2012-05-10
I have a 64bit version of SQL 2005 SP2 running on Windows 2008 Enterprise Edition. My DB is running in 2000 mode due to compaitbly issues with the application it host. Anyway on average my maintenace job take 30 minutes to run but every now and then it will take 1 to 3 hours to complete for no reason.

-No Errors in the SQL or WIndows logs
-No AV

Any thoughts???
0
Comment
Question by:compdigit44
  • 10
  • 8
19 Comments
 

Expert Comment

by:Asheenth
ID: 34120447
Please make sure that you are having sufficient space in the hard disk.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34120617
I have over 1TB of free space!!
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34120873
Have a look at the growth settings of the database and also the when the database has grown. You can see this at the bottom of the database disk usage report.
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 19

Author Comment

by:compdigit44
ID: 34121124
Where can i find this report?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34121192
how can I tell when the last time a DB grew..

All of te DB are set to grow by 1MB and unlimited
0
 
LVL 16

Accepted Solution

by:
EvilPostIt earned 500 total points
ID: 34121196
In management studio right click on your database goto Reports > Standard Reports > Disk Usage.

At the bottom there is a table to expand to show you the filegrowths. There is also a column to show you the time it took to grow the datafile on milliseconds.
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121222
Yeah you will want to change that 1mb to something bigger. The previously mentioned report will show you the date & time of the autogrowth.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34121630
I just got an error when tryinh to run this report..
It states it cannot run the report becuase the DB is in 2000 mode.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34121729
OK I was able todo some more poking around and my DB did not grow significaly before my maintenace job ran.

Any more idea?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34121745
What tasks are carried out in this maintenance task?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34121833
I did not build the job but it appears to update Stats if that makes sence..
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122906
Does it do anything else?

You could update the maintenance plan to include a text report. It will make it easier to find out exactly what is being done.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34122919
I did create the job and so not feel comforable changing it..

Is there anything else I could check?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34122977
Im not sure why this would be taking so long if it were only rebuilding stats and there has been no database growth. This is why it would be helpful to see an output with what happened at certain times.
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34123028
Besides doing this is there anything else I can check

Could the fact that my sql 2005 DB is running in 2000 mode cause these random slow jobs??
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34123077
It could, is there a reason for not changing the compatibility level?
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34123095
yes the application that uses the DB will only support running in 2000 mode

Are there any articles you know of that talk about performance problem with 2000 mode?
0
 
LVL 16

Expert Comment

by:EvilPostIt
ID: 34123444
Not that I know of, would just be doing the same as you at this point ie trying google...
0
 
LVL 19

Author Comment

by:compdigit44
ID: 34123581
thanks for trying
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you migrate a Terminal Server licenses server inside the 2008 server family, you can takte advantage of the build-in migration tool. If you like to migrate an older 2003 Server (and the installed client CALs) to a 2008 R2 server for example, you …
This article explains how to install and use the NTBackup utility that comes with Windows Server.
This tutorial will walk an individual through locating and launching the BEUtility application and how to execute it on the appropriate database. Log onto the server running the Backup Exec database. In a larger environment, this would generally be …
This tutorial will give a short introduction and overview of Backup Exec 2012 and how to navigate and perform basic functions. Click on the Backup Exec button in the upper left corner. From here, are global settings for the application such as conne…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question