SQL Server 2008 – High Memory Utilization / Memory Leak / Not releasing Memory

I have been working with SQL 2000 and since that knowing that SQL server ill use and release memory depending on its requirement and unitization.  This is not true with SQL 2008 while it is installed on Windows server 2008.

SQL 2008 will utilize as much memory assign to SQL Server while high resource oriented task running. It will never releasing this memory back to system though task finish. If any other task needed memory SQL internally flush out the older data from the memory. To release memory reserved by SQL 2008 you need to re-start SQL services. 

I also observed sometime in task manager also assigned memory not showing against the SQL Server.

How to use RunAs with SQL Server Management Studio and creating Quick Lunch

DBA having two separate account is normal practice in industry standard. In that case it is require often require to connect SSMS with different account. Run As is option for that.

There are two ways to connect SSMS with Run As
1. Press and Hold Shift Key and Right Click on SSMS gives you option for Run As.
2. Run as different user using Command Prompt and creating short cut helps you to quick login

Open command prompt and type runas /?

Enter below command in command prompt to open SSMS and it will prompt for password. You need to replace domain\spatel with your credentials.

C:\runas /user:domain\spatel "C:\Program Files\Microsoft SQL Server\100\Tools\Binn\VSShell\Common7\IDE\Ssms.exe"

It is easy to create shortcut for this.

Right click on desktop and click on create new short cut

And type above command in target. Adding this short cut to Quick Launch helps you to easy login in SSMS with ran as option. This will only ask for password so no need to enter user name everytime.

Controls the concatenation results with NULL are treated as NULL or empty string values with SET CONCAT_NULL_YIELDS_NULL

When SET CONCAT_NULL_YIELDS_NULL is ON, concatenating a null value with a string yields a NULL result. For example, SELECT 'Bunty' + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT 'Bunty' + NULL yields Bunty.

PRINT 'Setting CONCAT_NULL_YIELDS_NULL ON';

GO

SET CONCAT_NULL_YIELDS_NULL ON;

GO

SELECT 'Bunty' + NULL ;

GO

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

SELECT 'Bunty' + NULL;

GO

Below TSQL returns the current setting value for CONCAT_NULL_YIELDS_NULL

SELECT   SESSIONPROPERTY ('CONCAT_NULL_YIELDS_NULL')
 

How to change Dynamic Port of the SQL Server Named Instance to Static port on stand alone or SQL 2005 cluster?

SQL server the default instance has fixed TCP/IP port 1433 while for the named instances it has random dynamic selected port assigned. This is the default selection while you are installing SQL server. You might require changing the SQL server dynamic port to static port, especially when clients connect to the SQL server thru firewall. In that case particular port requires opening on firewall to allow connection to SQL Server.

Standalone Instance:

StartàProgram FilesàMicrosoft SQL 2005àConfiguration toolsàSQL Server Configuration manager.

Selection SQL Server 2005 network configuration, click on the “Protocols for ” and on right hand side right click  and go to properties of TCP/IP as shown in below screenshot.

Click on the IP Addresses tab on the top and keep the Dynamic port row Blank and write the require port number on TCP Ports row in the IP ALL section as shown in below screenshot.

It require the restart the SQL server services to change the take effects.

You can verify the setting by below registry key

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\ \MSSQLServer\SuperSocketNetLib\Tcp

Clustered Instance:

It requires following the same above steps but sometimes when you restart the services / node port change back to dynamic port. This is because while you restart the services local copy of registry not change and checkpoint get the old port number.

So you need to follow below steps.

Disable the check pointing to the quorum

Cluster res "SQL Server (Instance Name)" /removecheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Change the Dynamic port of the SQL server to static on all nodes as above mentioned steps.

Enable the check pointing to the quorum

Cluster res "SQL Server (Instance Name)" /addcheck: "Software\Microsoft\Microsoft SQL Server\MSSQL.3\MSSQLSERVER"

Make sure to give appropriate path for MSSQLSERVER. In above example I have MSSQL.3 because it is third instance I have installed. You may have different values.

Bring the SQL Server online after change.

Make sure port change after SQL services online.

How to find text/word inside SQL Server store procedure

Any of the following snippet allows you find a specific word or words from all SQL store procedure. This can be very useful if you want to find all references to table/specific word and remove/replace them. Below code should work on SQL server 2005/2008 (R2).

SELECT DISTINCT

        ( OBJECT_NAME(id) )

FROM    syscomments

WHERE   text LIKE '%Search_word%'

SELECT  ROUTINE_NAME ,

        ROUTINE_DEFINITION

FROM    INFORMATION_SCHEMA.ROUTINES

WHERE   ROUTINE_DEFINITION LIKE '%Search_word%'

        AND ROUTINE_TYPE = 'PROCEDURE'

      

SELECT  Name

FROM    sys.procedures

WHERE   OBJECT_DEFINITION(object_id) LIKE '%Search_word%'

   

SELECT  OBJECT_NAME(object_id)

FROM    sys.sql_modules

WHERE   Definition LIKE '%Search_word%'

        AND OBJECTPROPERTY(object_id, 'IsProcedure') = 1

SSIS Import/Export Wizard: “Unexpected Unrecoverable Error” in SQL 2008 R2.

In my last class I was teaching Import/Export feature for SQL 2008 on windows 7 thru  SQL Server Management Studio’s import and export wizard. I am getting “Unexpected and Unrecoverable Error” message with Abort,Retry and Cancel button. Any button you press it just abnormally close the import export wizard. This was very annoying and I was not able to get my work done.  

After some research I have found that you need to install the Extended .Net Framework 4. After installing this everything works as expected. You need to make sure to do windows update after the install as there is security patch for it.

Some people also suggest different method to edit “C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTSWizard.exe.config"  and comment out/delete . I tried this option but in my case it not works. 

I suggest to install .Net framework 4.

How to select / delete/update NULL record?

You cannot use  = NULL , you have to use IS NULL

e.g

-- Create temporary table

CREATE TABLE #TempSP (eid INT IDENTITY, Value1 varchar(10))

-- Insert sample values

INSERT INTO #TempSP values ('Value1')

INSERT INTO #TempSP values ('Value2')

INSERT INTO #TempSP values ('Value3')

INSERT INTO #TempSP values (NULL)

-- Select query for check what's in table

SELECT * FROM #TempSP

-- Select query where value1 is NULL

SELECT * FROM #TempSP

WHERE value1 IS NULL -- WHERE value1 = NULL won't work

-- Delete rows where value1 is NULL

DELETE FROM #TempSP

WHERE Value1 IS NULL  -- WHERE Value1 = NULL won't work

-- Select query for check what's in table

SELECT * FROM #TempSP

-- Drop temporary table

DROP TABLE #TempSP