June 4, 2002 at 5:49 pm
I just upgraded from SQL Server 7.0 SP4 to SQL Server 2000 SP2. All seems to have gone fine expect for the backups. After getting a backup error problem last night with a transaction log backup, I have been trying to manually invoke. It doesn't work. I then tried on a maintenance plan and that too doesn't work. Here is the error message from the Event Viewer:
SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'Metadata Database Maintenance Plan'' (0x71E5F937C437D6118B1D00104BCC2CB5) - Status: Failed - Invoked on: 2002-06-04 16:11:02 - Message: The job failed. The Job was invoked by User PUBLIC_WORKS\gisadmin. The last step to run was step 1 (Step 1).
I changed tapes to a fresh tape right before I upgraded SQL Server if that matters. For a little while, I was getting another error saying that I had an unnamed tape in my drive. I then took the tape, opened it in MSBACKUP (same server), and named the tape by backing up one file. After the unnamed tape error went away. However now I am receiving the other error.
Any ideas?
Thanks in advance!
June 5, 2002 at 5:16 am
Have you tried a backup using BACKUP DATABASE dbname TO TAPE = tapename WITH (See SQL BOL for more detail). You may need to drop your maintenance plan, but run Backup Database first to make sure backup is succeeding. If you get an error from this please post.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 11:45 am
This did work. I was actually thinking about trying this. I was playing with it after my post and noticed that some of the backups worked fine. However my system databases and a couple of small ones no longer worked. Do you think the backup profiles became corrupted with the upgrade?
Thanks!
June 5, 2002 at 12:13 pm
Not sure, could be. So let me make sure, you tried BACKUP DATABASE and some failed? If this is correct what was the message with those? I can look and see if any other complaints have been made. I have not seen this.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 5, 2002 at 12:20 pm
No, actually nothing has failed with backup database. What has failed are the individual scheduled jobs. They were failing for all except for my 1.2 gb database. In other words, for my system databases and other databases the scheduled jobs failed. However the backup database does work on all databases.
I figure I will recreate my maintenance plans and my differential backups. Hopefully when I recreate, it will allow my scheduled transaction log backups, integrity checks, and optimization plans to work again.
What are your thoughts?
Thanks again,
Jeff
June 5, 2002 at 12:35 pm
That would be my next plan of attack. If all works fine otherwise recreate the Maintainence Plans and see if it works then. If does the old are corrupted in some way.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
June 10, 2002 at 12:37 pm
My team has SQL Server 2000 SP2 on NT. I recently set up maintenance plans for two databases. The SQL Server Agent job history shows the DB backups failed, but the maintenance plan history the backups were successful.
Executed as user: [local NT - Authenticated ServicesAccountHere]sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
Other parts of the maintenance plan show success in the job history (optimizations, integrity checks)
I deleted the plans and re-created them and get the same scenario.
Any thought as to why the SQL Server Agent job is failing?
And why does the maint plan history show success when the Agent job history shows failure??
June 10, 2002 at 1:10 pm
Have you enabled logging to a text file to see if you can capture any more detail? Usually the maint plan history has more/better info than sql agent, never seen it happen where they disagree about a failure though.
Andy
June 10, 2002 at 1:47 pm
I didn't log to a text file but I did try to run the T-SQL statement I got from the SQL Server Agent job history in Query Analyzer.
It showed that the backup executed fine, but the attempted deleting of old jobs failed.
I'm thinking maybe it is because the local NT account I use to run the services doesn't have permission to the file where the backups are. Should the services account have to have permission to that folder?
I'm still not sure why the Maintenance plan history doesn't show failure for the deletion of the old Dbs while the agent errors off???
June 10, 2002 at 2:24 pm
Interesting, I'll have to check that. I did some testing related to this recently, not sure I looked in both places or not. You'd think if the service account had write access it would have delete as well. Definitely add it if it missing.
Andy
June 10, 2002 at 2:30 pm
A discovery I have made about the service account's permissions is that as long as the folder/share is beneath the sql server tree all is well. When I place a file outside of the sql share, it has been necessary to set specific permissions.
Not sure this info will help or not.
Jody
June 12, 2002 at 3:46 pm
I was able to get most of my problems fixed. I was reading on a web site about certain bugs with SQL Server 2000. Apparently if you have the "Fix small problems with your database checked" under integrity (I believe) of the maintenance plan, this will cause your integrity check to fail. This, in turn, will cause your maintenance plan to fail. I unchecked and both the maintennace plan and integrity check both worked. I am now still having a problem backing up my transaction logs for one database.
June 12, 2002 at 4:25 pm
Im pretty sure that bug has been fixed. Not often you want to use that option since it requires single user mode.
Andy
June 12, 2002 at 7:27 pm
Two things, first do you have a full backup for that database, and second what is your recover model set to (properties of database in EM)? It should be full or bulk-logged, if simple it will recovery the space from the log.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply