none
restore AdventureWorks2016CTP3 fails on SQL 2016 Developer RRS feed

  • Question

  • Hi, all.

    I have tried to restore sample database AdventureWorks2016CTP3 downloaded from https://www.microsoft.com/en-us/download/details.aspx?id=49502 and failed. Looking for solution I found that some people in the net succeeded while some suffered the same trouble, but nobody suggested an answer. Circumstances of my failure seem strange to me, so I ask an advice.

    In my test environment I have an Intel server with two Pentium D 3GHz cores, 2Gb RAM, with Windows Server 2016 Standard Evaluation Edition, Microsoft SQL Server 2016 Developer Edition. The server is tested under some lite to medium workload with large corporative database replica and works fine. No memory overflow conditions were checked.

    When I tried with the sample database for 2016 SQL Server, WideWorldImporters and AdventureWorksDW2016CTP3 backups restored fine. But restoring AdventureWorks2016CTP3 ended in error and the DB hanged with "Suspect" condition. Task Manager shows two very unusual memory bursts (see snapshot 1) while restoring the database through SQL Management Studio’s snap-in. When doing the same operation through a SQL script only one such burst occurs, but the result remains the same (snapshot 2). No memory bursts were seen when restoring any other database including similar in size and structure AdventureWorks2014 (snapshot 3) and aforementioned corporative DB about 300Gb in size. While normal restoration process memory graph is plain like lake surface in dead calm weather.

    screenshot 1 https://cloud.mail.ru/public/KSQf/mpKhYAr6Q

    screenshot 2 https://cloud.mail.ru/public/9iv5/fhBVULUuZ

    screenshot 3 https://cloud.mail.ru/public/6pjz/z71wxcZwL

    By this pictures the memory burst seemingly begins about 10 seconds after restoring starts and continues another 10 seconds. Still SQL Server log shows problems start about couple of seconds after.

    Here is just a few log events, since all the incident takes about 150 lines of log (normal restore process brings just half a dozen of events):

    <small>08/03/2017 15:34:34,spid57,Unknown,Starting up database 'AdventureWorks2016CTP3'.
    08/03/2017 15:34:35,spid57,Unknown,[INFO] Database ID: [11]. Deleting unrecoverable checkpoint table row (id: 7).
    08/03/2017 15:34:35,spid57,Unknown,[INFO] HkCkptLoadInternalEx(): Database ID: [11]. Root file: {48B86342-531A-41C6-B018-E7A5E7B3CA6C}<c> watermark: 168<c> RecoveryLsn: 00000044:000000F8:0002<c> RecoveryCheckpointId: 9<c> RecoveryCheckpointTimestamp: 0x9b
    08/03/2017 15:34:35,spid57,Unknown,[INFO] HkHostDbCtxt::Initialize(): Database ID: [11] 'AdventureWorks2016CTP3'. XTP Engine version is 2.9.
    08/03/2017 15:34:35,spid57,Unknown,The database 'AdventureWorks2016CTP3' is marked RESTORING and is in a state that does not allow recovery to be run.
    08/03/2017 15:34:37,spid47s,Unknown,[INFO] Database ID: [11]. Deleting unrecoverable checkpoint table row (id: 7).
    08/03/2017 15:34:37,spid47s,Unknown,[INFO] HkCkptLoadInternalEx(): Database ID: [11]. Root file: {48B86342-531A-41C6-B018-E7A5E7B3CA6C}<c> watermark: 168<c> RecoveryLsn: 00000044:000000F8:0002<c> RecoveryCheckpointId: 9<c> RecoveryCheckpointTimestamp: 0x9b
    08/03/2017 15:34:37,spid57,Unknown,[INFO] HkHostDbCtxt::Initialize(): Database ID: [11] 'AdventureWorks2016CTP3'. XTP Engine version is 2.9.
    08/03/2017 15:34:37,spid57,Unknown,[INFO] HkHostDbCtxt::Initialize(): Database ID: [11] 'AdventureWorks2016CTP3'. XTP Engine version is 2.9.
    08/03/2017 15:34:37,spid57,Unknown,Starting up database 'AdventureWorks2016CTP3'.
    08/03/2017 15:34:47,spid40s,Unknown,Disallowing page allocations for database 'AdventureWorks2016CTP3' due to insufficient memory in the resource pool 'default'. See 'http://go.microsoft.com/fwlink/?LinkId=510837' for more information.
    08/03/2017 15:35:07,spid47s,Unknown,XTP failed page allocation due to memory pressure: FAIL_PAGE_ALLOCATION 8</c></c></c></c></c></c></c></c></small>
    

    So my question is: what it is all about and how can I get working AdventureWorks2016CTP3, which is brought in intentionally to learn SQL Server 2016 new features and capabilities?

    Thanks.


    • Edited by aalub Friday, August 4, 2017 6:09 AM
    Thursday, August 3, 2017 4:48 PM

Answers

  • Hi aalub,

    I have tested it, when restoring it, the memory increases from 4.9 to 5.9GB, it increases about 1GB when restoring it.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aalub Saturday, August 5, 2017 10:13 AM
    Friday, August 4, 2017 7:51 AM

All replies

  • Hi aalub,


    Based on the error message, 'insufficient memory in the resource pool 'default'' has caused this problem. According to your description, you have installed it on a machine with 2Gb RAM, I have tested it on a 16Gb Ram machine and did not meet a similar problem, you can try to add some memory.

     

    Besides, AdventureWorks2016CTP3 is a Community Technology Preview edition and it has been replaced with WideWorldImporters, you can test SQL Server 2016 with it.

     

    Best Regards,

    Teige

     


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 4, 2017 2:32 AM
  • Thank you, Teige.

    You seem right, but I can’t afford another bit Gb of RAM for testing, sorry.
    Besides, I thought the process of restoration of a database differs from querying a database: the former is mostly disk space consuming, and not RAM space or CPU time consuming process, as the latter is. I’ve never seen a database restore of any size and complexity increases RAM used by more than 5%. In case of AdventureWorks2016CTP3 this increase reaches 100% and fails of lack for more.

    By the way, Teige! Can you kindly screenshot memory usage during the process of AdventureWorks2016CTP3 restoration on your 16 Gb server? It’s a matter of some five minutes to complete! I’ll be very thankful and we’ll determine the amount of RAM required.

    Regards,
      Andrei

    Friday, August 4, 2017 7:20 AM
  • Hi aalub,

    The test on the 16Gb machine is operated about three months ago, now I only have a 8Gb machine, I will test it again on this machine and share the result and screenshot with you.

    Best Regards,

    Teige


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, August 4, 2017 7:36 AM
  • Hi aalub,

    I have tested it, when restoring it, the memory increases from 4.9 to 5.9GB, it increases about 1GB when restoring it.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by aalub Saturday, August 5, 2017 10:13 AM
    Friday, August 4, 2017 7:51 AM
  • Besides, I thought the process of restoration of a database differs from querying a database: the former is mostly disk space consuming, and not RAM space or CPU time consuming process, as the latter is. I?ve never seen a database restore of any size and complexity increases RAM used by more than 5%. In case of AdventureWorks2016CTP3 this increase reaches 100% and fails of lack for more.

    I would guess that the difference in this case is the in-memory OLTP things.

    Friday, August 4, 2017 10:05 AM
  • I have tested it, when restoring it, the memory increases from 4.9 to 5.9GB, it increases about 1GB when restoring it.Hi

    Hi, Teige!

    Thank you very much. I'll try to increase my test server memory by 2Gb.

    Regards.

    Saturday, August 5, 2017 10:13 AM
  • I would guess that the difference in this case is t

    Agree. But why no sign of such memory burst restoring AdventureWorks2014 then? It is an OLTP database sample too.


    • Edited by aalub Saturday, August 5, 2017 10:27 AM exessive citation
    Saturday, August 5, 2017 10:24 AM