Error 22029 sql - Исправление ошибок и поиск оптимальных решений проблем

Remove From My Forums

Вопрос
Hi All,

I am trying to run Integrity Checks Job for DB Maintenance Plan ‘Delta Maintenance Plan’ job and I keep getting the following error message:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
I have looked under job history but I don’t seem to find anything on it, would someone please let me know what this means please.

Thank you

Все ответы

Hi Ti,

When I searched for this error I came to know that there might be multiple reasons for this:

1) Integrity step is running on high number of databases.

2) SQLDMO registration problem

3) In Integrity step databases are selected manullay and later one of DB is removed.

So there are mix and matches of things.

http://www.sqlservercentral.com/Forums/Topic128881-24-3.aspx#bm1023965

Regards Gursethi Blog: http://sqlfundas.blogspot.com/ ++++ Please mark «Propose As Answer» if my answer helped ++++
Hi ti2,

In addition to GURSETHI’s suggestion, please also refer the below link about how to troubleshoot problems that occur when you use a Database Maintenance plan in SQL Server:

http://support.microsoft.com/kb/288577

Thanks,
Weilin Qiao

Please remember to mark the replies as answers if they help and unmark them if they provide no help. This can be beneficial to other community members reading the thread.
Check to see if you have any log files for this plan , the error message that you see is more generic and we cannot really help you with this. If you haven’t had a log file set for this job/plan do it now then see the detailed error message in the log
file.

Thanks, Leks
you can enable logging of SQL Job to create detailed log which woudl also show the «relevant» error message.

Balmukund Lakhani | Please mark solved if I’ve answered your question, vote for it as helpful to help other user’s find a solution quicker

———————————————————————————
This posting is provided «AS IS» with no warranties, and confers no rights.

———————————————————————————
My Blog: http://blogs.msdn.com/blakhani

Team Blog: http://blogs.msdn.com/sqlserverfaq

A Transaction Log is a file that contains all the records of transactions and the database modifications made by each transaction in SQL Server database. The log file plays a very important part of SQL Server database when it comes to disaster recovery and it should not be in corrupted state. There are several types of errors we came across while using SQL Server and one of the most common error is Failure in SQL Server Transaction Log or Error 22029. This page will be discussing about the same error in details.

SQL Server Transaction Log Backup Failing

Why It Occurs?

This error in associated with the maintenance plan of the transaction log back up or maintenance plan options for the database in SQL Server.

There are many reasons why the above error in SQL Server occurs-

The SQL Server transaction log backup error 22029 may be seen if your database maintenance plan is trying to make a transaction log backup of a database that is offline.
It may occur when you try to create transaction log backup and when the database is using the simple recovery model.
It may occur if you select the master database and you choose backup the transaction log as part of the maintenance plan option.
This error may also be seen if the string for the target folder (where the database backup will be stored or the one used for transaction log backup or one used for reports) in the maintenance plan contains “-S”
It may happen if you have less disk space for the transaction log backup.
If the multiple databases are backed up in a single maintenance plan, this error might show up.
In addition, it may occur if you have no right access permissions or authority. Example- the SQL agent does not have write access to the file storing the database backups.

How It Can Be Resolved?

One thing that should be kept in mind while creating maintenance tasks is to check the “write report to a text file in directory” so that all the details of whatever happening during the maintenance task is seen, especially when there is any error.

The reason why the first error occurs can be solved by connecting the database to the server. So that, the log of that database can be backed up successfully with the maintenance plan as the database is online.
While creating the maintenance plan for transaction log backup under simple recovery model, we may find error. Why is it so?
The simple recovery gives the simplest form of backup and restore. It supports both database and file backups but does not support log backups. The absence of log backups simplifies managing backup and restore.

Tips: The simple recovery model is not appropriate for systems where loss of recent changes is unacceptable. In such cases, full recovery model is preferred.

We should never run a transaction log backup on a database in the Simple Recovery model. Even though there is option to select all databases, we can select either database that are not in Simple Recovery model or change the database to the Full or Bulk-logged Recovery models to avoid this error. This problem has been fixed in SQL Server 2005 as the databases in simple recovery are ignored automatically.
The third error says we cannot select master database and choose backup of transaction log because only full database backups of master database are allowed. Hence, we can create a separate maintenance plan for master database and not backup the transaction log or set master database to Full recovery and do transaction log backup.
To solve the fourth error, we will not use strings that contain “-S” for a directory path. It only occurs on named instances of SQL Server 2000.
The problem with less storage can be solved by allocating more disk space.
If the error occurred due to sixth reason, then we can select a single or some databases while back up and keep in mind the type of recovery models with respect to transaction log backup.
To perform transaction log backup of the database, we need to configure MS SQL Server to run under a user account that has administrator privileges to the machine.

Published by Andrew Jackson

I am SQL DBA and SQL Server blogger too. I like to share about SQL Server and the problems related to it as well as their solution and also I do handle database related user queries, server or database maintenance, database management etc.
View all posts by Andrew Jackson

Источник

SQL Server Performance Forum – Threads Archive

My maintenance plan backup failed the other day with the following error and from my research I found that it was likely caused from not having enough disk space, which there was not sufficient space to do the backup. I removed a ton of space and it failed again last night with there being 55GB free space, when the DB is only 16GB.

Executed as user: NT AUTHORITYSYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

After I freed up some space, the backup ran okay, but the maintenance plan was still failing with the same error message.

The only change I made to this Maintenance Plan backup recently, was I moved it from 6:00am to 7:00 am in the morning and I don’t see anything else conflicting.

My suspicion is that because this «step 1» of the plan also contains the deletion of files older than 1 day, that maybe this last setp failed after the backup was completed.

I do see that it is not deleting these older files.

Do you suppose that by changing the time of the backup, it would make this last step of the plan fail? This is the only reason I could think of. Below is the MP script:

EXECUTE master.dbo.xp_sqlmaint N’-PlanID EB943500-B818-425F-B9B8-20EBE92FFC8D -WriteHistory -VrfyBackup -BkUpOnlyIfClean -CkDB -BkUpMedia DISK -BkUpDB «Erogram FilesMicrosoft SQL ServerMSSQLBACKUP» -DelBkUps 1DAYS -BkExt «BAK»’

Thanks.

Ensure the SQL Server service account has required privileges to deal the files and see whether those files are accessed by any other process during that time.

Check DB maintenance plan history log file for further information.

Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Satya,

No problem with the accounts.

The log is showing that the backup succeeeded and the step «delete old DB backup files» has a red «X» in the «status» however the log says that 1 file was deleted when it wasn’t.

This is very quirkey. I am thinking about just recreating the maintenance plan from scratch, but would like to know what is going wrong.

Thanks.

Is it outputting to text file? You sometimes get more useful information from there about what’s going on than from the SQL Server Agent job history. If it isn’t, maybe try setting it to and see what is produced.

Tom Pullen
DBA, Oxfam GB

Just an FYI that I resolved this issue. I found that by changing the time of the backup, it was running into my process that copies the backup to another server. So, what this was doing was locking the file, so the maintenance plan could not delete it.

It is strange that there were no such errors in any of the logs indicating an access violation or that the file was in use….

Thanks!

True there are really few annoying errors displayed when any db maintenance plan is executed, I know Tom doesn’t like them for one of this reason [<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />] and by checking db maintenance history log it may give you clue. <hr noshade size=»1″>Satya SKJ Moderator http://www.SQL-Server-Performance.Com/forum</a> <center>This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.</center>

Hello People[<img src=’/community/emoticons/emotion-2.gif’ alt=’‘ />] I am new here and the same problem made me find my way here. Here is a quick solution i have to offer. This is a bug in SQL Server 2000 as accepted by Microsoft. If you click on the Plan properties and Click on the «View History» button under the «Reporting» tab, you will see the following text, not sure about the exact sentencing, but the meaning is clear: »Could not Back up transaction log. This backup command is ignored»: As advised by MSFT, i did the following: 1. I set the recovery model of my database to «FULL» 2. Re-executed the plan, but the same silly message. 3. The next thing i could do was look into the table that was mentioned in the option adjacent to the «View History» button. select * from msdb.dbo.sysdbmaintplan_history If you drill your way down to the correct plan , you will see this message in the «Activity» Column » The file <&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;p’ />ath on your server>><<database_name>>_tlog_200411101518.TRN already exists. This backup command is ignored » For some reason, even after the database model is set to anything other than SIMPLE, SQL Server refers to the same old .TRN file. So, i renamed this file to something else and then executed the plan again. This time it was a success!!! I hope this helps, if not all the Gurus, the newbies who find their way in here. Cheers, Shanthanu

I realize that I am replying to an old message, but I found this to be a great solution. When I did the following command:
select * from msdb.dbo.sysdbmaintplan_history
I was able to find the actual error which was not 22029, but
1934 [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 1934: [Microsoft][ODBC SQL Server Driver][SQL Server]DBCC failed because the following SET options have incorrect settings: ‘QUOTED_IDENTIFIER, ARITHABORT’.
I believe that the [SQLSTATE 42000] (Error 22029) error message is generic. I have had several reasons for this error. But the drilling down into the results of the above select statement have helped me resolve this issue on several different servers with the same error. I have found that the problem could be related to security, space and not this problem.
Thanks for this forum. Its really great.

If someone else have this problem, I verify security access and after that and some test I found that one error source could be the recovery setup of your data base, I changed it from single to full and the problem was fixed.ALTER DATABASE data_base_name SET RECOVERY FULL

I am also facing the exact problem in my maintenance plan, but iam not doing any copy of that backup to any another server just iam taking the backup and deleting the old backup, since this job is failing. please let me know what i need to check further.Thanks in advance.

In my SQL SERVER 2000 instance, I found that the JOB that runs the MAINTENANCE PLAN did not have the correct name of the MAINTENANCE PLAN. I changed the MAINTENANCE PLAN name to match the JOB name that was automatically created by the system. Specifically, my JOB name is [DB Backup Job for DB Maintenance Plan ‘Daily Transaction’] My MAINTENACE PLAN name was [Daily Transaction for CentralSite]. Now my MAINTENACE PLAN name is [Daily Transaction]. My job now runs to success.

My maintenance plans were running fine for long time, as soon as the database reached 18GB I started experiencing problems when the SQL Server Agent started reporting failures, yet the databases were backed up to a NAS, this prompted me to upgrade the memory in the server (quad processor, with 16 GB RAM, running Windows 2003 Enterprise, and SQL 2000 Enterprise), so maxed the machine to 32 GB of RAM and the problem went away!
So what I deducted of this experience is that the SQL Agent/OS, ran out of memory to read back the files, since I had set the jobs to verify the backup files.
Another reason for the jobs to fail is obviously lack of disk space if backing up to disk.
Happy SQL administration!

Have also had this crop up a lot recently (in plans written by soemone else!), and have found a further solution.
In the plan look at the ‘Integrity’ tab – there is a check box for «Perform these test before backing up the database or transaction log». If you have set the plan to try to repair problems, make sure this box is un-checked. It appears, no matter what scedule may have been created for this task, evry time the database is backed up SQL will perform the integrity checks…
Nick

This is an old thread but just in case it might benefit someone else… I was receiving this error and discovered that the user account running the job had lost its default database somehow. I logged into SQL Server under another user and reset the default database for the user running the job and problem solved!

]]>

Источник

failed maintenance plan [SQLSTATE 42000] (Error 22029)

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

I'm having the same problem. I reviewed the history but it doesn't tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney

————
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

I had the same problem with maint plans a few weeks ago after upgrading to sql 2K. What was so confusing is that the job would usually succeed, and it would not fail consistently(ie, at the same time every day). I went through the bad jobs, opened up each individual step, and changed the "run as" parameter from "self" to my domain account which runs sql(even though the entire job was setup to run as this user). I don't know why, but this seems to have worked for me. HTH

————
Sidney Ives at 4/30/01 2:41:16 PM

I'm having the same problem. I reviewed the history but it doesn't tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney

————
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

I've resolved my issue with this problem. My maint. plan was failing with essentially the same problem. I stopped backing up my trans log with my database. Once I did that, my backup plan was successful. I didn't need a separate trans log backup on the databases I was backing up.

There were two things that led me to this conclusion. First, I ran the query below and reviewed the messages for the failed backups. I noticed that all of them were on the trans log and not the database:

select database_name, activity, start_time, message
from msdb..sysdbmaintplan_history
where error_number = 0 <— this line optional

