Solved

MS SQL Server 2008 Database backup failures

Posted on 2014-01-29
6
635 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now