Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

MS SQL Server 2008 Database backup failures

Posted on 2014-01-29
6
651 Views
Last Modified: 2014-02-13
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?
0
Comment
Question by:dustypenguin
6 Comments
 

Author Comment

by:dustypenguin
ID: 39818517
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
 
LVL 16

Accepted Solution

by:
Surendra Nath earned 450 total points
ID: 39818788
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
 

Author Comment

by:dustypenguin
ID: 39818997
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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 35

Expert Comment

by:David Todd
ID: 39819420
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
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 39819598
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
 

Author Closing Comment

by:dustypenguin
ID: 39857246
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

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
"Disruption" is the most feared word for C-level executives these days. They agonize over their industry being disturbed by another player - most likely by startups.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question