Checking Password takes a long time

Powerway

We have a 40 gig SQL Backup file created with 4 threads. When we go to restore it, the checking password phase takes 25 minutes of high I/O. This is almost instantaneous when threads are not used.

Can someone explain why this takes so long?

Thx

marclallen

Interesting. I have a similar issue, but don't know if it's really related.

I have an 80Gb file, created with three threads. I have no password, but it locks on "Checking Backup Files..." after adding it to the restore wizard. I also notice that the server where the file resides starts seeing high network utilization. I assumed that the entire file was being read.

I have not tried it with only a single thread.

If I manually execute the SQL to restore the database in QA, there's no delay in starting.

I had assumed that the problem was some sort of validation or checksumming, but maybe not. In any event, my thread on the similar issue may bear watching as well.

Marc

Dan J Archer

Hello all,

Interesting, I'll take a look at these - can you let me know what other settings you had for those backups, if they're easily to hand. We'll try and reproduce this delay locally and see about investigating why it's happening.

All the best,

Dan


Dan J Archer
Red Gate Software

marclallen

Dan,

Thanks for the reply.

I'm new to SQL Backup, so I wasn't doing anything fancy. The restore file is on a network share, and the target machine, the share machine, and the machine where I am running SQL Backup PRO are three different machines. Finally, my backups had no password. They were created with three threads, minimum encryption.

I simply started the Restore wizard, opted for selecting a backup file, selected "XXX Network Shares" or whatever, navigated to the share and then my file. When I hit "accept", it loaded the line in the restore list, then went to "Checking...". After about 30 seconds I checked and saw 250MB/s network utilization on my share.

Sorry if I have any details inaccurate.. I'm not at my office at the moment. If you have any other questions, suggestions or tests to run, please let me know.

Marc

Dan J Archer

Hi Marc,

Thanks, that's useful. We'll try and reproduce it locally and get back to you, hopefully tomorrow.

All the best,
Dan

Powerway

Our backups are at compression 2, 128-bit encryption, 4 threads, no verify.

Like I said, if just 1 thread is used, the password seems to be validated immediately.

I realize that when threads are used, multiple files are created. Not sure if this has something to to do with it.

marclallen

My backup file is a single file. At least it is a single physical file. I just assumed that there was a 'thread marker' or something in each record so that the restore could untangle it. Perhaps not.

Marc

Dan J Archer

Thanks for your replies.

Can I confirm you're both using SQL Backup 5? Backup 5 should indeed generate one file regardless of the number of threads.

All the best,

Dan

Powerway

Dan J Archer wrote:

Thanks for your replies.

Can I confirm you're both using SQL Backup 5? Backup 5 should indeed generate one file regardless of the number of threads.

All the best,

Dan

Yes SQL Backup 5.1. The multiple file thing I was referring too was I read that inside the one file there were actually multiple streams/files. Is this true?

marclallen

I'm running 5.0.

Marc

Dan J Archer

Thanks to you both for that.

You're right, with multiple threads there is interleaved data in the file as backed up by those threads. This is to maximise the speed of the backup, as it avoids the threads waiting on each other in order to write the backup file in a precise sequence.

As an update, re the time taken to check passwords/validate backups performed with multiple threads: we've confirmed similar behaviour under certain conditions in our test suite - we're just isolating exactly what the cause is, and exactly when it happens. I'll get back to you as soon as I know more.

Thanks for your help so far.

All the best,

Dan

marclallen

Excellent! Thanks for the quick confirmation.

Marc

Dan J Archer

Hi all,

Another update: we've isolated the problem to use of the RESTORE FILELISTONLY command with backups created with multiple threads. SQL Backup 5.0 and 5.1 end up verifying the entire file during this process, which naturally takes some time if the file is large. The UI uses this command in order to work out which files each backup file contains.

We've got a fix in progress for this at the moment. This should make it into the 5.2 release of SQL Backup. I don't have an exact timetable for that release as yet, but if anyone has an urgent need for a fix for this, please private message me and I'll see what I can do

All the best,

Dan

marclallen

Thanks again!

Now if someone could help me out with my failing backups...

Marc

Powerway

Not urgent at all. We have people restore in our SQA department and they were asking why a restore took 20 minutes to do the checking password.

I'll tell them something is in the works for the next release.

Thanks

mikebridge

Hi-

I'm also encountering this problem---is there any workaround for it until 5.2 is released? Or any technique for avoiding it? It's been "Checking backup files...." for 30 minutes on a backup that has level-1 compression, created with a single thread.

Thanks,

-Mike

Dan J Archer wrote:

Hi all,

Another update: we've isolated the problem to use of the RESTORE FILELISTONLY command with backups created with multiple threads. SQL Backup 5.0 and 5.1 end up verifying the entire file during this process, which naturally takes some time if the file is large. The UI uses this command in order to work out which files each backup file contains.

We've got a fix in progress for this at the moment. This should make it into the 5.2 release of SQL Backup. I don't have an exact timetable for that release as yet, but if anyone has an urgent need for a fix for this, please private message me and I'll see what I can do

All the best,

Dan

mikebridge

Ok, apparently this is a GUI issue---the "Checking backup files..." screen never goes away, but the "Next >" button does indeed become available. (This might be an issue with logging in via Remote Desktop?)

-Mike

mikebridge wrote:

Hi-

I'm also encountering this problem---is there any workaround for it until 5.2 is released? Or any technique for avoiding it? It's been "Checking backup files...." for 30 minutes on a backup that has level-1 compression, created with a single thread.

Thanks,

-Mike

HJoyce

We are able to give you a patch release of the server components which includes a fix to the issue where the RESTORE FILELISTONLY command can take a long time when using a large backup file created using multiple threads. This will no longer read the whole file which will significantly reduce the time taken to run this command.

Please contact support@red-gate.com and they will be able to give you the patch release.
Thanks,
Helen