Monday, July 27, 2015

SQL Max Memory Limit Too Low: Increase SQL server Maximum Memory from Command Prompt

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

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




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.

Thursday, July 16, 2015

What's the best PAGE_VERIFY setting

I always set this value to CHECKSUM. When CHECKSUM is enabled for the PAGE_VERIFY database option, the SQL Server Database Engine calculates a checksum over the contents of the whole page, and stores the value in the page header when a page is written to disk. When the page is read from disk, the checksum is recomputed and compared to the checksum value that is stored in the page header. This helps provide a high level of data-file integrity.

The following query, generate a script to  change the page verify option to CHECKSUM in case its different.

-- Generate ALTER DATABASE statements to change Page Verify option to CHECKSUM
FROM sys.databases AS db
WHERE db.page_verify_option_desc <> N'CHECKSUM';


Note: Remember, for existing database, the changes wont take effect immediately after you make the change. The database engine will calculate it only when the page is read thru any of  DML statement.



For more info check this post