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
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
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!
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
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
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
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
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
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.
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
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
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
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.
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.
ASKER
I'll give it a try... Thanks!
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
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.
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.
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!
I hope my pain helps someone else!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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