| Hello DBA's I just experienced a significant occurrence involving a database restoration failure. I chose to restore a database in order to test DR (Backup-Restore). As a Scary DBA, we should perform random database restore testing on our Test Server before any disaster strikes our actual Production Server. So that we can be certain that our backup is both valid and restorable. My "Test" Database has a memory-optimized table and a 575 GB database size. How to debug a memory optimized table database is a relatively new issue for me. Beginning with SQL Server 2014, a memory optimized table is essentially a table with two copies, one active and one durable on disk, whether that includes data or only structure. Because the memory has been wiped clean Because memory is flushed when SQL Services is restarted, SQL Server preserves a physical copy of the table that is recoverable.
Issue:--Restoration of "XYZ" database failed on our Test Server with the following error:--
Msg 41379, Level 16, State 0, Line 0
Restore operation failed for database 'XYZ' due to insufficient memory in the resource pool 'default'. Close other applications to increase the available memory, ensure that both SQL Server memory configuration and resource pool memory limits are correct and try again. See 'http://go.microsoft.com/fwlink/?LinkID=507574' for more information.
Msg 3456, Level 16, State 1, Line 2
Could not redo log record (310751:4331:4), for transaction ID (2:-741982062), on page (1:229325), allocation unit 72057637921488896, database 'XYZ' (database ID 7). Page: LSN = (310735:185062:258), allocation unit = 72057637921488896, type = 1. Log: OpCode = 2, context 2, PrevPageLSN: (310749:76109:7). Restore from a backup of the database, or repair the database.
Location: "e:\\b\\s3\\sources\\sql\\ntdbms\\hekaton\\engine\\core\\database.cpp":2663 Expression: db->TransactionMap->ComputeCount() == 0
SPID: 51
Process ID: 7392
Restore was successful but deferred transactions remain. These transactions cannot be resolved because there are data that is unavailable. Either use RESTORE to make that data available or drop the filegroups if you never need this data again. Dropping the filegroup results in a defunct filegroup.
RESTORE WITH CONTINUE_AFTER_ERROR was successful but some damage was encountered. Inconsistencies in the database are possible.
RESTORE DATABASE successfully processed 15513188 pages in 1867.005 seconds (64.915 MB/sec). Msg 3313, Level 16, State 1, Line 2 During redoing of a logged operation in database 'XYZ', an error occurred at log record ID (310751:4331:4). Typically, the specific failure is previously logged as an error in the Windows Event Log service. Restore the database from a full backup, or repair the database. I firmly believe that this is a fake error message indicating that the database should be restored from a full backup or repaired, and that some harm has occurred. So, I executed the restore verifyonly command, and it reported that my backup was valid/good. Without a doubt.....
Let's do more troubleshoot.....while restoring 
- Error Log suggested that restoration failed because of insufficient memory to SQL Server
- From metadata information of backup file received by "Restore HeaderOnly" have shown in memory file stream table.
- Memory allocated to SQL Server got fully utilized before restoration was failed.
- On Production checked the memory usage of "memory optimize tables" feature, which came to 70 GB
- Test Server OS memory was 60 GB
Let's find out memory usage table, basically i will look for very interesting thing which is Memory Optimized Table using GUI... 
Even Not much clear.. for me. Let's fo a further investigate ....usnig query 
Resolution: I provided 60 GB+40 GB =100 GB memory to SQL Server and the database was properly restored, but I requested an additional 5 GB totaling 105 GB from SySOps. If the server on which I restore a database does not have enough accessible memory for the memory-optimized tables in the backup, the database will not come online and will be tagged as Suspect mode/Recovery Pending. After all of this effort, I was able to restore the "Test" database on our DBA QA/Test Server.
What are your thoughts? Please leave your thoughts!!! In the following essay, I will discuss how I addressed TB over Database in Recovery Pending State....Yes, I had this terrible scenario on my production box and I spent over 11 hours on it, as you may have guessed. Cheers, Zahid (Sagar) |
