Still having issues on Cluster hosting 1400+ databases

jhboricuajhboricua Posts: 41
edited October 11, 2011 4:51PM in SQL Backup Previous Versions
This keeps happening on our 64-bit SQL 2005 Cluster even after upgrades, patches and registry 'fixes' sent to us by RedGate Support.

The issue is that when selecting this instance on the SQL Backup GUI, it goes into a neverending green spining circle but the backup history simply won't populate, even if left alone for hours.

We've opened 2 tickets on this with support and, after trying several things with them without success it has always come down to stoping the SQL Backup Agent service, deleting the data.sdf file on the SQL cluster and restarting the service again. This has the effect of wiping the backup/restore history and takes care of the endless spinning green circle, but eventually the issue comes up again as new backups start filling the history. Because we do weekly fulls, daily diffs, and hourly logs, my guess is that at some point the number of history entries in this file are so many that something gets messed up with the file, and that's sort of the feedback I'm getting from support.

The problem I have with this approach at 'fixing' is that it effectively WIPES all the history so now doing restores becomes a much involved process since SQL Backup doesn't know which files to use. It seems to me this reliance on the SQL Compact Database file data.sdf is the product's weakest link.

I must note that we are only experiencing this issue only on this SQL cluster which is unique by far as far as the number of databases it holds, 1400+ and counting. None of our other 15 licensed servers have such a high count of DBs, the next one is probably not even hosting 50.

Is anyone else running Full/Diff/Hourly backups on a server with a high count of databases and seeing this behaviour?

I'm strongly considering talking to our sales rep into converting the 2 licenses we bought for SQL Backup on this cluster into Hyperback and just going with straight SQL maintenance plans for backups if this issue isn't resolved. I need to be able to use the backup history for restores.

Comments

  • Thanks for your post.

    Can you check how frequently you're deleting the old backup history in the SQL Backup history and the msdb database? If the history isn't getting deleted then performance will degrade over time. These settings are in Tools > server Options > File management.

    Another thing you could try is to disable the timeline in the GUI.

    If you want to disable the SQB timeline, set the (DWORD value) HKLM\Software\Red Gate\SQL Backup\Client\DisableTimelineItemRequests
    to 1 to disable

    Hopefully one or both of these suggestions will help.
    Chris
  • Hi Chris, we've gone down to just 30 days retention on history from being initially at 90. I wouldn't want to go lower than that.

    What is the effect of disabling the timeline in the GUI? Will it still populate backup history in the GUI if I disable the timeline?
  • Yes, everything else will function the same, you just won't see the timeline. It's quite expensive on resources to populate the timeline, so if you don't mind not seeing it, it will probably boost the performance considerably.
    Chris
  • Chris,

    That sounds like a good option. If you add the regkey it will disable the Timeline if the value is 1 and if the value is something else it will still give you the Timeline?

    Does SQLBackup need to be stopped/started to make use of the regkey?

    Thaanks

    Chris
    English DBA living in CANADA
  • Hi Chris,

    It's only a GUI setting, so you don't need to restart the engine.

    I think 0 is on and 1 is off. I don't know what other values would do.

    I've just tried this myself and it seems that the timeline is still displayed, but it doesn't draw on the various indicators for the backups, so the interface still looks the same.

    If you're on 64bit, the reg key you need to create will be in wow6432node.
    Chris
  • Chris,

    Client looks like a completely new subkey in the registry for Red Gate\SQL Backup. I just looked at two of my 32 bit servers and they don't have the Client subkey.

    Are there any other publically known registry key values that could be usefull?

    Thanks

    Chris
    English DBA living in CANADA
  • Need some clarification.

    Where do I make this regkey change? On the SQL cluster or on the server I run the GUI on (which is not a SQL server)?

    If the later, the server where I installed the GUI for SQL Backup doesn't have a Red Gate\SQL Backup subkey.

    In HKLM\Software\Red Gate the only subkey showing is Downloads, under with there IS a SQL Backup subkey which in turn has a '6' subkey in it.

    In HKLM\Software\Wow6432Node\Red Gate I have the following subkeys:
      Downloads IPN Licensing SQL Changeset SQL Object Level Recovery Pro SQLToolsInstalledProducts Update

    So no SQL Backup subkey either.
  • Hi Jhboricua,

    This is a GUI setting so you need to create the registry key on the machine where the SQL Backup client is installed.

    To create the registry value, please follow these steps:

    1) Open Regedit.exe
    2) Navigate to:
    on 32 bit: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup
    on 64 bit: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup
    3) Right-click the SQL Backup key and select New > Key
    4) Call the key 'Client' (without the quotes)
    5) Within the 'Client' key create a New > DWord > DisableTimelineItemRequests
    6) Set 'DisableTimelineItemRequests' to value to 1

    I hope this helps.
    Chris
  • Are there any other publically known registry key values that could be usefull?

    Not that I know of. There used to be one for UI debugging, but that's not used any more.
    Chris
  • On a 64_bit server I added the key and with a value of zero it looks no different (expected) but a value of 1 I see no activity history. Is this what it is expected to do?

    Thanks

    Chris
    English DBA living in CANADA
  • On a 64_bit server I added the key and with a value of zero it looks no different (expected) but a value of 1 I see no activity history. Is this what it is expected to do?

    As far as I know, the registry key only affects what is drawn on the timeline, it doesn't have anything to do with the activity history. I tested this on one of my servers and it worked as expected. I haven't heard anything else to the contrary for any other users either.

    It's not really a supported feature of SQL Backup, so if it's not behaving for you, then I would recommend removing the key and retuning the setting back to normal.
    Chris
  • No change in behavior for the clustered server, even with the timeline disabled.

    Implemented timeline disable regkey on client computer, launched GUI, can see the timeline is disabled for everything listed. Selected a random server from the list, Activity History populated in under a minute.

    Selected the SQL server that's giving me problems per the opening post, 10 minutes later I still have a spinning green circle on it and no Activity History to show.

    So it is doesn't seem to be that activity timeline is what causing the issue. It is definitely the data.sdf file issue.
  • Do you happen to know what size the data.sdf file gets to when it starts causing problems?
    Chris
  • I do not. Right now the file is 153MB in size. I still have the old one (renamed it .old) and that one is 173MB in size.
Sign In or Register to comment.