What are the challenges you face when working across database platforms? Take the survey
Options

Checking Password takes a long time

PowerwayPowerway Posts: 29
edited July 17, 2007 9:39AM in SQL Backup Previous Versions
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

Comments

  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    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
  • Options
    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
  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    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
  • Options
    Hi Marc,

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

    All the best,
    Dan
  • Options
    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.
  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    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
  • Options
    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
  • Options
    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?
  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    I'm running 5.0.

    Marc
  • Options
    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
  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    Excellent! Thanks for the quick confirmation.

    Marc
  • Options
    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
  • Options
    marclallenmarclallen Posts: 53 Bronze 3
    Thanks again!

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

    Marc
  • Options
    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
  • Options
    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


    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
  • Options
    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
  • Options
    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
    Helen Joyce
    SQL Backup Project Manager
    Red Gate Software
Sign In or Register to comment.