Solved

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

Posted on 2003-10-29
16
1,587 Views
Last Modified: 2007-12-19
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
0
Comment
Question by:jswan
  • 7
  • 6
16 Comments
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 

Author Comment

by:jswan
Comment Utility
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!
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 

Author Comment

by:jswan
Comment Utility
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
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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
0
 

Author Comment

by:jswan
Comment Utility
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.
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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

0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 

Author Comment

by:jswan
Comment Utility
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
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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.
0
 

Author Comment

by:jswan
Comment Utility
I'll give it a try... Thanks!
0
 

Author Comment

by:jswan
Comment Utility
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
0
 
LVL 35

Expert Comment

by:David Todd
Comment Utility
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.
0
 

Author Comment

by:jswan
Comment Utility
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!
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (500)

Computer101
E-E Admin
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

This article describes some very basic things about SQL Server filegroups.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

9 Experts available now in Live!

Get 1:1 Help Now