Solved

SQL 2005 SP2 Long Running Maintenance Job

Posted on 2010-11-12
19
262 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
Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…

624 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