mssql-docker
mssql-docker copied to clipboard
microsoft
insufficient system memory in resource pool to run this query
Running Docker in Fedora 26, and trying to restore a database from backup, I get the error:
SQL Error [701] [S00013]: There is insufficient system memory in resource pool 'default' to run this query.
Some more information:
The command that I'm using to run Docker:
# docker run --name mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=secret' \
-p 1433:1433 -v /home/igal/Documents/docker-mssql-fs/:/docker-mssql-fs \
--memory=16g -d microsoft/mssql-server-linux
SELECT @@VERSION:
Microsoft SQL Server 2017 (RC2) - 14.0.900.75 (X64)
Jul 27 2017 08:53:49
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.2 LTS)
master.sys.configurations WHERE name LIKE '%memory%'
configuration_id |name |value |value_in_use |description |is_dynamic |is_advanced |
-----------------|--------------------------|-----------|-------------|---------------------------------------|-----------|------------|
1505 |index create memory (KB) |0 |0 |Memory for index create sorts (kBytes) |1 |1 |
1544 |max server memory (MB) |2147483647 |2147483647 |Maximum size of server memory (MB) |1 |1 |
1540 |min memory per query (KB) |1024 |1024 |minimum memory per query (kBytes) |1 |1 |
1543 |min server memory (MB) |0 |16 |Minimum size of server memory (MB) |1 |1 |
RESTORE FILELISTONLY FROM DISK = '/docker-mssql-fs/mydatabase.bak'
LogicalName |PhysicalName |Type |FileGroupName |Size |MaxSize |FileId |CreateLSN |DropLSN |UniqueId |ReadOnlyLSN |ReadWrit
---------------|-----------------------------|-----|--------------|-----------|---------------|-------|----------|--------|-------------------------------------|------------|--------
mydatabase_dat |C:\SQLData\mydatabase.mdf |D |PRIMARY |5411700736 |35184372080640 |1 |0 |0 |00000000-0000-0000-0000-000000000000 |0 |0
mydatabase_log |C:\SQLData\mydatabase_1.ldf |L | |2011627520 |35184372080640 |2 |0 |0 |00000000-0000-0000-0000-000000000000 |0 |0
And the command that throws that error:
RESTORE DATABASE mydatabase
FROM DISK = '/docker-mssql-fs/mydatabase.bak'
WITH MOVE 'mydatabase_dat' TO '/docer-mssql-fs/mydatabase_dat',
MOVE 'mydatabase_log' TO '/docer-mssql-fs/mydatabase_log',
STATS = 1
How can I resolve that?
Thanks!
Looks like there is a typo in your RESTORE command: '/docer-mssql-fs/mydatabase_dat'
should be: '/docker-mssql-fs/mydatabase_dat'
Could that be it?
@twright-msft Good eye! I did, indeed, have a typo there, but after fixing it I still get the same error. I think that the process doesn't even get to the point where it tries to validate the path.
Any ideas on how to increase memory size etc. to the Docker MSSQL instance? I tried some DBCC commands but kept getting the same error no matter what I tried.
Thanks!
@rekahsoft Can you share some information about the database size and/or memory settings?
Sure @isapir. I am restoring a 6GB full backup. I was using AWS and used a couple different instances, with 8GB, 16GB and 32GB of RAM. I found that no matter what, mssql would only use 8GB, and crash once it reached the 8GB threshold. I used the following command to run the mssql docker image:
docker run -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=e3EZJSnd0fR39AH01AFCg06OP' -p 1433:1433 -v $(pwd)/ge-db:/tmp/db -d microsoft/mssql-server-linux
And then restored the backup using:
docker exec -it $(docker ps -aq | head -n1) /opt/mssql-tools/bin/sqlcmd -S localhost -U sa -P 'e3EZJSnd0fR39AH01AFCg06OP' -Q "RESTORE DATABASE pmps FROM DISK='/tmp/db/pmps.bak' WITH MOVE 'PM_Log' TO '/var/opt/mssql/data/PM_Log.ldf', MOVE 'PM_DB' TO '/var/opt/mssql/data/PM_DB.mdf'"
After a while, I receive the error: There is insufficient system memory in resource pool 'default' to run this query..
I also tried setting the maximum memory mssql setting to 32GB prior to attempting to restore the backup (based on https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/server-memory-server-configuration-options) like so:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 32768;
GO
RECONFIGURE;
GO
However, I still received the same error, and mssql does not use all RAM. As far as I'm aware, the express version of mssql for windows has a 8GB memory limit. Perhaps this is the case for the mssql docker image as well?
We're experiencing the same issue with the :latest, :2017-latest and :GA-latest images.
We tried to restore a 90Mb .bak file. The host is a Linux so the mssql-server-linux container has full access to the host ram space which is 24Gb.
We tried running from within the container while making sure all the files and path had the right user access levels and nothing changed on that end.
Same issue here. I wanted to restore a 10MB backup and got the same error There is insufficient system memory in resource pool 'default' to run this query. (Microsoft SQL Server, Error: 701). The Docker-Machine has 10GB memory overall.
Version: Microsoft SQL Server 2017 (RTM-CU1) (KB4038634) - 14.0.3006.16 (X64) Oct 19 2017 02:42:29 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
I wanted to restore a 10MB backup and got the same error There is insufficient system memory
Then I guess it's even worse than I thought. I'm surprised that this issue is not given any priority at all.
Then I guess it's even worse than I thought. I'm surprised that this issue is not given any priority at all.
Yea. I got an workaround for this. I installed an Windows Server 2016 VM with MSSQL Server 2016 and the MSSQL Server Manager, after that I attached the database, restored it with the backup and created a new backup, then I was able to use the new backup on the Docker MSSQL server.
Steps:
install mssql 2016 on windows
attach the database and restore it with the backup which does not work on the docker mssql server
create a new backup. Now you should be able to use the new backup on the docker mssql server
So a backup from MSSQL Server 2016 is restored in the Docker MSSQL Server but a backup by an older server does not? The backups I was testing were generated by MSSQL 2008.
I have the same problem with a backup coming from MSSQL 2008R2. Another backup coming from MSSQL 2008R2 is restoring just fine however.
@rvdginste Can you think of some of the differences between the two backups?
@isapir I tried to restore 8 databases (coming from MSSQL 2008R2), 5 succeeded and 3 failed. The only thing I can tell is that the biggest ones (mdf size) are failing. The smallest of those 3 is ~290MB, the biggest of those 5 is in size just below that.
Edit: And I can also confirm that after going through a restore and backup on an MSSQL 2016, those databases restore correctly on the docker image. Thus, workaround suggested by @jerptrs also works for me.
Have the same issue, haven't tried the VM option yet, but seems like the thread may have gone quiet?
And found one other interesting issue. The database created from the restore does exist, at least partially. But, if I try to access it (using DBVisualiser)
"Database 'Surety' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details."
And in the log it says:
(There is a second error message, Error: 928, Severity: 20, State: 1. not certain if that is related )
`2018-03-08 13:06:11.78 Server Microsoft SQL Server 2017 (RTM-CU4) (KB4056498) - 14.0.3022.28 (X64)
Feb 9 2018 19:39:09
Copyright (C) 2017 Microsoft Corporation
Developer Edition (64-bit) on Linux (Ubuntu 16.04.3 LTS)
2018-03-08 13:06:11.78 Server UTC adjustment: 0:00
2018-03-08 13:06:11.78 Server (c) Microsoft Corporation.
2018-03-08 13:06:11.78 Server All rights reserved.
2018-03-08 13:06:11.78 Server Server process ID is 4120.
2018-03-08 13:06:11.79 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2018-03-08 13:06:11.79 Server Registry startup parameters:
-d /var/opt/mssql/data/master.mdf
-l /var/opt/mssql/data/mastlog.ldf
-e /var/opt/mssql/log/errorlog
2018-03-08 13:06:11.79 Server SQL Server detected 4 sockets with 1 cores per socket and 1 logical processors per socket, 4 total logical processors; using 4 logical processors based on SQL Server li
censing. This is an informational message; no user action is required.
2018-03-08 13:06:11.79 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-03-08 13:06:11.80 Server Detected 3158 MB of RAM. This is an informational message; no user action is required.
2018-03-08 13:06:11.80 Server Using conventional memory in the memory manager.
2018-03-08 13:06:12.06 Server Buffer pool extension is already disabled. No action is necessary.
2018-03-08 13:06:12.31 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2018-03-08 13:06:12.31 Server Implied authentication manager initialization failed. Implied authentication will be disabled.
2018-03-08 13:06:12.32 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-A
ES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA
256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2018-03-08 13:06:12.37 Server The maximum number of dedicated administrator connections for this instance is '1'
2018-03-08 13:06:12.38 Server Node configuration: node 0: CPU mask: 0x000000000000000f:0 Active CPU mask: 0x000000000000000f:0. This message provides a description of the NUMA configuration for this
computer. This is an informational message only. No user action is required.
2018-03-08 13:06:12.42 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is r
equired.
2018-03-08 13:06:12.45 Server In-Memory OLTP initialized on lowend machine.
2018-03-08 13:06:12.54 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Onli
ne. This is an informational message only. No user action is required.
2018-03-08 13:06:12.58 spid5s Starting up database 'master'.
2018-03-08 13:06:12.57 Server Query Store settings initialized with enabled = 1,
2018-03-08 13:06:12.60 Server Software Usage Metrics is disabled.
2018-03-08 13:06:12.84 spid5s 12 transactions rolled forward in database 'master' (1:0). This is an informational message only. No user action is required.
2018-03-08 13:06:12.88 spid5s 0 transactions rolled back in database 'master' (1:0). This is an informational message only. No user action is required.
2018-03-08 13:06:12.89 spid5s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.
2018-03-08 13:06:13.11 spid5s Resource governor reconfiguration succeeded.
2018-03-08 13:06:13.12 spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2018-03-08 13:06:13.13 spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2018-03-08 13:06:13.23 spid5s SQL Trace ID 1 was started by login "sa".
2018-03-08 13:06:13.23 spid5s Server name is 'a02696c3db3f'. This is an informational message only. No user action is required.
2018-03-08 13:06:13.29 spid20s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2018-03-08 13:06:13.31 spid9s Starting up database 'mssqlsystemresource'.
2018-03-08 13:06:13.31 spid23s Starting up database 'SURETY_DEV'.
2018-03-08 13:06:13.32 spid20s Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action
is required.
2018-03-08 13:06:13.34 spid25s Starting up database 'SuretyDev'.
2018-03-08 13:06:13.34 spid24s Starting up database 'msdb'.
2018-03-08 13:06:13.34 spid26s Starting up database 'Surety'.
2018-03-08 13:06:13.36 spid9s The resource database build version is 14.00.3022. This is an informational message only. No user action is required.
2018-03-08 13:06:13.41 spid9s Starting up database 'model'.
2018-03-08 13:06:13.58 spid19s A self-generated certificate was successfully loaded for encryption.
2018-03-08 13:06:13.59 spid19s Server is listening on [ 'any'
2018-03-08 13:11:15.32 spid54 Using 'dbghelp.dll' version '4.0.5'`
I'm having the same issue while I'm trying to install the mssql-server-linux through docker-compose up.
database: image: microsoft/mssql-server-linux:2017-GA environment: - ACCEPT_EULA=Y - SA_PASSWORD=AceSqlDB123 - USER=sa ports: - "1433:1433"
when it try with docker-compose up.
database_1 | 2018-03-28 15:55:29.41 Server Setup step is copying system data file 'C:\templatedata\master.mdf' to '/var/opt/mssql/data/master.mdf'.
2018-03-28 15:55:29.52 Server Did not find an existing master data file /var/opt/mssql/data/master.mdf, copying the missing default master and other system database files. If you have moved the database location, but not moved the database files, startup may fail. To repair: shutdown SQL Server, move the master database to configured location, and restart.
2018-03-28 15:55:29.52 Server Setup step is copying system data file 'C:\templatedata\mastlog.ldf' to '/var/opt/mssql/data/mastlog.ldf'.
2018-03-28 15:55:29.53 Server Setup step is copying system data file 'C:\templatedata\model.mdf' to '/var/opt/mssql/data/model.mdf'.
2018-03-28 15:55:29.56 Server Setup step is copying system data file 'C:\templatedata\modellog.ldf' to '/var/opt/mssql/data/modellog.ldf'.
2018-03-28 15:55:29.59 Server Setup step is copying system data file 'C:\templatedata\msdbdata.mdf' to '/var/opt/mssql/data/msdbdata.mdf'.
2018-03-28 15:55:29.63 Server Setup step is copying system data file 'C:\templatedata\msdblog.ldf' to '/var/opt/mssql/data/msdblog.ldf'.
2018-03-28 15:55:29.74 Server Microsoft SQL Server 2017 (RTM-CU5) (KB4092643) - 14.0.3023.8 (X64)
databaseMar 2 2018 18:24:44
databaseCopyright (C) 2017 Microsoft Corporation
databaseDeveloper Edition (64-bit) on Linux (Ubuntu 16.04.4 LTS)
2018-03-28 15:55:29.74 Server UTC adjustment: 0:00
2018-03-28 15:55:29.74 Server (c) Microsoft Corporation.
2018-03-28 15:55:29.74 Server All rights reserved.
2018-03-28 15:55:29.74 Server Server process ID is 4124.
2018-03-28 15:55:29.74 Server Logging SQL Server messages in file '/var/opt/mssql/log/errorlog'.
2018-03-28 15:55:29.74 Server Registry startup parameters:
database -d /var/opt/mssql/data/master.mdf
database -l /var/opt/mssql/data/mastlog.ldf
database -e /var/opt/mssql/log/errorlog
2018-03-28 15:55:29.75 Server SQL Server detected 6 sockets with 1 cores per socket and 1 logical processors per socket, 6 total logical processors; using 6 logical processors based on SQL Server licensing. This is an informational message; no user action is required.
2018-03-28 15:55:29.75 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2018-03-28 15:55:29.75 Server Detected 9595 MB of RAM. This is an informational message; no user action is required.
2018-03-28 15:55:29.75 Server Using conventional memory in the memory manager.
2018-03-28 15:55:29.81 Server Large Page Allocated: 32MB
2018-03-28 15:55:30.25 Server Buffer pool extension is already disabled. No action is necessary.
2018-03-28 15:55:30.46 Server InitializeExternalUserGroupSid failed. Implied authentication will be disabled.
2018-03-28 15:55:30.47 Server Implied authentication manager initialization failed. Implied authentication will be disabled.
2018-03-28 15:55:30.48 Server Successfully initialized the TLS configuration. Allowed TLS protocol versions are ['1.0 1.1 1.2']. Allowed TLS ciphers are ['ECDHE-ECDSA-AES128-GCM-SHA256:ECDHE-ECDSA-AES256-GCM-SHA384:ECDHE-RSA-AES128-GCM-SHA256:ECDHE-RSA-AES256-GCM-SHA384:ECDHE-ECDSA-AES128-SHA256:ECDHE-ECDSA-AES256-SHA384:ECDHE-ECDSA-AES256-SHA:ECDHE-ECDSA-AES128-SHA:AES256-GCM-SHA384:AES128-GCM-SHA256:AES256-SHA256:AES128-SHA256:AES256-SHA:AES128-SHA:!DHE-RSA-AES256-GCM-SHA384:!DHE-RSA-AES128-GCM-SHA256:!DHE-RSA-AES256-SHA:!DHE-RSA-AES128-SHA'].
2018-03-28 15:55:30.57 Server Node configuration: node 0: CPU mask: 0x000000000000003f:0 Active CPU mask: 0x000000000000003f:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2018-03-28 15:55:30.57 Server The maximum number of dedicated administrator connections for this instance is '1'
2018-03-28 15:55:30.67 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2018-03-28 15:55:30.70 Server In-Memory OLTP initialized on standard machine.
2018-03-28 15:55:30.80 Server Database Instant File Initialization: enabled. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.
2018-03-28 15:55:30.83 Server Query Store settings initialized with enabled = 1,
2018-03-28 15:55:30.84 spid5s Starting up database 'master'.
2018-03-28 15:55:30.87 Server Software Usage Metrics is disabled.
2018-03-28 15:55:31.16 spid5s The tail of the log for database master is being rewritten to match the new sector size of 4096 bytes. 3072 bytes at offset 418816 in file /var/opt/mssql/data/mastlog.ldf will be written.
2018-03-28 15:55:31.47 spid5s Converting database 'master' from version 862 to the current version 869.
2018-03-28 15:55:31.48 spid5s Database 'master' running the upgrade step from version 862 to version 863.
2018-03-28 15:55:31.50 spid5s Database 'master' running the upgrade step from version 863 to version 864.
2018-03-28 15:55:31.54 spid5s Database 'master' running the upgrade step from version 864 to version 865.
2018-03-28 15:55:31.55 spid5s Database 'master' running the upgrade step from version 865 to version 866.
2018-03-28 15:55:31.57 spid5s Database 'master' running the upgrade step from version 866 to version 867.
2018-03-28 15:55:31.59 spid5s Database 'master' running the upgrade step from version 867 to version 868.
2018-03-28 15:55:31.61 spid5s Database 'master' running the upgrade step from version 868 to version 869.
2018-03-28 15:55:31.88 spid5s Resource governor reconfiguration succeeded.
2018-03-28 15:55:31.90 spid5s SQL Server Audit is starting the audits. This is an informational message. No user action is required.
2018-03-28 15:55:31.92 spid5s SQL Server Audit has started the audits. This is an informational message. No user action is required.
2018-03-28 15:55:32.05 spid5s SQL Trace ID 1 was started by login "sa".
2018-03-28 15:55:32.07 spid5s Server name is '02a6db196fd6'. This is an informational message only. No user action is required.
2018-03-28 15:55:32.08 spid18s Password policy update was successful.
2018-03-28 15:55:32.18 spid20s Always On: The availability replica manager is starting. This is an informational message only. No user action is required.
2018-03-28 15:55:32.19 spid5s Starting up database 'msdb'.
2018-03-28 15:55:32.19 spid9s Starting up database 'mssqlsystemresource'.
2018-03-28 15:55:32.20 spid20s Always On: The availability replica manager is waiting for the instance of SQL Server to allow client connections. This is an informational message only. No user action is required.
2018-03-28 15:55:32.24 spid9s The resource database build version is 14.00.3023. This is an informational message only. No user action is required.
2018-03-28 15:55:32.30 spid9s Starting up database 'model'.
2018-03-28 15:55:32.57 spid18s A self-generated certificate was successfully loaded for encryption.
2018-03-28 15:55:32.58 spid18s Server is listening on [ 'any' <ipv4> 1433].
2018-03-28 15:55:32.58 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].
2018-03-28 15:55:32.58 Server Dedicated admin connection support was established for listening locally on port 1434.
2018-03-28 15:55:32.60 spid5s The tail of the log for database msdb is being rewritten to match the new sector size of 4096 bytes. 512 bytes at offset 52736 in file /var/opt/mssql/data/MSDBLog.ldf will be written.
2018-03-28 15:55:32.61 spid18s SQL Server is now ready for client connections. This is an informational message; no user action is required.
2018-03-28 15:55:32.64 spid5s Converting database 'msdb' from version 862 to the current version 869.
2018-03-28 15:55:32.64 spid5s Database 'msdb' running the upgrade step from version 862 to version 863.
2018-03-28 15:55:32.67 spid5s Database 'msdb' running the upgrade step from version 863 to version 864.
2018-03-28 15:55:32.69 spid5s Database 'msdb' running the upgrade step from version 864 to version 865.
2018-03-28 15:55:32.70 spid5s Database 'msdb' running the upgrade step from version 865 to version 866.
2018-03-28 15:55:32.71 spid5s Database 'msdb' running the upgrade step from version 866 to version 867.
2018-03-28 15:55:32.72 spid9s The tail of the log for database model is being rewritten to match the new sector size of 4096 bytes. 2048 bytes at offset 75776 in file /var/opt/mssql/data/modellog.ldf will be written.
2018-03-28 15:55:32.72 spid5s Database 'msdb' running the upgrade step from version 867 to version 868.
2018-03-28 15:55:32.73 spid5s Database 'msdb' running the upgrade step from version 868 to version 869.
2018-03-28 15:55:32.74 spid9s Converting database 'model' from version 862 to the current version 869.
2018-03-28 15:55:32.74 spid9s Database 'model' running the upgrade step from version 862 to version 863.
2018-03-28 15:55:32.76 spid9s Database 'model' running the upgrade step from version 863 to version 864.
2018-03-28 15:55:32.78 spid9s Database 'model' running the upgrade step from version 864 to version 865.
2018-03-28 15:55:32.78 spid9s Database 'model' running the upgrade step from version 865 to version 866.
2018-03-28 15:55:32.79 spid9s Database 'model' running the upgrade step from version 866 to version 867.
2018-03-28 15:55:32.80 spid9s Database 'model' running the upgrade step from version 867 to version 868.
2018-03-28 15:55:32.81 spid9s Database 'model' running the upgrade step from version 868 to version 869.
2018-03-28 15:55:32.92 spid9s Polybase feature disabled.
2018-03-28 15:55:32.92 spid9s Clearing tempdb database.
2018-03-28 15:55:33.40 spid9s Starting up database 'tempdb'.
2018-03-28 15:55:33.71 spid9s The tempdb database has 1 data file(s).
2018-03-28 15:55:33.71 spid24s The Service Broker endpoint is in disabled or stopped state.
2018-03-28 15:55:33.71 spid24s The Database Mirroring endpoint is in disabled or stopped state.
2018-03-28 15:55:33.73 spid24s Service Broker manager has started.
2018-03-28 15:55:33.84 spid5s Recovery is complete. This is an informational message only. No user action is required.
2018-03-28 15:55:33.85 spid20s The default language (LCID 0) has been set for engine and full-text services.
2018-03-28 15:55:34.41 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.
2018-03-28 15:55:34.46 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
2018-03-28 15:55:34.48 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.
2018-03-28 15:55:34.68 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.
2018-03-28 15:55:34.70 spid51 Using 'xpsqlbot.dll' version '2017.140.3023' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.
2018-03-28 15:55:34.79 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.
2018-03-28 15:55:34.83 spid51 Using 'xpstar.dll' version '2017.140.3023' to execute extended stored procedure 'xp_sqlagent_notify'. This is an informational message only; no user action is required.
2018-03-28 15:55:37.60 spid54 Using 'dbghelp.dll' version '4.0.5'`
It just stops there and doesn't do anything. I'm hoping to get a solution. I see this thread isn't active over past month. Any help would be appreciated.
@siddthota - it looks like your container came up fine. That's just the end of the initial output. Have you tried connecting to it? I dont think it is the same issue that was originally raised by @isapir with restoring database backups. Or is there something about your situation that you didnt explain which ties it to the original issue?
I received this error as well.. The database itself has been up and working.. I ran it on my system (Win 10 Pro) and left it up for a couple days without issue.. Then I simply tried to query a table (which turned out to be empty) and received this error.
docker stop and docker start appears to have resolved the error for now.
It looks like this is fixed in SQL Server 2019, using the mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu image I am able to restore a bak file that previously gave me the insufficient system memory in resource pool error
It looks like this is fixed in SQL Server 2019, using the
mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntuimage I am able to restore a bak file that previously gave me theinsufficient system memory in resource poolerror
same image also resolved the issue for me.
I gave a try with mcr.microsoft.com/mssql/server:vNext-CTP2.0-ubuntu but still got the same issue.
Is there any other options which I might look into or any debug-info which I could share?
// Edit#1 It was happening always on 2 out of 10 .sql files which I am importing on a regularly base. Now it fails only on 1 out of 10 files. I have sliced the last file in order to avoid this.
I was getting this same error on Docker for Mac. I went into the docker preferences, increased the memory allocated to the VM, then restarted docker and started back up the container. This allowed me to import a larger sql file without the insufficient system memory error.
I was getting this same error on Docker for Mac. I went into the docker preferences, increased the memory allocated to the VM, then restarted docker and started back up the container. This allowed me to import a larger sql file without the insufficient system memory error.
Works for me!
I'm seeing the same error when running certain queries. However when I watch the memory usage on the container, it never even gets close to maximum.
I'm using the following image mcr.microsoft.com/mssql/rhel/server:2019-latest
I'm seeing the same error when running certain queries. However when I watch the memory usage on the container, it never even gets close to maximum.
I'm using the following image
mcr.microsoft.com/mssql/rhel/server:2019-latest
Try increase memory on docker preferences, maybe 6GB
I have increased the memory of the container to 6GB. It appears to resolve the issue. I suspect the container and SQL Server are not communicating with each other correctly about available memory.
Running Docker in Fedora 26, and trying to restore a database from backup, I get the error:
SQL Error [701] [S00013]: There is insufficient system memory in resource pool 'default' to run this query.
Some more information:
The command that I'm using to run Docker:
# docker run --name mssql -e 'ACCEPT_EULA=Y' -e 'SA_PASSWORD=secret' \ -p 1433:1433 -v /home/igal/Documents/docker-mssql-fs/:/docker-mssql-fs \ --memory=16g -d microsoft/mssql-server-linux
This error happens in some situation where the configured max memory parameter is over the operational system RAM or over SQL Edition limitation.
You can see each SQL 2017 Edition limitations here: https://docs.microsoft.com/en-us/sql/sql-server/editions-and-components-of-sql-server-2017?view=sql-server-ver15
For example, if you are runnig the SQL 2017 Express Edition in a VM with 4GB RAM, you must limit SQL max memory param with less than 1410MB (1.4GB), because the 2017 Express edition crashs if you exceed these memory limit with error 701. I recommend 1300MB in this scenario.
Another example: If you are running the 2017 Web edition, that supports max memory 64GB, in a VM with 8GB RAM, I recommend you limit the SQL Max memory param in 6GB, leaving 2GB to OS (Ubuntu). If you don't limit it, you can get the same error 701.
To set this max server memory parameter, you can use this script:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'max server memory', 1300;
GO
RECONFIGURE;
GO
You also can check how to pass this parameter to Docker run instruction to prevent the crash on container load process.
Try -e MSSQL_MEMORYLIMIT_MB=1300 in the Docker run command
I had a similar issue while running mysql in docker. Few things to take into consideration:
1. Increase the memory allocation of docker.
You can do it easily using docker desktop dashboard.
2. Run the docker-compose with an admin access.
For an example: sudo docker-compose -f mysql.docker-compose.yml up -d
Point 2 above will resolve inaccessible file issue.
The above I have done in my MacBook pro 11.5.1
