Newbie: continuing backup failures

Question

Newbie: continuing backup failures

jmonty

SSC-Addicted

Points: 407
More actions
August 26, 2003 at 7:53 am

#106327

SQL server 2000: Maint plan for master, model, msdb & maintenance plan log backup jobs keep failing as well as the integrity check job. In history, under details it reads: Job step failed & [SQLSTATE 42000] (Error 22029)
Now, I am very, very, very new to SQL Server.
I have been investigating why these log backups & integrity checks keep failing. I understand that it is a generic error (22029)that keeps being shown. I did some research and thought that the problem lied with the job step that said "repair minor problems" and so I removed the -CkDBRepair option from the job step, but they continue to fail. This system has users on almost 24/7. So I cannot manually kill sessions. I believe this may be part of it; it cannot get into single-user mode...at least I had thought so until today. Maybe not??? I could really use some helpful advise and some hand-holding on this one, cause I am still stumbling my way around.
Thanx in advance for your help.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

NPeeters SSChampion Points: 12541 More actions · Answer 1

Did you check permissions of the user running the Maintenance Plan job? This is by default the user that created the maintenance plan, and maybe that user does not have sufficient permissions to run one or more of the steps in the job.

SQLBill SSC Guru Points: 51440 More actions · Answer 2

Your databases are probably set for SIMPLE Recovery Mode. You can only do log backups if the mode is FULL. (Go to Enterprise Manager, drill down to the databases, right click on one of them, select Properties, go to the Options tab. Look at RECOVERY MODE:.)

And I believe MASTER can only be SIMPLE MODE.

-SQLBill

jmonty SSC-Addicted Points: 407 More actions · Answer 3

Thanx for your quick response.

OK. The maintenance plan is for the master, model & msdb. The master & msdb are set to Simple and the model set to Full Recovery mode. I checked the permissions tab on all:

The user/roles listed on the permissions tab are public, guest, printuser, and 2 other user groups. the plan's owner is listed as "Administrator".

Also, none of the permission boxes are checked for any user/role on any of the 3 DBs.

What's my next step?

BrenBart SSCrazy Points: 2622 More actions · Answer 4

In Enterprise Manager-SQL Server Agent-Jobs look at the specific jobs that failed. Right click and check the job history. Also check the SQL Server logs as well as the maintenance plan history. Somewhere in all this there might be more informative error messages. Try posting all of them here so that we have more to go on.

Some good info in this article;

http://www.sqlservercentral.com/columnists/awarren/sqlmaintenanceplans.asp

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

Steve Jones - SSC Editor SSC Guru Points: 736455 More actions · Answer 5

Also check the maint plan logs. Look at the properties for the maintenance plan to see where the job is being logged and check out the log files for an error.

Steve Jones

sjones@sqlservercentral.com

http://www.sqlservercentral.com/columnists/sjones

http://www.dkranch.net

jmonty SSC-Addicted Points: 407 More actions · Answer 6

There was nothing in the Current log.

under the job history details all I saw was the following error message:

sqlmaint.exe failed [SQLSTATE 42000] (Error 22029). The step failed.

I have not been able to find any other descriptive log error messages at all.

There is only 1 step in this job which consists of the following command:

Execute master.dbo.xp-sqlmaint N'-PlanID 6573B987-03A2-40BE-BB85-8213D13ADE1D -Rpt "C:\MSSQL7\LOG\Master, Model, MSDB Backup & Maintenance Plan6.txt" -DeltxtRpt 1WEEKS - WriteHistory -VrfyBackup - BkUpOnlyIfClean -BkUpMedia DISK -BkUpLog "F:\mssql7\backups" -DelBkUps 1WEEKS -CrBkSubDir -BkExt "TRN"

BrenBart SSCrazy Points: 2622 More actions · Answer 7

What do you have checked on the reporting tab of the maintenance plan?

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

BrenBart SSCrazy Points: 2622 More actions · Answer 8

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

BrenBart SSCrazy Points: 2622 More actions · Answer 9

Oops! Didn't mean to do that. I was just checking to see if I could.

"I met Larry Niven at ConClave 27...AND I fixed his computer. How cool is that?"
(Memoirs of a geek)

jmonty SSC-Addicted Points: 407 More actions · Answer 10

I went nosing around again. If you didn't read my first post, I am new to SQl Server and do not know where everything is at yet.

I finally found the DB maintenance plans (right where I should have looked before - duh!) And I also found the history to which you were referring to. The more descriptive error message WAS what I had thought the problem was in the first place (diagnosing the symptoms): DB not in single-user mode, cannot perform repair minor problems. I found the Maintenance plan and sure enough, on the integrity tab, the repair minor errors checkbox was checked. I UN-checked it and we will see if this clears this up tonight. I am confident it will.

FYI: I am also going to attempt to set up some scheduled downtime to do the complete backup correctly.

I want to thank all who replied to my posts.

You have all been very helpful and your replies were very quick. I am sure you will see me post here often, as like I said I am learning as I go, and I will check by here everyday to learn from others.

I am glad I found you guys - Thanx to all!