Log Shipping – NORECOVERY vs. STANDBY mode

SQL Server is well known for being a memory hog. When installed, the default configuration allocates a ridiculous amount of memory (2147483647 MB) to the instance. In effect, this grants SQL Server access to whatever memory is assigned to the server, which it will try to use at times at the expense of operating system tasks. Best practice is therefore to reduce the memory allocated to the instance to reserve some for the OS, but be careful! If you accidentally enter 10 thinking you’re allocating 10 GB to SQL or simply hit return too early, you’re in for a rude awakening.

The Maximum Server Memory setting is measured in Megabytes. If you allocate too little memory (such as 10 MB), you will bring the SQL instance and its databases to their knees and won’t be able to get back into SQL Management Studio to correct it. If you try, you’ll get the following error message:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0 – No process is on the other end of the pipe.) (Microsoft SQL Server, Error: 233)

I this recently on one of my production SQL server 2012 boxes, it took a few minutes for the users to realize that they are being kicked out from the database. One of my coworker set it to 28MB instead of 28GB.  We tried to stop SQL Server , and start it using Command prompt.  The OS shows pending task, but couldn't started the service. After waiting for 10 minutes, we decided reboot the computer. In a nutshell these were the steps we followed

1. Stop all SQL Services thru “services.msc” , if  you set the memory to way low, SQL Server Configuration Manager wont work.
2. Run CMD as administrator, and run the statement  sqlservr -f -m”SQLCMD” 
3. If you see the same connection error, follow step 4 otherwise go to step
4. Next step is to disable the auto start for SQL Server service and restart the server, you need to run “services.msc” and do it
5. Once the system is back online, run step 2. you should be able to connect to your sql instance

   6.  Now, leave the above command prompt and open another command prompt as admin and run the following statement, press ctrl+c to close the connection

SQLCMD
sp_configure ‘show advanced options’,1;
GO
RECONFIGURE;
GO
sp_configure ‘max server memory’,4096;
GO
RECONFIGURE;
GO

 

 

7. Close the command prompts

8.  Set the services to auto start, and start them from services

9. Connect to the Server using SSMS  and verify the memory settings.