Database maintenance task (full back up) fails on SQL Server

240 pts.
Tags:
Backup to Disk
SQL Server 2005
SQL Server 2005 backup
SQL Server 2005 maintenance
SQL Server administration
SQL Server backup
SQL Server maintenance
Hi My daily maintenance job fails due to the below error from last 20 days regularly Executing the query "BACKUP DATABASE [shipment] TO DISK = N'D:backupshipment.bak' WITH NOFORMAT, NOINIT, NAME = N'shipment', SKIP, REWIND, NOUNLOAD, STATS = 10 " failed with the following error: "Write on "D:backupshipment.bak" failed: 112(error not found) BACKUP DATABASE is terminating abnormally. 10 percent processed. 20 percent processed. 30 percent processed. 40 percent processed. 50 percent processed.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. very recently i have changed the recovery mode from Full to simple and truncated the log file and i changed back the recovery mode to Full. Does this affect the db.? The maintenance plan was set to take the back up of full db. How to sort this issue. Sql 2005 on sp2. Any ideas will help me. Thanks
ASKED: November 28, 2008  5:38 PM
UPDATED: July 10, 2009  2:55 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Deleting the post from someone else since the solution was to add more disk space, and we resolved this in early December.

Discuss This Question: 7  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Denny Cherry
    Check your ERRORLOG file. It will provide more information about why the backups are failing in there. Can you post all the data from the ERRORLOG related to the backup failure? If needed just zip up the file and upload it somewhere and post a link to the file, and I'll find the correct lines in it.
    65,485 pointsBadges:
    report
  • GIG
    Thanks for your reply, i checked the error logs. there is no enough space on the drive, i have created the space and the backup is sucessful. I just want to know if the database is affected if we change the recovery from full to simple and then back to full, truncating the log file. does the backup created can be restored sucessfully if we do this changes on the recovery.? thanks..
    240 pointsBadges:
    report
  • Denny Cherry
    If you do the full backup after you switch the database back to full recovery then you will be able to restore the database and roll any future log backups to it. You will not be able to restore from the log backups before this operation through to the log backups taken after this operation as the log chain will have been broken. If you need to clear the transaction log simply to a
    BACKUP LOG YourDatabase WITH TRUNCATE_ONLY
    . This will clear the log of all it's entries. As soon as you run this command you will need to perform a full backup, and just like changing the recovery mode you will not be able to restore from the logs before this statement is run to the logs after this statement is run because the log chain will still be broken.
    65,485 pointsBadges:
    report
  • GIG
    Thanks for your replu MRdenny. So can you suggest me a solution as my log file are contantly increasing and occupying a lot of disk space. so i have started truncating the log file. The whole size of the db is now 380 GB. The file growth is 100 MB. Full backup taken every day. On the database properties, i could see availiable space is 90 GB. Is there a possible way to reduce this space. I used shrinking, but did not help me. can you please let me know how to manage all this stuff. sql 2005 sp2.
    240 pointsBadges:
    report
  • Denny Cherry
    If you don't need to keep the transaction log the change the log to simple recovery. If you do need the log, backup the transaction log on a regular basis (every 15-30 minutes is normal) and the log will stay empty. After the first log backup you can then shrink the file smaller. If you keep the database in full recovery and don't backup the log then there is no point in keeping the database in full recovery mode as you can't do a point in time restore without log backups.
    65,485 pointsBadges:
    report
  • GIG
    Thanks for your inputs..MrDenny.
    240 pointsBadges:
    report
  • Proud2bBrown
    Hi Mrdenny, I have the same problem as GIG's. I have a database full backup maintenance plan that fails and shos the same error as GIG's. I checked disk space and I have 471GB available. My database is just about 18GB in size. What could be causing the maintenance plan to fail? Please help. Thanks in advance.
    15 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following