December 23, 2004 at 3:33 pm
On one of our servers (this is a development server so it is not a major issue but) there is one database that is giving me a persistent problem. Along with all the other user databases, this database is also in a maintenance plan. All the databases get backed up daily and for every databases, the backup is successful. However, for this particular database, lets call it ‘ProblemDB’, the backup occassionally fails. The error message in the SQL logs is as below:
“BACKUP failed to complete the command BACKUP DATABASE [ProblemDB] TO DISK = N'D:\MSSQL\BackUp\ProblemDB\ProblemDB_db_200412231400.BAK' WITH INIT , NOUNLOAD , NOSKIP , STATS = 10, NOFORMAT”
The job also displays as failed and displays a message as mentioned below:
“Executed as user: NT AUTHORITY\SYSTEM. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.”
As I said before, the backup does not always fails. It will fail two days in a row and the backup for this database would go fine for the next couple of days and then this problem will occur again.
If I manually take a backup of the DB, the backup and it’s verification goes through smoothly and a backup file is created.
Next step, I try to restore the backup and while restoring this DB I get the following error message:
“ConnectionRead(WrapperRead()).
General network error. Check your network documentation.
Processed 1 pages of database “ProblemDB”, file “ProblemDB_Log” on file 1.”
At this point, inspite of a failed restore message, the database is created and I can see it in the Enterprise Manager. The physical data and log files are created on the local folders as specified with the same exact size as the files that were backed up. So, now I run DBCC CheckDB on this database and it tells me that there are no errors in the DB. Rows and data in the tables is intact and nothing seems to be missing.
The backup and restore on happening on a local drive, so there is no question of network authentication involved. I am using an user account that has administrative privileges to all the database servers. There are no errors reported in the System and Application Events log at or around the time of the backup failure.
The server is Windows Server 2003 with SQL Server Enterprise Edition 2000 (8.00.760) with SP3. In addition the server has Visual Studio .Net 6.0, Visual Source Safe, Microsoft BizTalk Server 2002. But, as I mentioned earlier, there are not other errors any close to the time of the DB backup error that would cause me to suspect if any other application was clashing on some level with this DB on SQL server to cause this error.
Also, I have seen this error with the Model DB, but very very rarely (only twice yet in 6 months) and it was never at the same time with the ProblemDB.
Has anyone seen anything like this before? Anyone has any ideas as to what is going on here or what I should be looking for ?
Thanks in advance.
p.s. I should mention, that this DB (the ProblemDB) is used by the BizTalk server and there are no BizTalk errors.
December 27, 2004 at 8:00 am
This was removed by the editor as SPAM
December 27, 2004 at 9:42 am
I've seen similar errors in applications ad DTS packages, though not in any of our backup processes.
The general network errors I've seen has been pointed to a network bottleneck at that time. We had one a year ago that was caused by our heavy network traffic during our server backup process. After we rescheduled the job to run at such a time when the network traffic was low, the problem ended.
Just a thought.
December 29, 2004 at 6:57 am
http://www.dbforums.com/t812439.html
This might help, Seems to different views but MVP and some suggestions for you to go on.
Sorry cannot be any more help,
James
September 12, 2006 at 3:59 am
This problem is one limit of mdac 2.7 of server 2003 by design
so in the next version 2.8 include in service pack 2 there is a resolution
November 25, 2006 at 8:29 pm
I have a similar problem. That is, I have jobs running every day performing backups of all the user databases.
For all, except 1 database, no problems ever occurred. For one of the databases, every 5 days or so, I get the following error: "BACKUP failed to complete the command ...".
When I check the jobs - no jobs failed.
When I check "Database Maintenance Plan History" - it shows that this "Faulty Database" has been backed up and the backup was verified. It is as if the 1st attempt for backup was made - it failed (producing the error mentioned above) - but a retry was successful.
I did not do any tests yet to ensure the successful backup. But in general, if the "Database Maintenance Plan History" states that the backup was successful and verified, can we trust it completely?
Thanks a lot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply