Avatar of Lionel MM
Lionel MM
Flag for United States of America asked on

SQL Server 2000 and DB Maintenaince Plans

I have a very curious problem with SQL DB maintenance plans. (see picture). All these jobs were created by me, with the same user (lmm) and in the same way. The first on the list runs with no problems. The 2nd and 3rd ran without any problems until the server was rebooted; and then both would no longer work. After I created the 2nd one it ran for several weeks without any problems then after I rebooted it (after a Windows update) I could not get it to work again. Then I created the 3rd one in the list and again it ran for several weeks until again I had to reboot server and again it would stop working. Have looked in error logs and can't seem to find anything to give me any kind of guidance as what to look for. The curious thing is that it runs until the server is rebooted. Any ideas, suggestions? Thanks.
SQL-200-Maintenance-Plans.jpg
Microsoft SQL ServerMicrosoft SQL Server 2005Microsoft SQL Server 2008

Avatar of undefined
Last Comment
Lionel MM

8/22/2022 - Mon
Raja Jegan R

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

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).
Lionel MM

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Anthony Perkins

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.
Lionel MM

ASKER
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.
Anthony Perkins

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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.
Raja Jegan R

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.
Lionel MM

ASKER
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Raja Jegan R

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.
Lionel MM

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

Oh,ok..
Can you share us the log files generated for these failing jobs to check it once..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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?
Raja Jegan R

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.
Lionel MM

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
SOLUTION
Raja Jegan R

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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?
Raja Jegan R

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

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Raja Jegan R

>> 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..
Lionel MM

ASKER
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.
Lionel MM

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Raja Jegan R

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..
Lionel MM

ASKER
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?
Raja Jegan R

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..
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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.
Lionel MM

ASKER
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. ????????????
Raja Jegan R

Strange, not sure why sp_start_job procedure is not working as desired in your machine..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Lionel MM

ASKER
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)
Raja Jegan R

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.
Lionel MM

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Raja Jegan R

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

ASKER
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?
Raja Jegan R

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Lionel MM

ASKER
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.
SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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
Lionel MM

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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
Lionel MM

ASKER
mistaken entry (how to delete this?)
Raja Jegan R

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..
Your help has saved me hundreds of hours of internet surfing.
fblack61
Lionel MM

ASKER
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.
Lionel MM

ASKER
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.
SOLUTION
Raja Jegan R

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Anthony Perkins

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
Lionel MM

ASKER
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?
ASKER CERTIFIED SOLUTION
Anthony Perkins

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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).
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Raja Jegan R

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
Lionel MM

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

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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
SOLUTION
Raja Jegan R

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Lionel MM

ASKER
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.
Lionel MM

ASKER
Thanks for all the time and suggestion
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy