SQL Server Performance Forum – Threads Archive

[SQLSTATE 42000] (Error 22029)

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 "E

rogram 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!

]]>