January 26, 2016 at 11:59 pm
Hi all,
I have this scenario where in Win Server 2012 R2, two SQL 2012 FCI on 2 nodes Cluster, 1 is production max server memory (MB) = 110 GB out of 128 GB total memory which is working fine, the other instance is non production, max server memory (MB) was 5GB, tried to reduce it a little bit through the GUI, then Oops the keyboard got unusal stuck and the parameter change was applied already with 1 MB only, now the instance will not connect by any mean (either locally ore remotely): through regular SSMS connection, or DAC, etc...
Even the SQL Service is not able to start at all either from Services.msc or SQL Server Configuration Manager, and the SQL group showing failed state in the Failover Cluster Manager and cannot be brought Online again, Event Viewer showing this:
"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." and this:
"There was a memory allocation failure during connection establishment. Reduce nonessential memory load, or increase system memory. The connection has been closed".
PerfMon showing Memory counters of that instance is Zero! And I don`t have a backup of the master db (as it`s non-production instance).
So other than removing the SQL group & reinstalling the instance again, is there any workaround to connect to be able to fix this?!
Thanx
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
January 27, 2016 at 2:00 am
Have you tried starting it with the -f switch? (sqlservr.exe -f;-s INSTANCENAME, I think it is.)
https://technet.microsoft.com/en-us/library/ms178067%28v=sql.105%29.aspx
January 27, 2016 at 10:43 pm
@Beatrix: Thanx for your help, it worked, I didn`t know about this /f startup switch, could connect in a single user mode through this command: SQLCMD -E -S<servername>, then issued sp_configure command to rectify the issue, check this out:
https://msdn.microsoft.com/en-us/library/ms188236(v=sql.110).aspx
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply