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

Tuesday, March 24, 2015

What causes Multi-Page allocations?

In SQL Server versions before SQL 2012 single page allocations and multi-Page allocations are handled by different components, the Single Page Allocator (which is responsible for Buffer Pool allocations and governed by 'max server memory') and the Multi-Page allocator (MPA) which handles allocations of greater than an 8K page. If there are many multi-page allocations this can affect how much memory needs to be reserved outside 'max server memory' which may in turn involve setting the -g memory_to_reserve startup parameter.

So what kinds of query result in MPA activity? Here are few scenarios

1. A workload that has stored procedures with a large # of parameters (say > 100, > 500), and then invoked via large ad hoc batches, where each SP has different parameters will result in a plan being cached for this “exec proc” batch. This plan will result in MPA.


2. Another workload would be large adhoc batches of the form:


In SQL 2012 all page allocations are handled by an "Any size page allocator" and included in 'max server memory'. The buffer pool effectively becomes a client of the any size page allocator, which in turn relies on the memory manager.

SQL Maintenance Plan error “Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter.”

Some body have this error : Could not generate mail report.An exception occurred while executing a Transact-SQL statement or batch.No global profile is configured. Specify a profile name in the @profile_name parameter. When i execute my maintenance plan.



The issue is related to the default email profile, you have to set a default email profile in order for the maintenance plan send the email alert.


Please Follow These steps to Resolve this Problem

1. Go to Database mail

2. Right Click  Click on Configure Database mail.

3. wizard will click next Manage Profile security

5. Click Next

6. Beside Profile name  by default there will be no , Please make it yes

7. Finish

Wednesday, February 25, 2015

Difference between DateTime2, DateTime and SmallDateTime

Here are the main differences you should know about these three date types:

  Range of Dates



usage Applies to

January 1, 1900 - June 6, 2079

one Minute. smalldatetime values with 29.998 seconds or lower are rounded down to the nearest minute; values with 29.999 seconds or higher are rounded up to the nearest minute.

4 bytes

DECLARE @myDate SmallDatetime

SQL 2000 >+

January 1, 1753 - December 31, 9999

three-hundredths of a second (equivalent to 3.33 milliseconds or 0.00333 seconds).Values are rounded to increments of .000, .003, or .007 seconds

8 bytes

DECLARE @myDate Datetime

SQL 2000 >+

January 1, 0001 - December 31, 9999


can take 6-8 Bytes; by default it will take 7 bytes. 6 bytes for precisions less than 3; 7 bytes for precisions 3 and 4. All other precisions require 8 bytes

DECLARE @myDate   Datetime2(7)

>=SQL 2008



Armed with this knowledge, you may want to use SmallDateTime instead of DateTime if you only need to represent dates from January 1, 1900 to June 6, 2079 and you do not need accuracy below 1 minute. Why? Simple! Using SmallDateTime will reduce the amount of data your queries are pulling back. The size of each row will be a bit smaller.