December 31, 2020 at 7:31 pm
Not a DBA here. Now that that is out of the way... I am troubleshooting an issue where our SQL service is stopping. We are on SQL 2012 Express x64 11.0.2100.60 running on Windows Server 2008 R2 Standard x64. The Windows application event logs show:
-Event ID 701: There is insufficient system memory in resource pool 'internal' to run this query. (many of these)
-Event ID 18056: Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. (many of these)
-Event ID 17188: SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT:192.168.0.10]
In the Windows System logs is shows 1 second after event id 17188 occurs:
- The SQL Server (CROSSTALK) service entered the stopped state.
The server had been running for several years without any issues. No Windows or SQL updates had been installed since the system was setup several years ago, so that does not seem to be a variable. I did see recommendations to update to the latest SP, so I am aware of that. Perhaps that will solve the issue?
The server was only running 4GB of memory, and 1GB was reserved for SQL. I did bump the vm up from 4GB to 10GB, and bumped SQL up to 4GB. So currently it show Mimumum Server Memory = 0, Maximum = 4096. Index creation memory=0, Minimum memory per query=1024. After adding the memory, I still get the Windows event ID 701 and 18056 unfortunately. I have confirmed the SQL Process is consuming all 4GB in the Task Manager. Although I am slightly confused as it has been stated that SQL Express only supports 1GB.
The service has been crashing about once a week. It has only been 2 days since I upgraded the memory, so I have no idea if it is going to crash again or not. And of course I set the service to restart automatically before the last crash and it didn't start as it should have.
I did run the dbcc memorystatus command but have no idea what would be considered a problem. I have attached the report. If anyone can take a peek and let me know if you see a problem or have any recommendations, it would be greatly appreciated. Thank you!
January 1, 2021 at 8:10 pm
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
January 5, 2021 at 4:32 pm
SQL Server 2012 Express Edition only uses up to 1GB of memory. So if you need more than that to perform your work, you'll either need to see if you can tune your code to use less memory or move to Standard Edition.
January 10, 2021 at 7:55 am
The limit of 1gb is for a singe SQL instance. You can create extra instance and that will use 1gb also.
The restriction of memory is for buffer cache. SQL Server has many caches, even when not counting the plan cache, there are plenty of other caches within SQL Server. Because only the buffer cache has the strict 1gb limitation, you can actually see SQL Server Express memory uses more.
Additional information:
Not a DBA here. Now that that is out of the way... I am troubleshooting an issue where our SQL service is stopping. We are on SQL 2012 Express x64 11.0.2100.60 running on Windows Server 2008 R2 Standard x64. The Windows application event logs show:
--> First backup your databases.
--> I would upgrade the windows server to Windows 2012 R2 latest Service pack.
--> Uninstall/Reinstall the SQL server 2012 express.
-Event ID 701: There is insufficient system memory in resource pool 'internal' to run this query. (many of these)
--> Please adjust the query to access smaller row count. (Top 10, I would experiment.)
--> Please send us the query you are running.
-Event ID 18056: Error: 18056, Severity: 20, State: 29. (Params:). The error is printed in terse mode because there was error during formatting. Tracing, ETW, notifications etc are skipped. (many of these)
-Event ID 17188: SQL Server cannot accept new connections, because it is shutting down. The connection has been closed. [CLIENT:192.168.0.10]
In the Windows System logs is shows 1 second after event id 17188 occurs:
- The SQL Server (CROSSTALK) service entered the stopped state.
The server was only running 4GB of memory, and 1GB was reserved for SQL. I did bump the vm up from 4GB to 10GB, and bumped SQL up to 4GB. So currently it show Minimum Server Memory =**** 2048 *** - pickup some speed for the virtual, Maximum = 4096. Index creation memory=0, Minimum memory per query=1024. After adding the memory, I still get the Windows event ID 701 and 18056 unfortunately. I have confirmed the SQL Process is consuming all 4GB in the Task Manager. Although I am slightly confused as it has been stated that SQL Express only supports 1GB.
The service has been crashing about once a week. It has only been 2 days since I upgraded the memory, so I have no idea if it is going to crash again or not. And of course I set the service to restart automatically before the last crash and it didn't start as it should have.
--> An option would be to repair the SQL server current install.
--> Apply the latest service packs.
I did run the dbcc memorystatus command but have no idea what would be considered a problem. I have attached the report. If anyone can take a peek and let me know if you see a problem or have any recommendations, it would be greatly appreciated. Thank you!
--> DBCC memorystatus - For example:
Memory Manager
The first section of the output is Memory Manager. This section shows overall memory consumption by SQL Server.
ConsoleCopy
Memory Manager KB
------------------------------ --------------------
VM Reserved 1761400
VM Committed 1 663556
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
--> I would suggest to perform the above tasks first before we get into your memory usage issues with the DBCC command.
--> If you still have issues, please provide the DBCC command output for our review. I did not see it.
Cheers
DBASupport
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply