Patrick,
Thanks so much for all your help. Here's what was causing the jobs to
fail. The wizard asks if you would like to do integrity checks and I
selected yes then it also asks if you want the system to make minor
repairs if a problem is found and here is where I went wrong I selected
yes. But for the system to make those repairs the database must be in
single user mode and it wasn't. I recreated the maintenance jobs and
said no the automatic repairs and surprise, surprise, surprise the jobs
all ran successfully.
I feel stupid but I hope posting the resolution might help someone who
has the same issue or prevent some other newbie from making the same
mistake.
Thanks again,
Elecia
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed]
Sent: Wednesday, January 29, 2003 9:31 PM
To: LazyDBA.com Discussion
Subject: RE: Maintenance jobs are failing
Can you execute these in query analyzer and send the results...also has
the recovery model changed for the DB's?
-----Original Message-----
From: Elecia Ezell [mailto:[Email Address Removed]
Sent: Wednesday, January 29, 2003 7:31 PM
To: LazyDBA.com Discussion
Subject: RE: Maintenance jobs are failing
Job1 trans log backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
3471B682-C911-4EEE-8AA0-898810B62E1F -Rpt "D:\Data\SMART CITY PLAT
MAINTENANCE-BACKUP6.txt" -WriteHistory -VrfyBackup -BkUpOnlyIfClean
-CkDBRe pair -BkUpMedia DISK -BkUpLog "D:\Data" -BkExt "TRN"'
Job 2 Integrity Checks
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
3471B682-C911-4EEE-8AA0-898810B62E1F -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\BACKUP\SMART CITY PLAT AINTENANCE-BACKUP2.txt"
-WriteHistory -CkDBRepair '
Job3 Database Backup
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
3471B682-C911-4EEE-8AA0-898810B62E1F -Rpt "C:\Program Files\Microsoft
SQL Server\MSSQL\BACKUP\SMART CITY PLAT AINTENANCE-BACKUP4.txt"
-WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDBRep air -BkUpMedia
DISK -BkUpDB "D:\Data" -CrBkSubDir -BkExt "BAK"'
I should have mentioned that this is a new sql installation and the
database was migrated from another server today and that this is the
first attempt to execute the jobs. So it's not like they were working up
to this point and just started failing. Is it possible that it has
something to do with my installation?
Elecia
-----Original Message-----
From: Elecia Ezell [mailto:[Email Address Removed] Wednesday, January 29, 2003 8:22 PM
To: LazyDBA.com Discussion
Subject: RE: Maintenance jobs are failing
Patrick,
There is just the one step and they are only running for 1 second
before they fail. Elecia
-----Original Message-----
From: Patrick Gallucci [mailto:[Email Address Removed] Wednesday, January 29, 2003 8:11 PM
To: 'Elecia Ezell'; 'LazyDBA.com Discussion'
Subject: RE: Maintenance jobs are failing
Elecia,
That does not mean the whole job failed.
Do this...
Navigate to and then expand Database Maintenance Plans. Right-click the
Database Maintenance Plan for which the corresponding job failed. Click
Database Maintenance Plan History.
Show steps and see what exact step failed...should give you more
information as well...If still unresolved reply and will dig deeper...
Patrick Gallucci
http://www.patrickgallucci.com
[Email Address Removed] 214-642-0524
Dallas Web Builder, Inc.
http://www.dallaswebbuilder.com
15950 North Dallas Parkway
Tower Two, Suite 400
Dallas, Texas 75248
office: 972-361-8017
fax: 972-361-8005
-----Original Message-----
From: Elecia Ezell [mailto:[Email Address Removed] Wednesday, January 29, 2003 7:11 PM
To: LazyDBA.com Discussion
Subject: Maintenance jobs are failing
Hi all,
I am trying to execute my maintenance jobs that I created using the sql
wizard but they fail each time. When I view the job history this is the
error given.
Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE
42000] (Error 22029). The step failed.
I looked at the permissions on the extended stored procedure sqlmaint
and public user has exec permission. I am clueless as to what the
problem is. Any assistance would be greatly appreciated.
Thanks in advance,
Elecia
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY To
unsubscribe, e-mail: mssqldba-[Email Address Removed] For additional
commands, e-mail: mssqldba-[Email Address Removed] REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To unsubscribe, e-mail: mssqldba-[Email Address Removed] additional commands, e-mail: mssqldba-[Email Address Removed]MS Sql Server LazyDBA home page