Secondly, I read 'Transaction Log Backups' in BOL. It indicated to me that I only needed the trans log backup if I was attempting to recover up to a point in time after the db backup was taken. If I have to backup the trans. log, I intend to do it in a separate maint. plan to begin outside of the db backup.

I hope this makes sense.
Sidney

————
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
This is the only error message to be found in any of the logs. I've checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can't find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley
failed maintenance plan [SQLSTATE 42000] (Error 22029)

HI All,

I have came across the same case my maintanence also failed with error [SQLSTATE 42000] (Error 22029).

Upon my investigation i found that the drive is running out of space where the backup is taking place.I Have released space and re ran the maintainence plan and it is successful.Hope it might work for you too.

Thanks,
Sk.Mahamood
HP India
mahamood351@gmail.com

Источник

Содержание

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed
Asked by:
Question
All replies
Thread: failed maintenance plan [SQLSTATE 42000] (Error 22029)
failed maintenance plan [SQLSTATE 42000] (Error 22029)
failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)
failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)
Sqlmaint exe failed sqlstate 42000 error 22029 the step failed
Asked by:
Question
All replies
Sqlmaint exe failed sqlstate 42000 error 22029 the step failed
Вопрос
Ответы
Все ответы
Sqlmaint exe failed sqlstate 42000 error 22029 the step failed
Answered by:
Question
Answers
All replies

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

I am trying to run Integrity Checks Job for DB Maintenance Plan ‘Delta Maintenance Plan’ job and I keep getting the following error message:
sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
I have looked under job history but I don’t seem to find anything on it, would someone please let me know what this means please.

When I searched for this error I came to know that there might be multiple reasons for this:

1) Integrity step is running on high number of databases.

2) SQLDMO registration problem

3) In Integrity step databases are selected manullay and later one of DB is removed.

Источник

Thread: failed maintenance plan [SQLSTATE 42000] (Error 22029)

Thread Tools

Display

failed maintenance plan [SQLSTATE 42000] (Error 22029)

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
«sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.»
This is the only error message to be found in any of the logs. I’ve checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can’t find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

I’m having the same problem. I reviewed the history but it doesn’t tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney

————
Stuart Ainsley at 4/9/01 9:23:41 PM

We have a maintenance plan that runs every 15 minutes to backup a transaction log. It fails intermittantly with the message:
«sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.»
This is the only error message to be found in any of the logs. I’ve checked event viewer, the SQL error logs, exception log, maintenance plan log, sysdbmainplan_history table in msdb, but can’t find any extra information. I checked the MSDN for info on this error and this did not add any extra light on the problem.
Does anyone have any ideas on how to de-bug this error?
regards
Stuart Ainsley

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

I had the same problem with maint plans a few weeks ago after upgrading to sql 2K. What was so confusing is that the job would usually succeed, and it would not fail consistently(ie, at the same time every day). I went through the bad jobs, opened up each individual step, and changed the «run as» parameter from «self» to my domain account which runs sql(even though the entire job was setup to run as this user). I don’t know why, but this seems to have worked for me. HTH

————
Sidney Ives at 4/30/01 2:41:16 PM

I’m having the same problem. I reviewed the history but it doesn’t tell me anymore than you already have, nor does the Event Log and the SQL Error Log. Have you been able to resolve your problem?
Sidney

————
Stuart Ainsley at 4/9/01 9:23:41 PM

Источник

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

When I searched for this error I came to know that there might be multiple reasons for this:

1) Integrity step is running on high number of databases.

2) SQLDMO registration problem

3) In Integrity step databases are selected manullay and later one of DB is removed.

Источник

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

Вопрос

We are getting the error as below:

Message
Executed as user: domainDBAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

The transactional logs are not deleting since Sept 2019. What is the best process of deleting the transactional logs? Can I create full backup, change the database recovery mode to simple, delete all transactional logs and run the job again.

Ответы

Все ответы

What do you mean with transaction logs? The log backups? If these backups are not deleted as intended, delete the files manually. No need to change the recovery model (overall changing the recovery model to simple is something you should be very hesitant to do).

I believe the maintenance plabns have their own logs which should have more details on the actual error.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Firstly, for the error message, this does not necessarily indicate that the Maintenance Plan as a whole has failed. you have to check the plan history to see what the failure is and then take the appropriate action to resolve the problem.

For your questions:

>>What is the best process of deleting the transactional logs?

You can backup the transaction log first, then use DBCC SHRINKFILE statement and specify the EMPTYFILE clause. And you can create a job that delete useless backup.

>>Can I create full backup, change the database recovery mode to simple, delete all transactional logs and run the job again.

It’s better not to set database recovery mode to simple because when disaster strikes you may risk data loss. You just want to free up storage space and reduce transaction file size, right? Best methods is Backup, Truncate and Shrinkfile. More detail ,You can refer to the article https://www.sqlshack.com/sql-server-transaction-log-backup-truncate-and-shrink-operations/

Hope it will help. If you still have problem, let me know.

Источник

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We are getting the error as below:

Message
Executed as user: domainDBAdmin. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.

Answers

I believe the maintenance plabns have their own logs which should have more details on the actual error.

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

For your questions:

>>What is the best process of deleting the transactional logs?

You can backup the transaction log first, then use DBCC SHRINKFILE statement and specify the EMPTYFILE clause. And you can create a job that delete useless backup.

>>Can I create full backup, change the database recovery mode to simple, delete all transactional logs and run the job again.

Hope it will help. If you still have problem, let me know.

Источник

October 24, 2008 at 1:56 am

#128086

W2k, SQLserver2k SP4

Simple Db

This occurs in 1-Integrity chcks and 2- Optimizations check, in a Maintenance plan.-

I dont know what happend.

Thanks for your help.-
MarkusB

SSC-Dedicated

Points: 37370

October 24, 2008 at 2:09 am

#889059

If you have reporting enabled in your maintenance plan, there should be a logfile with more details about the error.

The error you gave us is just a general error which happens every time a maintennace tasks fails.

[font=»Verdana»]Markus Bohse[/font]
Renuka__

Hall of Fame

Points: 3656

October 24, 2008 at 2:20 am

#889062

Hi,

Right click on Maintenance plan and select Maintenance Plan History. You may get more details there….

[font=»Verdana»]Renuka__[/font]
IceMan-920581

Mr or Mrs. 500

Points: 586

October 27, 2008 at 1:55 am

#889851

Thanks, here the reports. I have dbo rights, but i dont undertand, this happend 2 weeks ago.
Gail Shaw

SSC Guru

Points: 1004494

October 27, 2008 at 2:18 am

#889855

Is it still happening?

What login are the maintenance plans running as? What rights does that login have?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass
MarkusB

SSC-Dedicated

Points: 37370

October 27, 2008 at 2:19 am

#889857

Obviously the account under which the maintenance plan is executed has not enough permissions. Check which account is used.

[font=»Verdana»]Markus Bohse[/font]
IceMan-920581

Mr or Mrs. 500

Points: 586

October 27, 2008 at 2:26 am

#889859

I dont change the user, but this occurs 2 weeks ago and without any change for me. I use sa or other user who have admin rigths. In other DB in «other server» i havent any problem with the same user.
MarkusB

SSC-Dedicated

Points: 37370

October 27, 2008 at 3:12 am

#889870

Without working on the system myself I can’t say for sure why the user doesn’t have enough permissions anymore.

ISince you use the LocalSystem account my guess would be that someone removed the Built-InAdmin group and forgot to grant permissions to the service account.

Check if there’s a Login NT AuthoritySYSTEM and if it has the correct permissions.

[font=»Verdana»]Markus Bohse[/font]
IceMan-920581

Mr or Mrs. 500

Points: 586

October 27, 2008 at 2:40 pm

#890258

Yes Marcus, somebody, «SO administrators delete the user», «i notice FBI». Then in the Connection of the SQL Agent properties, i use the sa user, and finish the problem. Thanks for your help. cya.

Viewing 9 posts — 1 through 8 (of 8 total)

Источник

Error 22029 — SQLMaint.exe fails but works?

(OP)

3 Aug 05 10:48

I’ve set up Transaction Log backups for a database (no optimization, no integrity checks since this database is the read only product of replication from the production db) via DBMP. For over 5 months, the job has been working fine, no issues. This db is the only db on the job, set up with FULL recovery mode. I’m using it as the source of a manual log shipping to a customer’s server.

