March 6, 2014 at 7:20 am
I'm running the following command to backup a 500GB database to a USB drive that's attached to the server.
Before I generate my backup command I work out the estimated size of the backup file, divide that by 50GB (I don't want a backup file that's more than 50GB in size) then generate the backup statement.
However, when the below is run it seems to get stuck at around 16% completion and nothing happens!
BACKUP DATABASE [MyDatabase]
TO
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-1.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-2.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-3.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-4.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-5.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-6.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-7.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-8.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-9.Full',
DISK = N'G:\[11]-MyDatabase-[20140306]-Part-10.Full' WITH INIT, BUFFERCOUNT = 1024, CHECKSUM
I keep running : select percent_complete from sys.dm_exec_requests where command = 'backup database' to monitor progress but there is no movement once it gets to 16%
The wait type for this thread seems to be ASYNC_IO_COMPLETION which is fair enough but I'd expect the percent_complete to change?
Any hints/tips please?
p.s.
This happened yesterday too, once again it got to around 16% and the backup just got stuck. the USB drive was no longer accessible. I had to kill the thread, unplug the USB drive. This morning I plugged in a different USB drive but the same issue seems to be happening at around 16% through the backup process.
Thanks.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 6, 2014 at 7:55 am
Usually, but not always, ASYNC_IO_COMPLETION is caused by client side slow downs. The secondary cause is from network processes. But, in this case, it sounds like an issue with the USB connection. Just as an experiment, have you tried backing up to a single file. I know it's not what you want, but the best way to troubleshoot is to eliminate as many variables as possible in order to arrive at the root of the problem. Maybe the issue has to do with splitting the files in combination with a slow USB drive.
By the way, why do you want to split the files? In this case you won't see performance increases because you're writing to multiple disks. Just curious.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2014 at 8:04 am
Grant Fritchey (3/6/2014)
Usually, but not always, ASYNC_IO_COMPLETION is caused by client side slow downs. The secondary cause is from network processes. But, in this case, it sounds like an issue with the USB connection. Just as an experiment, have you tried backing up to a single file. I know it's not what you want, but the best way to troubleshoot is to eliminate as many variables as possible in order to arrive at the root of the problem. Maybe the issue has to do with splitting the files in combination with a slow USB drive.By the way, why do you want to split the files? In this case you won't see performance increases because you're writing to multiple disks. Just curious.
Thanks for the suggestion Grant. I'm going to try backing up the database to a single file and see what happens.
The IT director (we're a small company lol) suggested that no backup file should be bigger than 50GB in size and so I followed his instructions. As you said, there won't be much performance gain as I'm backing up to the same disk.
Will report back in the next couple of hours.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 6, 2014 at 8:31 am
1024 is a very large value for BUFFERCOUNT, isn't it?
Any reason you've set this rather than not specify it (I think it's automatically tuned based on the number of output files if you don't set it anyway)?
March 6, 2014 at 8:54 am
Have you tried backing up to another attached disk on the server, then copying to USB? Or, attach the USB to your local machine or another server. Backup via UNC path to workstation/server and copy to USB. I'm just trying to think of a workaround.
March 6, 2014 at 9:04 am
HowardW (3/6/2014)
1024 is a very large value for BUFFERCOUNT, isn't it?Any reason you've set this rather than not specify it (I think it's automatically tuned based on the number of output files if you don't set it anyway)?
Hah and I thought it's too small! When I was testing this a year ago I tried various settings and in the end I settled for 1024.
However, I am open to suggestions.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 6, 2014 at 9:05 am
Gary Gwyn (3/6/2014)
Have you tried backing up to another attached disk on the server, then copying to USB? Or, attach the USB to your local machine or another server. Backup via UNC path to workstation/server and copy to USB. I'm just trying to think of a workaround.
Not a lot of space on the server I'm afraid, hence the use of USB drives. This isn't a one-off exercise I'm afraid. This is part of the weekly backup process so I need to find a solution. 🙁
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 6, 2014 at 9:14 am
Abu Dina (3/6/2014)
The IT director (we're a small company lol) suggested that no backup file should be bigger than 50GB in size and so I followed his instructions. As you said, there won't be much performance gain as I'm backing up to the same disk.
Any idea why? I'm not arguing against it per se. I'm just trying to understand what you're achieving with it. SQL Server backups aren't like file exports, so limiting the size to some arbitrary value just seems odd to me. It's not something I've read about as a best practice anywhere. But, I'm always interested in learning more.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2014 at 9:22 am
Grant Fritchey (3/6/2014)
Abu Dina (3/6/2014)
The IT director (we're a small company lol) suggested that no backup file should be bigger than 50GB in size and so I followed his instructions. As you said, there won't be much performance gain as I'm backing up to the same disk.Any idea why? I'm not arguing against it per se. I'm just trying to understand what you're achieving with it. SQL Server backups aren't like file exports, so limiting the size to some arbitrary value just seems odd to me. It's not something I've read about as a best practice anywhere. But, I'm always interested in learning more.
No that is fine. In fact I just asked him again to explain to me his choice of 50GB and his explanation is along the lines of it being less risky, in other words he believes there is less chance of something going wrong when writing to multiple smaller files than writing to one big backup file.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
March 6, 2014 at 9:22 am
Abu Dina (3/6/2014)
Gary Gwyn (3/6/2014)
Have you tried backing up to another attached disk on the server, then copying to USB? Or, attach the USB to your local machine or another server. Backup via UNC path to workstation/server and copy to USB. I'm just trying to think of a workaround.Not a lot of space on the server I'm afraid, hence the use of USB drives. This isn't a one-off exercise I'm afraid. This is part of the weekly backup process so I need to find a solution. 🙁
Ah. I see. If adding additional drive space is not feasible, do you have another server with available space? Share and secure the folder, then configure your backup command to direct the backup to the share. Just an option if you have somewhere to store the backup, since this will be a permanent routine.
March 6, 2014 at 11:09 am
Abu Dina (3/6/2014)
No that is fine. In fact I just asked him again to explain to me his choice of 50GB and his explanation is along the lines of it being less risky, in other words he believes there is less chance of something going wrong when writing to multiple smaller files than writing to one big backup file.
Interesting. I think I'd probably argue against that, or at least argue that there's pretty much an equal chance of it going wrong no matter what. However, it will making copying the individual files easier. You'll still have to copy everything, every time.
Just so you know, splitting the files like that makes recovery that much more difficult since you have to have every single file in order to restore the database. No exceptions.
I'm not saying you shouldn't do it, but I don't see a great benefit based on what you're describing. If you had multiple drives too, oh yeah, huge benefit (with some cost) but without them. Nope.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 6, 2014 at 11:34 am
Try to backup with compression. In this case, the combined backup size will be around 150-200 GB, so there is a chance you'll be able to find a space on your disk, not USB. Try to remove buffercount. And BTW, what is the total and available size of your USB drive?
March 6, 2014 at 2:04 pm
Are you on 2008 or 2008 R2? If 2008 R2 you should be using the native compression - which on a 500GB database could get you down to less than 100GB (or 2 files).
If you are still on 2008 and Standard Edition - then compression is not available. If you have Enterprise Edition it would be available.
If you cannot use native compression, I would recommend that you take a look at any of the 3rd party backup utilities that provide compression like Redgate's SQL Backup or Quest Litespeed.
Using compression may allow you the ability to backup locally and copy to USB which could improve how long it actually takes to perform the backup.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 6, 2014 at 3:51 pm
SQL Guy 1 (3/6/2014)
Try to backup with compression. In this case, the combined backup size will be around 150-200 GB, so there is a chance you'll be able to find a space on your disk, not USB. Try to remove buffercount. And BTW, what is the total and available size of your USB drive?
You can't be sure how much compression he'll get. It's very dependent on the type of data being compressed. Some is more conducive than others. I don't disagree. Compression on backups is good, and a good idea here. But the amount is not always going to be well over 50%, even up to 70% as you suggest.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
March 7, 2014 at 3:15 am
Grant Fritchey (3/6/2014)
Interesting. I think I'd probably argue against that, or at least argue that there's pretty much an equal chance of it going wrong no matter what. However, it will making copying the individual files easier. You'll still have to copy everything, every time.
That's what I felt like saying to him but you know, I'm 3 weeks into my notice period (1 week to go before I leave for my new job) and I didn't bother debating the 1 vs many backup files.... let him have it his way.
---------------------------------------------------------
It takes a minimal capacity for rational thought to see that the corporate 'free press' is a structurally irrational and biased, and extremely violent, system of elite propaganda.
David Edwards - Media lens[/url]
Society has varying and conflicting interests; what is called objectivity is the disguise of one of these interests - that of neutrality. But neutrality is a fiction in an unneutral world. There are victims, there are executioners, and there are bystanders... and the 'objectivity' of the bystander calls for inaction while other heads fall.
Howard Zinn
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply