Error 22029 - SQLMaint.exe fails but works?

[Catadmin]

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???

 

[SQLBill]

Is something else running at the same time, maybe using the files you are saving the backups to? For example, is there a tape backup job that copies the backups to tape? Maybe it's running when the backup attempts to run.

Have you right clicked on the failed job and selected View Job History? If not, do so then check the Show step details box, that should show exactly which step of the job failed.

Also, as always, the Windows Event Viewer logs might have some messages that relate to this.

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

I have to get back into that office, which won't happen until around 4, before I can check the error logs. The person I did this for, though, says there was an error when verifying the backup (Cannot open backup device) after a supposed successful backup occurred.

We're using the hard drive as our backup device. The backups occur every 24 minutes on a machine that is seldom used for other stuff but does include an old reference database accessable from the web. The occasional job conflict doesn't explain to me why the job has been consistantly failing since last Thursday, though.

If a verify of a backup fails, it automatically deletes the backup right?



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???

 

[SQLBill]

If a verify of a backup fails, it automatically deletes the backup right?

NO. Just that step failed.

What may be happening, is that when a backup finishes, the verify starts, but before it can begin another backup may be starting. Check how long each backup takes...are any of them taking longer than 24 minutes?

-SQLBill

Posting advice: FAQ481-4875

 

[Catadmin]

Ahhh. Now there's something I hadn't considered. Thanks, SQLBill. @=) Will definately check job times when I get into the office.



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???

 

[Catadmin]

Job duration was all of 16 seconds. Here's the error message from the log (with names changed for privacy reasons):

Job 'TransLog_Backup_for_MyDB' : Step 1, 'Step 1' : Began Executing 2005-08-03 16:24:02

Output of sqlmaint.exe ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
(null)
Microsoft (R) SQLMaint Utility (Unicode), Version 8.00.194
Copyright (C) Microsoft Corporation, 1995 - 1998
(null)
Logged on to SQL Server 'MySQLServer\InstanceName' as 'MyDomain\loginName' (trusted)
Starting maintenance plan 'TransLogBackup' on 8/3/2005 4:24:06 PM
[1] Database MyDB: Transaction Log Backup...
Destination: [R:\Backups\SQLBackup\MyDB\MyDB_tlog_200508031624.TRN]
(null)
** Execution Time: 0 hrs, 0 mins, 6 secs **
(null)
[2] Database MyDB: Verifying Backup...
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 320
[Microsoft][ODBC SQL Server Driver][SQL Server]VERIFY DATABASE is terminating abnormally.
End of maintenance plan 'TransLogBackup' on 8/3/2005 4:24:14 PM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]

Anyone see this one before?



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???

 

[mrdenny]

Try manually verifying the file.

restore VERIFYONLY from disk='r:\Backups\SQLBackup\MyDB\MyDB_tlog_200508031624.TRN'

This may give you more info.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[Catadmin]

The problem is, there is no file to verify. That's why I was wondering if a failed verify deleted the file. The file supposedly succeeds and fails on verify, but when we look in the windows explorer path, there's nothing there.

I just found a Microsoft KB article which looks related to this issue, but may or may not be:

http://support.microsoft.com/default.aspx?scid=kb;en-us;827452

It deals with a bug with SQL Server 2000 on Windows 2k3 Server.

Now if I can just convince them to give this hotfix a try...



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???

 

[mrdenny]

Try removing the verify requirment and see if the file is created and if the job fails.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(Not quite so old any more.)

 

[SQLBill]

Execution Time: 0 hrs, 0 mins, 6 secs

I have never had a log backup take only 6 seconds. It almost looks like nothing got backed up and since there wasn't anything to verify, it failed.

-SQLBill

Posting advice: FAQ481-4875

 

[smedvid]

Strange, I have a similar issue...

I have a sporadic error that appears on a SQL Server Backup Job. The job history displays the following failure message "sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029)". Although, the BAK file does appear to be created and is even the approximate size of the previous successful backup file.

I created the backup job via the maintenance wizard. I only execute the backup step, I do not perform any other maintenance within that job. I perform shrink and optimization in seperate database jobs. Those jobs always succeed. My backup job does perform the verify step amd the backup file target is a network share directory. I have tried to alter the start time of the backup job, and the sporadic failures still occur; perhaps once a week.

Any clues are appreciated. tia,



Steve Medvid
"IT Consultant & Web Master"

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...