SQL Database Guru's: Do you know how to fix this?

Deathmage

Hey guys,

so while doing a normal rolling restart today, our SQL server logged into windows rather fast and I was like wow that reboot fixed stuff then I got curious and I look in memory and the memory in the task manager was only 2.15 GB's for a server that normally caches the SQL database.

Now I checked all of the services in the SSCM for SQL 2008 R2, and the Shared Memory, TCP/IP, Named Pipes are all enabled and I've done the typical troubleshooting with turning them off and restarting the 'SQL Server Services' but no matter what I do I keep getting this popup:



I've found this well known script to resolve this issue becasue it seems I have too many active connections, but the problem is, with the error above, I can't gain access to a 'New Query' (since I can't login) in order for me to run the following script:

EXEC sys.sp_configure N'show advanced options', N'1'RECONFIGUREWITHOVERRIDE
GO
EXEC sys.sp_configure N'user connections', N'0'
GO
RECONFIGUREWITHOVERRIDE
GO
EXEC sys.sp_configure N'show advanced options', N'0'RECONFIGUREWITHOVERRIDE
GO


So I'm curious if anyone ever ran into this issue before or knows how to access the sp_configure parameter in like say a PowerShell script so I could gain access to our database and see what the hell happened.

Any insight would be appreciated, I've google the crap out of this error and they all point ot the obvious of turning off the objects is the SQL 2008 R2 SSCM so now I'm staying here all night if I have too to figure out what in gods creation happened.

...Figures the Friday night I was going to do the tail end of my CCENT and this happens.... it just doesn't want me to take the exam....

Deathmage

Dear lord that was a pain in my darry air!

It's now 2:44 am and I'm now finished. I think I googled 30+ errors and painfully doing each one until I found the problem.

it turns out somehow the max memory for the SQL server database got changed to 0 and in turn prevent login into the database. I must have looked at 3000 lines of registry code looking for a hook into this issue, and then I got desperate and I started googling all the erors I was getting when I was messing with SSCM.

I ended up doing something I remembered from A+ days. I went to start and type 'cmd' and then copied it and moved it to the DATA folder where the 'sqlsrvr' is located and I ran this command line under a elevated administrative UAC prompt:

But before I did all this I had to set every SQL service to manual, disabled TCP/IP in SSCM for all protocols and reboot the SQL Server so NONE of thme would be in memory....

"sqlservr -f -m”SQLCMD” -d \SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf -e \SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG -l \SQLData\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf"

Then after I worked through the registry errors, the lovely 17113 error. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e624b4f7-b10f-4928-b345-f81dbc5af753/sql-error-17113-while-starting-the-the-server-in-single-user-mode?forum=sqldatabaseengine

I then moved on to blocking the 1434 (DAC login - the OOO **** login - my luck it was disabled!!!!!!!!!!!!!!!!! !!!!!!!!!!!!!!!!!!!!!!!) port on the Sonicwall and on the local firewall to make sure no servers could send a request for the SQL server (super annoying)

So then after finding that out I went a different route and got resourcesful with the above commands, I just typed 'sqlsrvr -f' in a elevated command prompt, while opening the 'cmd' from the primary SQL folder so it could access the SQL .dll files to work correctly, to enable a singe-user login for SQL with minimal memory allocation.

Now I couldn't login with the normal panel so I need to to use the "Connect to Database Engine" login and use the local administrator account to login.

Then after finding out you simply can't modify the memory I had to write this script....


EXEC sp_configure 'Show Advanced Options',1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'max server memory (MB)',31500;
GO
RECONFIGURE;
GO
EXEC sp_configure 'remote admin connections', 5;
GO RECONFIGURE;
GO

now once all this was done I reset all the service to automatic and then rebooted the SQL server.

I went and go a Red Bull out of the car and logged in and the problem is fixed....she is caching...




9+ hours of stress and aggrevation defeated.... now to go home and sleep on MY FRIDAY!!!!

Kai123

I truly wish I understood any of that.

So, does that make you a SQL Database Guru?

Deathmage

Kai123 wrote: »

I truly wish I understood any of that.

So, does that make you a SQL Database Guru?

I don't think I'm a guru with anything just a humble person. However I did troubleshoot the snot out of that problem...with 3 red bulls and 3 protein bars later...

techfiend

Glad you figured it out and the memory issue could help others. It's kind of amazing what has to be done to change things in MS SQL. In postgresql and mysql it's simply one command or editing a config file. It also makes me wonder why so many companies pay the big bucks for MS SQL when it has many disadvantages to other rdbms'.

Matt2

Congrats on figuring it out. The logs "should" show when the config changed. If not, might not hurt to get logging in a improved state.