Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

MS SQL Server 2008 Database backup failures

Posted on 2014-01-29
6
Medium Priority
?
695 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
[X]
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
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

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 part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

722 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