473,703 Members | 2,375 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Bizarre maintenance plan failure (db backup)

I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.e xe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1 414087BE19541D1 67861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.

Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Anyone had anything similar?

Jul 23 '05 #1
4 8748
(ni**********@y ahoo.co.uk) writes:
I am getting a failure on the db backup job of one of my maintenance
plans. It is coming back with the generic error message of,

"sqlmaint.e xe failed. [SQLSTATE 42000] (Error 22029). The step
failed."

I then checked the Database Maintenance Plan History page, but this
shows all the steps having run successfully. If I check the drives for
the actual backup files, they exist and look healthy too!

There is plenty of space on the drives, so it is not that.

I've checked the NT logs and all they say is,

"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DB
Maintenance Plan for All User Databases''
(0xC06E15E2A9E1 414087BE19541D1 67861) - Status: Failed - Invoked on:
2005-06-29 21:00:04 - Message: The job failed. The Job was invoked by
Schedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "

Which doesn't give me any clues.
So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.
Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?


Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #2
So did you look at the Job History? Find the backup job, right-click
and select Show History. In this dialog check "Show step details". The
step details usually have some more meat for information.
This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_E xec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).
Since the backups have actually run to success, I am going to take off
the option on the maintenance plan to "Verfiy the integrity of the
backup upon completion". Maybe it is this that is causing problems,
rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.


Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?

Jul 23 '05 #3
ni**********@ya hoo.co.uk (ni**********@y ahoo.co.uk) writes:
This doesn't really give me any clues either. If I click show step
details, I get it displaying 2 steps,

Step 1: Executed as user: OMNIA\SVC_SQL_E xec. sqlmaint.exe failed.
[SQLSTATE 42000] (Error 22029). The step failed.

Step 0: (Job Outcome) The job failed. The Job was invoked by Schedule
35 (Schedule 1). The last step to run was step 1 (Step 1).


I rarely set into maintenance job, so I don't really know where sqlmaint
might write its errors. But looking at the properties of a plan that I've
actually set up, I see that there is a Reporting tab. Have you checked
what's yours, and if that leads to a useful file?
> Since the backups have actually run to success, I am going to take off
> the option on the maintenance plan to "Verfiy the integrity of the
> backup upon completion". Maybe it is this that is causing problems,
> rather than the backup?

Ehum, if the problem is that the backups don't verify, that I would
not like to know that and not sweep it under the carpet.


Having checked this morning the backup status is still showing Failed
even with the verify option off. The only reason I thought it *could*
be this is that the backups are fairly large (25gb+) so SQL server
could be having problems verifying with the disk space available?


I will have to retract what I said above. I recalled a discussion among
my MVP colleagues who are into server administration. The consensus was
that verification of backups as offered by RESTORE VERIFYOLNLY is useless.
The only way to really verify a backup is to restore it. And run DBCC checks
on the restored copy.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #4
haggul
7 New Member
Do your maint plans cover more than 1 database? If so check the recovery modes of each - this error seems to pop up if there are mixed modes within 1 plan.
Aug 19 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
19483
by: Mark S | last post by:
I have a db maintenance plan which is set to backup (then truncate hopefully) the transaction log. In order to backup a transaction log the db must be in single user mode so the maint. plan fails. How do you automatically set the db, in single user mode, for the transaction log to be backed up then truncated? Also, I manually set the db in single user mode and it shows itself to be in single user mode yet the number of users in the db...
2
3552
by: Michael Nostrom | last post by:
Hello, I'm trying to configure my MSDE 2000 installation to dump all databases and transaction logs to disk every night to be backed up by our backup software. All goes great in configuring the maintenance plan until I get to the retention part. I want to configure the backup plan so that it'll delete OLD backup older than 2 days. When I click the 'Remove files older than" box and choose 2, but there's nothing in the drop-down box where...
0
1007
by: Davide Bortolini | last post by:
Hi all, i can't understand why my scheduled backup in maintenance plan not work. It won't start because user haven't rights for the operation. I've set user Administrator as member of DB2ADMNS but not work. If i connect to db throught DB2 Console and perform backup operation will work fine. How is the mistake? PS: The system is running with DB2 UDB Enterprise 8.3 Windows Edition Thanks
1
3635
by: microsoft . public . sqlserver | last post by:
I am using SQL server 7.0. After restoring all the databses (Except distribution) Everything seems to be working fine except the backup maintenance plan. I put following detail in the maintenance plan "General Tab: Plan Name --- DB Maintenance Plan1 () All Databases Optimizations tab:
1
1603
by: nirop | last post by:
I created a Database Maintence Plan to backup one of my database @ 7pm but doesn't seem to do anything at all. It was working for the past months untill just this morning i noticed that no latest *.BAK files were created since 18-Aug-2006. I have other databases which do backup OK except for this particular database. I can manually back it up but cannot automatically backup from the Database Maintenance Plan. I can't seem to locate any error...
0
1782
by: mmohammed | last post by:
Hi All, I am trying to Schedule a full backup and a transaction log backup in sql server 2005 using Maintenance Plan.My requirement is to schedule these two backups at different time say 10pm and 10.30 pm daily.But when i am using Maintenance Plan it only allows me to schedule both jobs at one time that is it shows only Maintenance Plan Schedule in which you can have only one schedule. Can anyone please tell me how to schedule these two...
0
4573
by: Neff | last post by:
I have a clustered installation of SQL Server 2005 running on Windows 2003 with SSIS installed on the cluster. I'm running SQL Server Management Server on one of the cluster nodes and I can right click and create a new maintenance plan and drag a 'Back Up Database Task' into the plan. (Using the wizard also works.) However if I try to open the the Backup item in the plan I get the following error
4
3689
by: fniles | last post by:
I have created a backup maintenance plan in SQL Server 2005. Is there any way to run/execute this SQL Server backup maintenance plan from VB.NET ? Thank you.
0
1060
by: dakshinamurthy06 | last post by:
Hi, I am using SQL Server 2005, and using Maintenance plan to take the DB Backup on daily basis so please let me know which one is effecient should i use Append or Overwrite option. Please DB Backup plan to be made as append or overwrite
0
8662
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9111
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9004
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7853
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6585
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5922
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4679
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
3
2058
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.