SQL Server 2000 and DB Maintenaince Plans

Your 2nd and 3rd jobs seems like a Backup job.
Can you kindly check your account has privileges to write to the backup path, probably that would have been disturbed after your system reboot.

if the backups are differential, they could fail if no full backup is available (to calculate a difference). Reboot (alone) should not affect that, but it is worth to try (create a full backup).

All 3 jobs are db backups; the only difference is that the first includes additional tasks--those of optimization, integrity checks and transaction log backups. The user lmm has full access rights to the backup location, a local drive, the same place that the first job, just different directories. These are not differential backups but full backups that run every hour. If there was a rights issue these jobs would fail from the start--the fact that they ran for from two to four weeks shows there is no problem with permissions to the backup path.

I will go out on a limb and suggest that the problem is with the Integrity Check and the fact that it is placing the database in single user mode.

I would disable that step and let us know how it goes.

The job that is having this problem IS NOT doing an integrity check--the job that is working does do an integrity job and continues to work after a reboot so unfortunately that suggestion won't fix anything.

It was not intended to fix anything, just point out a known issue with Maintenance Plans with SQL Server 2000 (and one of the reasons why most DBAs avoid them like the plague).

Good luck.

Thanks but if it wasn't intended to fix the problem I am encountering why would you suggest I stop it? I'm confused! Especially since the the job with the integrity checking in it has been running for years, since 1995, without any problems--the problem I am having is on the job without the integrity checking.

Kindly share across the error logs of the failing jobs if any,.
If not, then kindly try running the backup of all databases from Query window to the same backup location via the same user to see whether it works fine or not.
IF you receive any error messages from the results screen, kindly share it to us so that we can dig into the actual reason of failure.

Please excuse the delay--here is the snapshot of log files--which ones do you want? And am I looking in the right place?
SQL-Logs.jpg

No, this is not the correct place..
Can you kindly open up the Maintenance plan and see where the log files are created for these jobs and if so, can you provide those logs.
If not, configure a new log file and provide us the logs to get the reason for failure.

This IS where they write to C:\Program Files\Microsoft SQL Server\MSSQL\LOG
SQL-Logs.jpg

Oh,ok..
Can you share us the log files generated for these failing jobs to check it once..

OK but which ones--the previous picture I sent was of the log files--which ones do you want. When you read the log files of the jobs there is no failing messages in them--they work just fine, no problems until you reboot and then they stop working and then there are no more log files generated--thus my question what do you want me to send to you?

ok, got it..
>>  no problems until you reboot and then they stop working and then there are no more log files generated

Kindly confirm whether those jobs alone are failing or is it all jobs.
If all jobs are not working after Server reboot, can you check the startup type of SQL Server Agent to Automatic and see whether it helps.

Actually, I need to log file of the failed execution and since you don't have any logs like that I would request you to run the job once manually while these logs are not generating and see whether it is showing any error during the execution and update us.

Not all jobs are failing--there are ones that have been running for years.
The job that has failed in the past after a reboot--which logs would you need to show you why they stopped and why they failed.

So are you also telling me that there are no error logs I can look for, logs that after these reboots that can tell me what is going wrong?

yes, at this moment it seems like that..
Anyhow, have you tried running the job that failed after system restart from Query window once..

OK but the problem I am having is this
1) Jobs work--whenever I setup the job it runs and continues to work properly until I reboot
2) When I reboot then the jobs stop working
3) If I delete the jobs and re-create it, it works again and continue to work until the next time I reboot

>> 2) When I reboot then the jobs stop working

Kindly try running the job from Query window once it stops working to capture the errors..

OK the new task ran just fine for about a week. During that time I rebooted to see if I could get it to stop working. It continued to work. Then On Tuesday when Windows updates were installed and a reboot was required the jobs AGAIN did NOT work anymore. AGAIN it stopped after a Windows update and a reboot. I ran the query as follows
use msdb;
go
exec dbo.sp_start_job N'Hourly Backups of HFI, Dynamics and Master';
go
and got this error
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('Hourly Backups of HFI, Dynamics and Master') does not exist.

Update
I tried to use the other job name, the one that has continued to work for year after year and reboot after reboot and got the same error, so I must have something wrong in my query. When I ran this for the working job, I got the same error
use msdb;
go
exec dbo.sp_start_job N'HFI, Dynamics & Master';
go

Can you check the collation of your database whether it is case sensitive or not.
If it is case sensitive, then you should type the job name with cases as it is exactly there in the server.
Also confirm whether you are running this command from an user having sysadmin rights..

