First a little description of the system that I'm seeing the problem on:
My firm has a regularly scheduled restoration of a database from backups of another, I won't go into the business reasons for this.
The database being restored is on an instance of SQL Server 2014 with SP2-CU1 updates which is running on a Windows Server 2012 R2 VM.
The drive on which the mdf/ndf/ldf files are, has 1.19TB free space, and the database occupies some 863GB. The .bak files themselves are held There are also some other database files on this drive leaving 244GB free, which I assume is enough for it to do a restore without any issue related to insufficient working space.
Over the weekend we do a restoration from a full backup, and every other day is from a differential. We sometimes see the restoration fail multiple times in a row, at present I've had some 8 or 9 consecutive failed attempts since Monday. This usually occurs when restoring from the full backup which leads me to believe that it's a performance issue with SQL Server when handling large databases, or it might be something funny with the VMs?
The error I am seeing when it fails is below. This can occur at seemingly any point during the restoration, for instance the last attempt it got up to 76% complete on restoring the full backup before keeling over.
Msg 3203, Level 16, State 1, Line 2 Read on "\\VM\Backups\TheDatabase.bak" failed: 1130(Not enough server storage is available to process this command.) Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. Msg 4319, Level 16, State 5, Line 3 A previous restore operation was interrupted and did not complete processing on file 'TheDatabase'. Either restore the backup set that was interrupted or restart the restore sequence. Msg 3119, Level 16, State 1, Line 3 Problems were identified while planning for the RESTORE statement. Previous messages provide details. Msg 3013, Level 16, State 1, Line 3 RESTORE DATABASE is terminating abnormally.
There is a project to do partitioning so we need only backup and restore the hot portion of the DB, which should hopefully resolve this, but I am looking for anything in the interim which could potentially reduce the occurences of this or stop this from happening entirely. Let me know if more info is needed to diagnose this.
Any help would be greatly appreciated!