Solved

SQL Server 2000 and DB Maintenaince Plans

Posted on 2013-12-30
56
361 Views
Last Modified: 2014-04-01
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
0
Comment
Question by:lionelmm
  • 31
  • 19
  • 5
  • +1
56 Comments
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39746028
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
 
LVL 5

Expert Comment

by:DamjanDemsar
ID: 39746064
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39746222
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39747472
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39748092
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39749220
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39749750
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39750880
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39759277
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39761537
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39762124
This IS where they write to C:\Program Files\Microsoft SQL Server\MSSQL\LOG
SQL-Logs.jpg
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39762166
Oh,ok..
Can you share us the log files generated for these failing jobs to check it once..
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39762217
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39762518
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39763073
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 39764024
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39765021
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39765223
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39771889
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39774415
>> 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
 
LVL 24

Author Comment

by:lionelmm
ID: 39788522
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39788529
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39793925
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39794076
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39794500
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39794876
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39800520
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39801941
Strange, not sure why sp_start_job procedure is not working as desired in your machine..
0
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
LVL 24

Author Comment

by:lionelmm
ID: 39802812
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39805779
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39806418
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39806564
I think we have to wait for your next Server reboot.
0
 
LVL 24

Author Comment

by:lionelmm
ID: 39811964
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39818037
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39818081
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 200 total points
ID: 39819568
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39825623
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39844415
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39847930
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39848625
mistaken entry (how to delete this?)
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39849813
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39849952
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39851983
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 39852704
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39853565
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
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 39855141
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39856802
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
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 200 total points
ID: 39857820
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39873522
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
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39873823
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39876704
Server: Msg 2812, Level 16, State 62, Line 4
Could not find stored procedure 'master.dbo.xp_delete_file'.
0
 
LVL 57

Expert Comment

by:Raja Jegan R
ID: 39876743
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39876819
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
 
LVL 57

Assisted Solution

by:Raja Jegan R
Raja Jegan R earned 300 total points
ID: 39908493
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
 
LVL 24

Author Comment

by:lionelmm
ID: 39934125
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
 
LVL 24

Author Closing Comment

by:lionelmm
ID: 39969271
Thanks for all the time and suggestion
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

12 Experts available now in Live!

Get 1:1 Help Now