Friday, July 20, 2012

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.

Thursday, July 19, 2012

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.