Maximum amount of databases

[Richardbennett]

We have been having serious server instability on SQL server2008 installed on a virtual server on VMware ESX.
It has 12 gig ram and 4 cpus.
The server would blue-screen and reboot several times a day with an eventlog error about ntfs corruption.
I had about 130 virtually restored database, and of these about 20 would become corrupt during the day. No normal databases became corrupt.
I am using Hyperbac v 5.1.13.0 to back-up to a zipfile, and virtually restore from that.

We tried migrating the VM to new hardware, re-installed SQL server, re-installed Hyperbac, rebooted, performed checkdisk etc.

Finally I deleted 20 virtual databases and the corruption stopped.
After 12 hours without corruption or reboot I added another 100 virtual databases, within a few hours they started to become corrupt again, even though they were not in use.

Is there a known limit to the amount of virtually restored databases on a single server? Is there a work-around for this?

Thanks, Richard.

[RajK]

I am the support engineer that is investigating this behaviour you have reported. This forum post has opened a support request in our ticketing system. All future replies will be sent via the support system.

At this stage I am going to suggest a quick change if possible for you to try out on your side to circumvent this behaviour.

Since such a high number of SVR databases are being mounted online the restart is going to be a period of high-activity. This activity will be in terms of High Disk IO and CPU cycles. In addition to this stress SQL Server will be trying to figure out if these SVR databases are all ok or it needs to put them in suspect mode pending successful recovery.

To get around this scenario and to put less resource pressure on the system I would recommend that you try to put all these SVR databases in offline mode before a restart. On a restart please ensure that HyperBac service is successfully online and then sql server service starts up in this particular sequence. At this stage I won't request that a dependency be created but this might need to be a possible option if the behaviour is still causing issues. Currently, just ensure that the databases are in offline state before a reboot.

After a reboot:
When the server is successfully restarted and then the various services are online I would recommend that you try to bring the databases online one at a time. This will create the necessary delay to not cause any resource contention.

Thanks for your feedback and patience in this matter.

[Richardbennett]

Hi,
Thanks for looking into this.
I do already have a stored procedure that I can use to bring all virtual databases offline during a server restart.
The issue is however not during restart, but during regular use.
I now have 111 virtual databases and all is fine. If I create another 30 virtually restored databases I start seeing corruption occurring in about 20 databases. This happens at random during the day and also at night when no one is using any databases.
If I then drop enough databases and keep the number under about 110 they work fine without any corruption occurring (previously corrupt databases have to be dropped of course, they are not recoverable)

Thanks for your ideas and tips.

[RajK]

SVR is regularly tested with concurrent restores of upto 80 databases (varying from 5GB to 80GB in size). Is it possible to review your database sizes?

It is also worth trying this on a server with more cores and more memory. You have mentioned 4 cores and 12GB ram. I run stress tests with 24GB RAM and 8 cores. If you can increase the resources for this VM than that would definitely help! Please let me know what your current resource allocation is.

The stability issue is being debugged via the support ticket that has been generated. I will send you another email shortly with more details for your investigation.

Thanks for your patience in this matter.

[Richardbennett]

Hi,
Thanks for the patch.
I installed this and it did show an improvement, I can now run 125 databases without any corruption. When however I added another 30 databases a a test two of them corrupted again, so there still seems to be a limit.
The behaviour of the file-locking also seems to have changed, both the data files and the backup files sometimes remain locked far longer after a database has been dropped, sometimes requiring the HyperBac service to be restarted to free the files. I also get the impression that the RedGate\Indexes folder does not seem to clear itself when a database is dropped, as there are 30 GIGs in many folders there now. Are there any guidelines as to what can be deleted from that folder to free up space?
Thanks for all the help!

[RajK]

In regards to the maximum number of databases you may have reached the limit as far as the hardware resources are concerned. There is only so much benefit a software solution might be able to provide in this use case. Are you able to let me know what are the native online sizes of the databases that are being virtual restored?

In regards to the file-locking behaviour can you confirm how many databases are you restoring from each backups? Is there a 1:1 relationship between a SVR database and a backup?

You should be ok to delete the contents of the indexes folder as long as there are no corresponding databases. If you are constantly creating and deleting new SVR databases then it might be good to put this delete as an additional step after the delete step.

Are you able to send us your serial number details to the support works email that corresponds to this case? Thanks.

Thanks for your feedback in this matter.