Maintenance Plan on SQL Server 7 "Executing Job Step '1 (Step 1)' - Job never finishes

Using SQL Server Agent's Database Maintenance Plan on SQL Server 7, SP4 (Windows 2000 SP3), it used to be that jobs ran fine. I am not sure what I changed, but they simply started not finishing (only affects one DB). Now when they run, their status lists "Executing Job Step '1 (Step 1)' forever. Tasks that take 4 seconds now go on for ever and never finish. If I restart the SQL server service the jobs will run for about a day and then they stop working.

Things I've tried.

Deleted all maintenance plans and recreated
Increased disk space (I have plenty for backups now)
restarted the box
sp_who2 doesn't return any locks on the DB

Seems odd to me that the problem only affects a single DB. The system DB's and a couple other DB's we use all run fine.

Disk space had been a problem at one time but I since corrected that and have recreated my maintence plans.

The DB is 900MB with unrestricted file growth
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior Database AdministratorCommented:

I can't recall for SQL 7, but for SQL 2000 you have to refresh to find out if the job has finished.

Just a thought - what is the log file on this database like? Reasonable or way to big?

And have you manually run some of this maintenance - dbcc dbreindex and dbcc checkdb?

Are there any errors that do need your attention?

Just some first thoughts.

jswanAuthor Commented:
You need to refresh with SQL 7 too.

How do I check the log file?

I haven't ran any manual maintenance.

Thanks for your input!
David ToddSenior Database AdministratorCommented:

From enterprise manager,
select the database
select view|taskpad

At the bottom is a graph of file usage.

Now you _do_ need to read the numbers as the graphs are all the same lenght.

But if the log file is unusally large, and maybe rather empty, then the log file might not be shrinking. This is a prenial question - how to shrink a log file.

But this is a first look - rather like the doctor commenting that you are looking rather green before taking your pulse and other tests.

The log size depends on the recovery options. For full expect a larger log file. On one client the log file is almost the same size as the data-file, but this is unusal.

The suggestion is that the log file should be no more than 10% of the size of the data-file(s)

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

jswanAuthor Commented:
Thanks for your help!

Data space;

711MB used, 81MB free, 792MB total

Transaction log space;

15MB Used, 95MB free, 110MB total

Is this what you needed?

David ToddSenior Database AdministratorCommented:
Hi Jeff,

It is a smallish database, and those numbers don't give me any cause for concern.

Yes the log file is mostly free, but at the size it is its okay.

Since things come right by stopping and restarting SQL, check the temp database.

What about if you just stopped and restarted the agent - does thsi have any impact?

jswanAuthor Commented:
Sorry... I'm SQL rookie.

What do you mean by check the temp db?

If I stop and restart the agent the status on the hung jobs clear but they hanging the next time they are scheduled to run.

Things I'm thinking of trying;

Updating the Server to SP4
Re-applying SQL SP4
Re-installing SQL and Re-applying SQL SP4

Currently I'm restarting the SQL and Agent service with a batch file scheduled to run in off hours. The odd part about this is the maintenance jobs always seem to hang in the morning (9:00AM - 10:00AM) and approximately every other day.
David ToddSenior Database AdministratorCommented:

SQL has a temp database called tempdb. Can you look at its size compared to your production database just like you did above.

The reason for checking tempdb is that if you run our of disk space and tempdb can';t grow the job may not be able to log-on to SQL - especially between 9 & 10 am where everyone else is logged on or logging on.

But the biggy here is why are you scheduling databse maintenance to run in what are generally the heavest use hours of the day? Why isn't the job scheduled for off hours, say prior to 6am?

What is your maintenance plan trying to do?

If it is the data oroganise or reindex, be aware that at its heart is a very powerful routine that leaves 1.2 times the largest table finished size in the database as blank space. If this space isn't available - lack of disk space, database isn't autogrow, there isn't this space inthe database  - then this routine will fail.

The figures on space used/space free  you gave above don't have enough free space for this to be occurring.

Please reply with what your maintenance plans are trying to achieve.


jswanAuthor Commented:
Data space;

1MB used, 7MB free, 8MB total

Transaction log space;

.5MB Used, .2MB free, .7MB total

I setup a maintenance plan as follows;

Sundays 12:00AM - Check DB integrity (include indexes)
Sundays 1:00AM - Reorganize & Index (change free space to 10%)
Everyday 7:30PM - Complete backup
Everyday (except weekends), every 10 minutes - backup transaction log

When I clicked on the maintenance plan to check the properties so I could provide you accurate information... I received the following message;

"The job 'Transaction Log Backup Job for DB Maintenance Plan 'name" could not be parsed. Do you want to ignore it and continue? YES/NO"

The other odd part about this; My maintenance plan (when the log backup is hung) doesn't show that I have it set to backup the log file. When I look at the jobs the log file backup job is listed there. I'll  know more about this after I have a chance to restart the SQL service. I never change this!

David ToddSenior Database AdministratorCommented:

One ideas is to use separate maintenance palns and not try and do everything form one plan.

If the maintenance plans are giving you grief and hanging then I'd suggest deleting them and their jobs and recreating the plans from scratch.

This doesn't answer the question why? but it does have a fairly quick and easy fix compared to trying to take it all apart.

This given my background of doing this on site where the quickest sure fix warms your customers heart more than somewhat.


PS Even hand editing the job steps doesn't show too much useful information - usually one step to do x on maintenance plan y.
jswanAuthor Commented:
I'll give it a try... Thanks!
jswanAuthor Commented:
I tried the separate plans without success along with deleting the plans and starting from scratch.

Anything else for me to try?

What sort of response do think I'll get from MS if we spend the $250.00 for a support call?

David ToddSenior Database AdministratorCommented:
Hi Jeff,

I don't know.

I forget where but I ran across a setup for either the Agent or DTS job timeout.

I'm wondering if that woudl at least make the job finish.

Is it just the log backup that is hanging?

What happens if you run this job manually from query analyser? Is the log backup running out of disk?

Have you searched MS Knowledge base? Try that be fore spending money ...

Here's a suggestion:
I only look at the newest couple of questions when replying, so maybe posting a new question pointing to this one is the answer.

You may need to ask a question of the exchange on how to post a 9 point question to cross-post.


PS It depends how important and urgent this is in $$$ terms. If it is getting up there in those terms, I'd suggest that you scan the microsfot partners site and get contact an appropriate certifed partner - and they may have a free or reduced cost incidnet. Yes you are paying for their time, but maybe they can see things quicker than you can.

If going this path, treat this like getting a lesson in how to debug crashed maintenance plans. Also the certified partner may be seeing more of these than you will ... maybe they are becoming aware of a bug in the service pack or something like that.
jswanAuthor Commented:
I solved the problem by recreating the maintenance plan only after I deleted then recreated and restored the database in question. (ODD)

I hope my pain helps someone else!
PAQed, with points refunded (500)

E-E Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.