August 14, 2019 at 4:13 pm
We have mounted a SQL Server test site using Hyper-V (restored/created from a Veam backup)
We last did this several months ago to test before a SQL 2017 upgrade ... worked fine at that time.
Trying to do this on a fresh Veam restore now blows up with
"SQL Server was unable to run a new system task, either because there is insufficient memory or the number of configured sessions exceeds the maximum allowed in the server. Verify that the server has adequate memory. Use sp_configure with option 'user connections' to check the maximum number of user connections allowed. Use sys.dm_exec_sessions to check the current number of sessions, including user processes."
The only change that we have made on Production (that I can find in our Change Control DOCs ...) was to restrict the RAM (instead of using unlimited setting). Production was set to 24GB ... but VM Test only has 8GB available ... so assuming that was the problem I set VM TEST SQL Startup parameter to single user, Restarted the Service and made that sp_config change, and changed Startup back to "normal" and restarted service again. Same problem.
I thought the "number of configured sessions" must be a Red Herring because Production was working fine ... but I changed that (from 8) to 0 anyway ... and Bingo! everything then worked.
So I scratched by head as to how Production does not have the same problem. On closer inspection the CONFIG setting for Max users on Production is 8, but the RUNNING value is 0
EXEC sys.sp_configure
name minimum maximum config_value run_value
---------------------- ------- ---------- ------------ ---------
max server memory (MB) 128 2147483647 24000 24000
user connections 0 32767 8 0
So: "how did that happen"?
So now I am assuming that the sp_config change was made to Production but RECONFIGURE has not been used (and Production not rebooted since)
SELECT sqlserver_start_time FROM sys.dm_os_sys_info
sqlserver_start_time
-----------------------
2019-04-01 19:36:20.017
I used following script to see when sp_config was last run:
DBCC TRACEON (3604);
DBCC CONFIG;
GO
that gave me cfgupddate = 43554, cfgupdtime = 19931300, and I calculate that as:
SELECT
DATEADD(Millisecond
, 19931300 * (3.0 + 1.0/3)-- cfgupdtime
, DATEADD(Day, 43554, '19000101')-- cfgupddate
)
which (if I have done that right?) gives
2019-04-01 18:27:17.660
But this is BEFORE the last reboot ?? if so why would Reboot NOT have taken the CONFIG value for Max Users?
I don't have SQL Error Log going back that far (nor Event Log), but by chance there is an old SQL Agent Error Log:
2019-04-01 18:27:12 - ? [000] Event Global\sqlserverRecComplete opened
2019-04-01 18:27:12 - ? [100] Microsoft SQLServerAgent version 14.0.3076.1 (X64 unicode retail build) : Process ID 4752
2019-04-01 18:27:12 - ? [495] The SQL Server Agent startup service account is MyDomain\SQL-Service.
2019-04-01 18:27:13 - ? [393] Waiting for SQL Server to recover database 'msdb'...
2019-04-01 18:27:17 - ? [000] Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
2019-04-01 18:27:18 - ? [101] SQL Server MyServer-SQL version 14.00.3076 (0 connection limit)
2019-04-01 18:27:18 - ? [102] SQL Server ODBC driver version 14.00.3076
2019-04-01 18:27:18 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is MyServer-SQL
2019-04-01 18:27:18 - ? [310] 12 processor(s) and 32639 MB RAM detected
2019-04-01 18:27:18 - ? [339] Local computer is MyServer-SQL running Windows Server 2012 R2 Standard 6.3 (9600)
2019-04-01 18:27:18 - ? [432] There are 12 subsystems in the subsystems cache
2019-04-01 18:27:18 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2019-04-01 18:27:19 - ? [353] Mail session started
2019-04-01 18:27:19 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2019-04-01 18:27:19 - + [425] delay_between_response attribute (10 sec) of alert (12) is less than poll interval for this alert (20 sec)
2019-04-01 18:27:19 - + [425] delay_between_response attribute (10 sec) of alert (11) is less than poll interval for this alert (20 sec)
2019-04-01 19:21:20 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...
2019-04-01 19:21:22 - ! [359] The local host server is not running
2019-04-01 19:21:22 - ! [359] The local host server is not running
2019-04-01 19:21:22 - ? [098] SQLServerAgent terminated (normally)
Seems mightily suspicious that "2019-04-01 18:27:17 - ? [000] Configuration option 'Agent XPs' changed from 0 to 1." is in there ... if this was as a result of some SQL Agent startup seems highly unlikely that Human was using sp_config at the same time
Have I got any of my analysis wrong?
Maybe cfgupddate and cfgupdtime are for the last RECONFIGURE, rather than the last sp_config change? in which case, unless I can find the time / user for sp_config I have no way of knowing if/when it changed?
August 14, 2019 at 6:09 pm
Try running sp_configure without the reconfigure on your test instance - and see if the values from DBCC CONFIG actually change. My guess is that they won't change until a reconfigure is performed...
That would lead me to believe the sp_configure change occurred at some point after the last restart and has not been applied (yet).
The message related to the Agent XP's is normal...and you can verify that by looking at the agent error logs. Every time SQL Server agent starts up it will execute that code.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 14, 2019 at 7:37 pm
Just running sp_configure (with or without using reconfigure) won't necessarily change the config block in master which is what DBCC CONFIG reports. It's often not flushed to disk until a restart. Paul Randal explains that in this blog post:
New script: When were the sp_configure options last changed?
Sue
August 15, 2019 at 6:25 am
Thanks both. Seems like it was most likely an accidental change, but we are totally mystified as to how that would happen. We don't go messing with that stuff (like all good DBAs ... 🙂 ) using e.g. sp_config
Accidental change in SSMS much more likely (just a bunch of form fields ... leaning on the keyboard could change something accidentally). But I assume that does do a RECONFIG when saved ... and we typically don't change anything like that with GUI
Maybe something else to add to our server-restart BATCH file.
Copy MASTER Data/Log (physical) files (before SQL starts) [been in situation in past where could not start SQL and hard work to restore MASTER]
New: add (after SQL starts) to do an sp_config and "permanently" save the results
Maybe there is an article on similar belt and braces stuff? We have Audit tables on pretty much all our APP Data Table and triggers to store every change, which makes post mortem "what happened" much easier. sys.tables seem very old fashioned in that regard ... modify date available on sys.objects, and therefore sys.tables, but not on anything else like sys.columns, sys.indexes etc. Makes retrospective "what happened" investigation hard work. Perhaps I ought to look into logging all sorts of system stuff to help with diagnosis
August 15, 2019 at 7:01 am
More careful checking reveals some other sp_config values that are different to the Running values
min server memory (MB) value=0, value_in_use=16
priority boost value=1, value_in_use=0
user connections value=8, value_in_use=0
Just can't think why any of us would have made any of these changes (and also NOT run RECONFIGURE after changing them ...). I wish I had the SQL Error File for when sp_config was run 🙁
Perhaps rather than / in addition to "archive at startup" I need a daily scheduled job that checks Server Config Setting Stuff against known-good-values and alerts if anything has changed.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply