Solved

SQL 2005 SP2 Long Running Maintenance Job

Posted on 2010-11-12
19
256 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

To effectively work with Diskpart on a Server Core, it is necessary to write some small batch script's, because you can't execute diskpart in a remote powershell session. To get startet, place the Diskpart batch script's into a share on your loca…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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 walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles to another domain controller. Log onto the new domain controller with a user account t…

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now