Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

MS SQL Server 2008 Database backup failures

Posted on 2014-01-29
6
Medium Priority
?
712 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 1350 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 150 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

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

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

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 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