Not Enough Server Storage to Process Command – SQL Server Restore Error

restoresql serversql server 2014windows-server

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!

Best Answer

The error message is misleading you, it's not talking about disk storage on the machine where you are restoring.

The windows error message states:

1130(Not enough server storage is available to process this command.)

When you check that error message you will notice that it's not an error during the restore operation but rather during the file copy operation over the network as documented here

The two causes of this problem are related. The more frequent cause is listed first: More files are open than the memory cache manager can handle. As a result, the cache manager has exhausted the available paged pool memory. The backup program has tried to back up a file whose size is larger than the backup API can access on that version of the operating system. This has the same result (that is, the paged pool is exhausted). Note This second issue is more likely to occur on a Microsoft Windows NT 4.0-based computer.

The resolution for each problem differs depending on whether you experience the problem in Windows Server 2003, in Microsoft Windows 2000, or in Windows NT 4.0.

Or here

When you access shared items on a Windows NT server from a Windows NT client, you may receive the following error message: Not enough server storage is available to process this command.

So as you say, you likely have enough storage to restore the database but you are running into issues retrieving the file over the network. In any case you need to be looking at some SMB issue

Had you been running into disk space problems during the restore the error message would have been:

112(There is not enough space on the disk.)

As @Nic correctly pointed out in the comments you should probably try to copy the backup file over using an unbuffered file copy (use ESEUTIL or XCOPY) and restore from there to either solve or at least isolate the issue.

If you still run into issues copying over the file that way I'm not sure that still fits the scope of dba.se.

Related Question