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
LVL 26
Lionel MMSmall Business IT ConsultantAsked:
Who is Participating?
 
Anthony PerkinsConnect With a Mentor Commented:
1) how to change the path that each DB goes into its own directory
Something like this perhaps (make sure the folder exists prior to doing the backup or it will fail):
SET @fileName = @path + '\' + @name +'\' + @name + '_' + @fileDate + '.BAK'  
BACKUP DATABASE @name TO DISK = @fileName  

2) How do I have DBs older than 24 hours deleted?
There are a number of ways of doing this:
1. If you have SQL Server xp_cmdshell enabled (not recommended) you can then write some script to find the backups that are older than x days.
2. You can write a vbscript (or your language of choice) program to do the same.  You could also use this program to execute your backup program.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
DamjanDemsarCommented:
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).
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Anthony PerkinsCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
This IS where they write to C:\Program Files\Microsoft SQL Server\MSSQL\LOG
SQL-Logs.jpg
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Oh,ok..
Can you share us the log files generated for these failing jobs to check it once..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Ok, whenever log file generation get stopped for your Hourly Backup job for HFI & Master, then try running it manually once and see if a log file is getting generated or not.
And also confirm when the log files are not generated, your backup files are generated or not.
If not, then try starting the maintenance plan job from query window like this:

use msdb;
go
exec dbo.sp_start_job N'job_name' ;
go

Not sure whether this will work for SQL Server 2000 but give it a try.
While running in SQL Server query window, it should show some error in the results screen.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
yes, at this moment it seems like that..
Anyhow, have you tried running the job that failed after system restart from Query window once..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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. ????????????
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Strange, not sure why sp_start_job procedure is not working as desired in your machine..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
I think we have to wait for your next Server reboot.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Anthony PerkinsConnect With a Mentor Commented:
SQL Server Agent logs--where are these?
See if this helps:
DECLARE	@ThisIsMySQLServerAgentErrorLogFile nvarchar(4000) 
EXEC master.dbo.xp_instance_regread
    N'HKEY_LOCAL_MACHINE',
    N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
    N'ErrorLogFile', 
    @ThisIsMySQLServerAgentErrorLogFile OUTPUT
select @ThisIsMySQLServerAgentErrorLogFile

Open in new window

0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
mistaken entry (how to delete this?)
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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..
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
You can try with this SQL script to backup your databases..

http://www.mssqltips.com/sqlservertip/1070/simple-script-to-backup-all-sql-server-databases/

Since I don't have SQL Server 2000, haven't tested it.
Kindly try it once and let me know of any issues with errors.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Anthony PerkinsCommented:
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
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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?
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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).
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'master.dbo.xp_delete_file'.
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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
0
 
Raja Jegan RConnect With a Mentor SQL Server DBA & ArchitectCommented:
Can you try with this one and use the Createtime parameter to delete files created based upon the created datetime.
http://gallery.technet.microsoft.com/scriptcenter/Delete-files-older-than-x-13b29c09
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
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.
0
 
Lionel MMSmall Business IT ConsultantAuthor Commented:
Thanks for all the time and suggestion
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.