I copied the names from the properties of the job so it is exact. Where do I check case sensitive? I am logged on as me, and I have all rights. Is there a way to run the script to use another user and password?

Just try connecting via Query Analyzer from some other sysadmin login and try once.
If you have copied the job name from its properties, then it should not have any case sensitiveness issue..

OK I tried as me and as sa--get the same error
Server: Msg 14262, Level 16, State 1, Procedure sp_verify_job_identifiers, Line 61
The specified @job_name ('Hourly Backups of HFI, Dynamics and Master') does not exist.

I created another new job two days ago and once again it works--did it exactly the same way as before. And as before it works, even after I reboot the server. So it is clear that it stops working AFTER installing Windows updates and then rebooting. This is really weird that only the new jobs stop working. The first one continues to work but if I add a second or a third job they work until there is a Windows update that requires a reboot, then they stop working. ????????????

Strange, not sure why sp_start_job procedure is not working as desired in your machine..

This is a log that shows my current, newly created job works.
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server 'SBSERVER' as 'NT AUTHORITY\SYSTEM' (trusted)
Starting maintenance plan 'Daily' on 1/23/2014 7:00:00 AM
[1] Database DYNAMICS: Database Backup...
    Destination: [D:\SQL Backup\Daily\DYNAMICS\DYNAMICS_db_201401230700.BAK]
    ** Execution Time: 0 hrs, 0 mins, 2 secs **

[2] Database DYNAMICS: Verifying Backup...
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[3] Database HFI: Database Backup...
    Destination: [D:\SQL Backup\Daily\HFI\HFI_db_201401230700.BAK]
    ** Execution Time: 0 hrs, 3 mins, 31 secs **

[4] Database HFI: Verifying Backup...
    ** Execution Time: 0 hrs, 6 mins, 37 secs **

[5] Database master: Database Backup...
    Destination: [D:\SQL Backup\Daily\master\master_db_201401230710.BAK]
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[6] Database master: Verifying Backup...
    ** Execution Time: 0 hrs, 0 mins, 1 secs **

[7] Database DYNAMICS: Delete Old Backup Files...
    1 file(s) deleted.

[8] Database HFI: Delete Old Backup Files...
    1 file(s) deleted.

[9] Database master: Delete Old Backup Files...
    1 file(s) deleted.

Deleting old text reports...    0 file(s) deleted.
End of maintenance plan 'Daily' on 1/23/2014 7:10:11 AM
SQLMAINT.EXE Process Exit Code: 0 (Success)

Similarly if you can get a log for the failed execution, it could help us identify the problem either with accessing the backup path or deleting the backup files.

Once I reboot there are no new logs created, not of this type. If different log file with different extensions or is a different location are generated please tell me where and I will try to find them--otherwise we are going to have to wait until the next round of updates and a reboot to see what happens then.

I think we have to wait for your next Server reboot.

OK but surely if we know what happened in the past, not once not twice but at least 4 to 5 times why would we wait. Each time the job stops and each time there are no more log files created--unless I need to look elsewhere -- in other words does SQL log errors for jobs to a different location or does it use the same place where it logs successful jobs, namely where the logging is setup in the job?

Not sure whether it would be captured in SQL Server Agent logs, but kindly check once.
If it is not logged over there, then we don't have any other logs to check out.

SQL Server Agent logs--where are these? I have asked many different times where else I would have to look, and what other logs I may be able to check--the main point is the where to look not the what--thank you.

OK it gave me a location to log file and this is the contents of the log file
2014-01-25 07:52:18 - ? [393] Waiting for SQL Server to recover databases...
2014-01-25 07:52:20 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID
2014-01-25 07:52:20 - ? [100] Microsoft SQLServerAgent version 8.00.2039 (x86 unicode retail build) : Process ID 2640
2014-01-25 07:52:20 - ? [101] SQL Server SBSERVER version 8.00.2066 (0 connection limit)
2014-01-25 07:52:20 - ? [102] SQL Server ODBC driver version 3.86.3959
2014-01-25 07:52:20 - ? [103] NetLib being used by driver is DBMSSHRN.DLL; Local host server is
2014-01-25 07:52:20 - ? [310] 2 processor(s) and 2048 MB RAM detected
2014-01-25 07:52:20 - ? [339] Local computer is SBSERVER running Windows NT 5.2 (3790) Service Pack 2
2014-01-25 07:52:20 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2014-01-25 07:52:20 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2014-01-25 07:52:20 - + [260] Unable to start mail session (reason: No mail profile defined)
2014-01-25 07:52:20 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect

I rebooted the server again today and it is still running the hourly backup job. So yet another reboot when there are no Windows updates. Now just have to wait for the next batch of updates and see what happens after that reboot.

OK I rebooted the server this morning and now the job has stopped working--this time there was no Windows update and after the reboot the job stopped. The last log file before the reboot shows success but then after the reboot no more log files are generated--it is as if the job is no longer seen, no longer effective, it is like it just isn't there anymore.
Log-File-History.jpg

mistaken entry (how to delete this?)

Can  you kindly confirm whether after the server reboot, your databases are available in Online state before the job started since database in Recovery mode can impact your jobs too. Also confirm us the status of the jobs whether it is disabled or schedule removed or any other changes..

Databases are available; the application that uses the database functions just fine after each reboot. And if I create a new job the backup works just fine--it is the existing job that fails. And I have done nothing to the exiting job, nothing--I rebooted and it stopped working, otherwise nothing else, just a server reboot, no changes to databases or the application or the existing jobs -- nothing.

Is there a way to convert the jobs I have running into sql scripts--I may just be better off using a script (called from a batch file) instead of trying for months now to solve this seemingly unsolvable problem. All the job does is do a database backup of three DB files, no optimizing, no integrity checking just a backup once every hour, from 7AM to 9PM each day, and then files older than 24 hours get deleted. I can do some of this with batch commands but the more I can get done with a sql script the better--can anyone wrote this for me? Thanks.

OK thanks for that--however this script backup all the databases I only want to backup 3--how/where do I adjust the script to do that?

OK thanks for that--however this script backup all the databases I only want to backup 3--how/where do I adjust the script to do that?
Did you read the article?  Specifically this part:

You could also change this into a stored procedure and pass in a database name or if left NULL it backups all databases.

What about the code?  Did you see this:
WHERE name NOT IN ('master','model','msdb','tempdb')  -- exclude these databases

So if you wanted to include the database Larry, Curly and Moe it would be:
WHERE name IN ('Larry','Curly','Moe')  -- Include the 3 Stooges

I tested that script and it works--just had to change the path. However that leaves me with 2 unresolved problems 1) how to change the path that each DB goes into its own directory 2) How do I have DBs older than 24 hours deleted?

I intend to close this question since we have been unable to determine what is wrong, what is causing the problem--I will then open another to get a script working. So far I have been able to get 66% of what I need done by a script except the part to delete files based on the age of the file (namely files that are 24 hours old or older).

Try deleting the older backup with the below command..

declare @dt datetime
-- delete older than two days...
select @dt = getdate()-2
EXECUTE master.dbo.xp_delete_file 0,N'D:BackupFolder',N'BAK',@dt

Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'master.dbo.xp_delete_file'.

If so, then you can try with VB scripts mentioned in the below link..

http://community.spiceworks.com/scripts/show/719-delete-files-older-than-max-age-in-days

This scripts does not work does not delete any files. I tried a similar one (see below) and it did not work either because even though a specify hours it goes by days somehow. Say I have a file created at 11:30PM today and this scripts runs at 12:30AM tomorrow it will delete all the files from today and not only those 24 hours or older but any from the day before.
' Define threshold for deletes, folder to scan, and extension of files to purge
iHours = 20
sDir = "D:\Backup\Master"
sExt = ".bak"

' Create filesystem object and base folder object
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sDir)

' Call routine to recursively scan and purge old files from base folder
DoDelete oFolder

' Recursive subroutine to delete old files
Sub DoDelete(oFolder)
    ' Look at all files in this folder
    For Each oFile In oFolder.Files
        ' See if it's the extension we want
        If Right(LCase(oFile.Name), 4) = sExt Then
            ' If it's old enough, remove it
            If DateDiff("n", oFile.DateLastModified, Now) >= iHours Then oFile.Delete
        End If
    Next

    ' Call this subroutine for each child subfolder
    For Each oSubFolder In oFolder.SubFolders
        DoDelete oSubFolder
    Next
End Sub

Raja, thanks for your continued help. I looked at the file you provided but it does not allow for checking the age of a file based on time, namely hours. All the examples provided in the link deal with days--if I use a day parameter of 1 day and I have a file that was created at 10AM yesterday and I run this script today it will delete that file because it is one day old. What I need is a scrip that will delete the file that I created only when I run the script at 10AM today and leave all the files that were created at 11AM, 12PM, 1PM, 2PM, etc.etc. This script will delete all the files from yesterday I only want them deleted once they are 24 hours or more old and not all of yesterday's files. In essence at 10AM it should delete the 10AM from yesterday, at 11Am it will delete the 11AM from yesterday and so on, but leave all the rest from yesterday alone.

Thanks for all the time and suggestion