Microsoft SQL 2008 R2 won’t let you login after a server reboot…but why?

6/26/15

 

If you ever do a rolling restart on a SQL server and you get the below error when you attempt to login to the SQL Studio you can have a ‘ooo fuck’ moment thinking the database poofed.

 

attachment.php

 

However I had this lovely not so fun error happen to me and this is what I did to get it resolved.

If you get this error, make sure all of the services for the network protocols in the SSCM are working correctly. Also make sure all of the SQL services are running, if they aren’t sometimes you need to re-apply the ‘login as’ information and password for the service account. This can get tedious but it sometimes does the trick and those SA account somehow get corrupt.

For me it turns out somehow the max memory for the SQL server database got changed to 0 and in turn prevented the login into the database. I ended up doing something I remembered from A+ days. 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 them would be in memory….

I went to start and typed ‘cmd’ and then copied it and moved it to the DATA folder (of the SQL Server Installation) where the ‘sqlsrvr’ is located and I ran this command line under a elevated administrative UAC command prompt:  ‘sqlsrvr -f’ 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. Alternatively you could have just done a ‘cd /file location’ from a normal command prompt but I’m anal.

Now I couldn’t login with the normal panel inside of the SQL Studio so I needed 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 after right clicking the MSSQL server and going to properties and then Memory and changing the Max memory to something other than 0, like say 3500 or whatever you max memory is for your server (minus 1.5 GB’s for the Operating system) I had to write this script and execute it line-by-line (in my instance our SQL server had 32 GB’s of RAM)….


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 and it allowed me to login to the SQL Studio normally and without a problem but dam it was an annoying issue to troubleshoot.

 

Hope this helps someone else.