Still having issues on Cluster hosting 1400+ databases
jhboricua
Posts: 41
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.
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
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.
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?
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
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.
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
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.
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.
Not that I know of. There used to be one for UI debugging, but that's not used any more.
Thanks
Chris
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.
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.