DB Maintenance Plan fails routinely.
-
On a database set to Full recovery mode, I get the following messages routinely.
"Executed as user: domain\username. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed."
In the reprting folder I get the following message as well.
"[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3204: [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[Microsoft][ODBC SQL Server Driver][SQL Server]Operator aborted backup or restore. See the error messages returned to the console for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally."
Does anyone have any experience with this problem?
-
Where are you backing up the file to? If it's a network location then you could be having networking issues. SQL Server is very, very picky about network connectivity.
--------------------
Colt 45 - the original point and click interface
-
Phill, thanks for the response. I am attempting to back up the database to a different folder on the same physical server. The error in the maintenance log suggests an ODBC problem but a review of the connection shows that it is working fine.
-
Hi,
how is the job started? If you start it from a linked server, you might be running into the connection-timeout...
karl
Best regards
karl -
Does the SQL work from a QA window ? Is your MDAC up to date ? Does the Backup (Maintenance Plan) have 'VERIFY' checked ? If it does try removing the 'VERIFY' ... just a few Q's to start with ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Has it ever run successfully? I would also make sure the user attempting the job has priveleges to run it.
My hovercraft is full of eels.
-
Do you put your system database and user database in the same maintenance plan?
I think you have to separate them into 2 different plans. You can look for this in Microsoft web site (i forgot what is the url, sorry
).Leo
-
First, thank you all for your replys. I haven't responded because I'm been busy trying to resolve this issue through Microsoft Support. Surprisingly enough, they have not provided a solution.
This post will hopefully answer all questions posed. The server starting the maintenance plan is not linked to another server. The plan worked consistenly for 2 years. About 3 months ago it started to fail intermittently. Now it fails consistently although the transaction log does get backed up. I have separated the system databases from the user databases in this plan and it still fails. I have turned off all options: confirm backup, optimize, and verify.
Most recently I am receiving the following message:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 3204: [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[Microsoft][ODBC SQL Server Driver][SQL Server]Operator aborted backup or restore. See the error messages returned to the console for more details.
[Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.
-
It's a stumper ... so back to the basics ... What's your SQL version and SP ? How about MDAC version ? Are you using a domain account for the SQLAgent and SQL Server or LocalSystem ? If you are using domain accounts has any AD policy changed lately ?
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Hi,,
I am Facing the same problem were you able to fix the issue.
Please reply.
-
We were suffering from a similar issue. I had a standard-issue maintenance plan in place to check db integrity, perform the backup and delete bak files that are > 2 days old. Maintenance plan said all worked nicely, each step accounted for with a 'success' marker and a little description of it's task.
However, while the backups worked, the old files were not actually being removed. It all boiled down to security on the backup folder - the service account wasn't included in the folder permissions. No errors were thrown at all, nada. Nothing from SQL Server or the O/S. This little MS oversight gave us a production outage and a 6 hour call to their support line.
Perhaps this ditty may help you out too. Issues sound similar.
-
We are having this issue with a job that restores our reporting database nightly. When you run the restore manually through QA everything works. When it kicks off in the wee hours of the morning it performs the delete of old files and copy of new ones ok, and even looks to be restoring the database. There is a fourth step that makes changes to the db and this fails with the error:
Executed as user: . Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0). The step failed.
Through testing of this issue it has been found that the restore of the db happens twice but this only happens when running it through the job - when you run the same code in QA it only runs once. I have walked through it, picked it apart and I can't find anything that would do this.
I saw where is was mentioned about rights on the directory - the login being used has more than sufficient rights through AD to do anything needed but is not specifically a listed user for any of the directories that are being referenced.
We are thinking it is a server issue but have no clue what to look at. This job was running successfully for months and then this suddenly started happening week before last. I am a new DBA and have been working with our sr. DBA - he is also stumped.
-
Dear all:
I have this problem as well.
From Server Report I got the Following Jobs have failed:
DB Backup Job for DB Maintenance Plan 'DB Maintenance Plan' Last Run Date: 04/27/2008 ---
Integrity Checks Job for DB Maintenance Plan 'DB Maintenance Plan' Last Run Date: 04/28/2008 ---
This job has been failed, so I recreated it again with only one database. It is SQL 2000, it backup to network server. The backup backup fine. But I got the error as below:
Error:
[Microsoft SQL-DMO (ODBC SQLState: HY000)] Error 0: [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
[Microsoft SQL-DMO (ODBC SQLState: 01000)] Error 0: This server has been disconnected. You must reconnect to perform this operation.
(null)
Deleting old text reports...
1 file(s) deleted.
(null)
SQLMAINT.EXE Process Exit Code: 1 (Failed)
(null)
Msg 22029, Sev 16: sqlmaint.exe failed. [SQLSTATE 42000]
Can anyone help? Thank you very much,
-
Made some changes to the job last night - added in lines that comment to the event log. Based on these comments that restore is running twice, beginning to end. Why would a step in a job be executed twice even though there is nothing there telling it to run twice?
-
hi all
i have the similar problem
till yesterday the backups where fine on the tape..
got this error last night
Description: Error encountered during backup. Error: [ERROR: [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [Microsoft][ODBC SQL Server Driver][SQL Server] [Microsoft][ODBC SQL Server Driver][SQL Server]BACKUP DATABASE is terminating abnormally.]
could not find any errors on the error.log file except this warning
Warning: unable to allocate 'min server memory' of 2009MB.
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply