Link to home
Start Free TrialLog in
Avatar of jswan
jswan

asked on

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
Avatar of David Todd
David Todd
Flag of New Zealand image

Hi,

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.

Regards
  David
Avatar of jswan
jswan

ASKER

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!
Hi,

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)

Regards
  David
Avatar of jswan

ASKER

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?

Jeff
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?

Regards
  David
Avatar of jswan

ASKER

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

FYI
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.
Hi,

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.

Regards
  David

Avatar of jswan

ASKER

TEMPDB
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!


Jeff
Hi,

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.

Regards
  David

PS Even hand editing the job steps doesn't show too much useful information - usually one step to do x on maintenance plan y.
Avatar of jswan

ASKER

I'll give it a try... Thanks!
Avatar of jswan

ASKER

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?

Jeff
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.

Regards
  David

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.
Avatar of jswan

ASKER

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!
ASKER CERTIFIED SOLUTION
Avatar of Computer101
Computer101
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial