MS SQL Server 2008 Database backup failures

Windows Server 2008R2, MSSQL server 2008, 32 GB RAM setup as a VM on xenserver 6.2

Unfortunately, I am not a Database administrator by any stretch, but am being thrust into that role with some issues we are having.

Using SQL Server Manager Studio we had a maintenance plan set up to make a full database backup every 6 hours

Database backups had expanded to the 37 GB range

About every couple of days, the plan would run, but it would not generate the maintenance plan text file that usually goes with the backup file.

When that happened, when we wanted to move that file off the the backup drive to storage, it was locked "by another process"

The next scheduled maintenance would not run, nor would manual backups when launched.

Rebooting the server is the only way we have found to unlock the file, and then it would be fine for a while.

Have since shut off the scheduled backup and have been doing manual backups to see if we could get more information from the system about what is happening.  One did fail last night and we got event 3041 "Backup failed .... check backup log for detailed messages."

Checking the logs by going to Management > SQL Server Logs I do not see anything too helpful during that time except an entry that says "Error 3041, Severity: 16, State: 1." at essentially the same time as the 3041 in the event viewer ("Backup failed .... check backup log for detailed messages.")

We also do get a few Error 833's ("I/O requests taking longer than 15 seconds ...")  during the time the full backups are running.

We have been also figuring out and researching the differential backup options, but could use some pointers as we are not sure of the mechanics of that.

The big question:  Why are the backups failing?
There must be more information somewhere in logs I am missing?
What is the best way to improve this situation?
What are the proper steps to have a successful full / differential backup / recovery strategy?
I am unclear how transaction log backups fit into this.  Pointers?
dustypenguinAsked:
Who is Participating?
 
Surendra NathConnect With a Mentor Technology LeadCommented:
error 833 suggests it is a disk issue, you can read more about it here
http://support.microsoft.com/kb/2137408/en-us
so I would suggest you to check the drivers are properly updated or not for the disk or better run a chkdisk..

did you try to check if there is enough space on the disk for this backup in the first place, if that is not the case then the backups will fail as well.

Differential backups

taking a full backup at 6 hours every time is a waste of disk space, you should definelty use the differential backups

You can take a full backup at sunday evening (when your server is having less load on it) and later you will start differential backups until you reach sunday.

You can find the below article helpful on how to do a differential backup
http://technet.microsoft.com/en-us/library/ms188248.aspx
0
 
dustypenguinAuthor Commented:
I'd be willing to break this question into two 500 point parts if that helps anyone.

1) The big question:  Why are the backups failing?
There must be more information somewhere in logs I am missing?

and 2) which I can resubmit as a second question:

What is the best way to improve this situation?
What are the proper steps to have a successful full / differential backup / recovery strategy?
I am unclear how transaction log backups fit into this.  Pointers?
0
 
dustypenguinAuthor Commented:
Thank you ... have read the 833 info earlier ... does not seem to be an issue until backups are going so was wondering if there was some other bottle neck ... could try a chkdisk.

What I am hearing you say is that you feel the backups are failing because of the something. to do with the 833.  Correct?

I had read the differential backup item as well, as well as some other documents, but was confused a bit about how transactional log backup fits into this as well.

If we do a full (simple) backup, and then we do differential (simple) backups, is there any reason to do transactional log backups as well ... and if so, how does that work to restore?

From what I have read, I think we could restore from the full using NORECOVERY option and them the latest differential with the RECOVERY option .... That would put us to a state as of the latest differential, correct?

But then what of the transactional log backups?  Little fuzzy there.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
David ToddSenior DBACommented:
Hi,

If running SQL 2008 Enterprise Edition or SQL 2008R2 Standard Edition (or better) then have you turned on backup compression?

Server Properties|Database Settings.

This will help keep the size of the backups down.

When the backup completes, how much free disk space is left? I've seen that when disk space reduces, the locks tend to stay on the file and they don't get cleaned up.

Instead of the maintenance plans, I prefer to use Ola Hallengren's free script
http://ola.hallengren.com/

I agree with some of the above - a full backup every 6 hours seems a little over the top. If you need a good backup at those times I agree with the recommendation to consider differential backups. But, at 37GB I think that this isn't too big to tolerate daily full backups.

Now the other thing is that the disk that the backup is stored on, doesn't need to be tier 1 15krpm raid10 disks. I think that it could be as low as a pair of large SATA disks in a raid1 mirror. Pretty much any current disk is going to be faster than tape.

HTH
  David
0
 
Anthony PerkinsConnect With a Mentor Commented:
is there any reason to do transactional log backups as well .
It is quite simple:
If you require point-in-time restores, then you must set your database to Full Recovery Model (the default) and make frequent Transaction Log Backups.

if you don't need point-in-time restores then change to Simple Recovery Model and don't worry about Transaction Log backups.
0
 
dustypenguinAuthor Commented:
Thanks for your patience on this.
 
Surendra Ganti
The initial problem of the backups failing seems to have been outdated drivers for this machine.  Specifically the underlying xenserver host got upgraded, but the drivers for the VM did not.  Testing backups since have shown this to have been the fix.

Anthony Perkins
Thanks for helping to clear up the diff vs full vs simple vs transactional issues I was fuzzy on.  We are now indeed doing 1 full a day and then diffs.

Thanks to all who participated.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.