Solved

SQL 2005 SP2 Long Running Maintenance Job

Posted on 2010-11-12
19
261 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 20

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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 20

Author Comment

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

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 20

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 20

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 20

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 20

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 20

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 20

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 20

Author Comment

by:compdigit44
ID: 34123581
thanks for trying
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
A safe way to clean winsxs folder from your windows server 2008 R2 editions
This tutorial will walk an individual through the steps necessary to install and configure the Windows Server Backup Utility. Directly connect an external storage device such as a USB drive, or CD\DVD burner: If the device is a USB drive, ensure i…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

759 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