Share via

System memory error while deploying dacpac to SQL Server 2019 in Windows OS

Praveenraj R K 61 Reputation points Microsoft Employee
2022-11-02T11:42:11.86+00:00

Hi Team,

I am getting below error messaging while running dacpac file in SQL server 2019 in Windows 11 (16 GB RAM) But same dacpac is working when we deploy in Windows server 2016 (Standard D2s v3 (2 vcpus, 8 GiB memory) ).

Error SQL72014: .Net SqlClient Data Provider: Msg 701, Level 17, State 65, Line 150571 There is insufficient system memory in resource pool 'default' to run this query.
Error Deploy72002: Exception of type 'System.OutOfMemoryException' was thrown.
(Microsoft.SqlServer.Dac)

Note : Dacpac file has post deployment scripts of size approx 100 MB.

Please advise on the fix.

Azure SQL Database
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,364 questions
0 comments

3 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Alberto Morillo 33,426 Reputation points MVP
    2022-11-02T12:49:52.917+00:00

    Could you try to apply the latest cumulative update for SQL Server 2019 on the Windows 11 host? Please download and apply CU18 from here.

    Please update SQLPackage also here.

    Please try to update the .net sqlclient data driver also from here.

    1 person found this answer helpful.
  2. Bjoern Peters 8,856 Reputation points
    2022-11-02T12:56:20.467+00:00

    Hi @Praveenraj R K

    First, windows 11 is a desktop operating system, and Windows Server 2016 is a server operating system; there are differences in how the handles system resources and requests from applications.

    Second, your Windows 11 typically runs many other applications parallel to your SQL Server, so there might be a difference in available memory.

    How about the configuration of your SQL Server? Which value is set for "max Memory" in the configuration?
    Is it still the default value of ~2PB? Then your SQL Server requests up to 2PB from OS, which shortens the available memory in OS, which can lead to "out of memory".

    So you have to configure your SQL Server correctly in order to run such "restores" and get rid of error messages.

    If SQL Server is running out of memory and has no more memory available to allocate to its transactions then it will generate SQL Server memory error 701 or error 802. 

    sp_configure 'show advanced options', 1;    
GO    
RECONFIGURE;    
GO   
sp_configure 'max server memory', 8192;   
GO    
RECONFIGURE;    
GO

    Change the value to something that fits your environment.

    1 person found this answer helpful.
  3. Erland Sommarskog 107.2K Reputation points
    2022-11-02T22:18:01.843+00:00

    To sort things some things out: this an error from SQL Server, so the SqlPackage version has nothing to do with it.

    You say Azure SQL Server 2019. But then you say that it runs on Windows 11. I take it that there is connection to Azure SQL Database here?

    I think you need to set up an extended-events session to capture exceptions, so that you can find out which statement that bombs. You need to know this for further troubleshooting.

    For how to set up such a session and how to view it, see this article on my web site: https://www.sommarskog.se/Short%20Stories/trace-exceptions.html.