Now, all of a sudden, I’m getting SQL Server State 42000, Error 22029, SQLMaint.exe failed errors. No changes have been made that I am aware of. Twice I have gone into my client’s place to restart everything. I stay there over 2 hours, watching the process work after I manually start the backup job. Just last week, I watched it backup the TransLog 5 times before I left, and it continued to backup until after 9:36 p.m. After that, it started failing again with the same error.

Does anyone have any ideas why this error would only come up intermittantly? And why it will work when I’m babysitting it, but hours after I leave and go back to my other job, it fails again? The customer is running SQL Server 2k SP3 and this has not changed since before I created the job in the first place.

Thanks,

Catadmin — MCDBA, MCSA
«If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???»

Источник

From BetaArchive Wiki

Jump to:navigation, search

Article ID: 326485

Article Last Modified on 12/10/2005

Microsoft SQL Server 2000 Standard Edition

This article was previously published under Q326485

SYMPTOMS

A database job created with the Database Maintenance Plan Wizard fails for a named SQL Server 2000 instance. If you view the job history in SQL Server Agent and you enable the Show step details check box, you will see the following error:

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

CAUSE

The string for the target folder (the folder specified to store the database backup, or the one used for the transaction log backup or the one used for reports) in a maintenance plan contains «-S». For example, the directory path «C:Db-Save» contains an «-S» string.

WORKAROUND

To work around this issue, do not use strings that contain «-S» for a directory path. Also note that the error only occurs when the «S» that follows the dash «-» is an uppercase letter («S»).

STATUS

Microsoft is researching this problem and will post more information in this article when the information becomes available.

MORE INFORMATION

This problem only occurs on named instances of SQL Server 2000.

The Database Maintenance Plan Wizard creates a job that includes a step to run the xp_sqlmaint extended stored procedure. The xp_sqlmaint extended stored procedure calls the sqlmaint utility with a string that contains various switches to Sqlmaint.exe. One of those switches is the -S parameter, which is used for the server name. The -S parameter is misinterpreted; therefore, the error occurs. As a consequence, you may experience this problem if you use the xp_sqlmaint extended stored procedure in conjunction with named instances of SQL Server 2000.

This problem can also occur if the path of the location of the SQL binaries contains a «-S» string. For example, if the SQL binaries are located in a folder that is named, C:Program FilesMicrosoft SQL ServerMssql-S01Binn, the xp_sqlmaint jobs will fail. This behavior also occurs with named instances. In this scenario, you receive the following error message:

Microsoft SQLMaint Utility (Unicode), Version [Microsoft SQL-DMO (ODBC SQLState: 08001)] Error 0: [Microsoft][ODBC SQL Server Driver][Shared Memory]Invalid connection. [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Invalid Instance()).

The following registry key value is used to obtain information about the location of SQL Server binaries:

HKLMSoftwareMicrosoftMicrosoft SQL Server<Instance Name>SETUPSQLPATH

SQL Server 2000 Books Online; topics: «xp_sqlmaint»; «sqlmaint utility»

For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:

288577 Troubleshooting Database Maintenance plan failures

303292 BUG: Expired transaction log backups may not be deleted by Maintenance Plan

Keywords: kberrmsg kbtshoot kbprb kbpending KB326485

Источник

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" alt="RRS feed" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif?cver=0001" />

Вопрос

Все ответы

SQL Server Transaction Log Backup Failing

How It Can Be Resolved?

Published by Andrew Jackson

failed maintenance plan [SQLSTATE 42000] (Error 22029)

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

failed maintenance plan [SQLSTATE 42000] (Error 22029)

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Asked by:

Question

Thread: failed maintenance plan [SQLSTATE 42000] (Error 22029)

Thread Tools

Display

failed maintenance plan [SQLSTATE 42000] (Error 22029)

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

failed maintenance plan [SQLSTATE 42000] (Error 22029) (reply)

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Asked by:

Question

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

Вопрос

Ответы

Все ответы

Sqlmaint exe failed sqlstate 42000 error 22029 the step failed

<img decoding="async" onError="javascript: wp_broken_images = window.wp_broken_images || function(){}; wp_broken_images(this);" src="https://i1.social.s-msft.com/globalresources/Images/trans.gif" />

Answered by:

Question

Answers

Error 22029 — SQLMaint.exe fails but works?

SYMPTOMS

CAUSE

WORKAROUND

STATUS

MORE INFORMATION

Читайте также: