[Blog Update]: August posts on SQLServerFAQ

I was working on a root cause analysis for an OOM (Out-of-Memory) issue for SQL Server 2008 R2 and I needed to schedule notifications when the available memory on the server fell below a certain value. SQL Server 2008 and above has a nifty little DMV to do just this. Read about it’s usefulness here:

The hidden gems among DMVs: sys.dm_os_sys_memory

Now SQL Server 2008 has BPA too

This has been an ask from the community for a long time now. We just launched SQL Server 2008 R2 Best Practices Analyzer. The SQL Server rules are executed using the Microsoft Baseline Configuration Analyzer framework. The rules are all Powershell based. So you need Powershell 2.0 and MBCA 2.0 to use SQL Server 2008 R2 BPA. This version of BPA has an extensive set of rules which can validate a lot of commonly known issues in areas like SQL Setup, Engine, Replication etc. This makes the work of a DBA much easier. Another cool feature about this tool is that you can run remote scans also against other SQL instances.

Addendum: April 13th, 2011

Note: It works with SQL Server 2008 and SQL Server 2008 R2.

Token Perm issues on SQL Server 2008

There were performance issues identified on SQL Server 2005 builds because of the large size of the access cache store on certain servers with huge ad-hoc query workloads. I had blogged about this earlier along with information on how to customize the cache store size using trace flags and registry entries after a certain build of SQL Server 2005.

With SQL Server 2008, we provide access check cache bucket count and access check cache quota size customization through sp_configure. So, if you do need to change these values, you do not have to add trace flags or make registry changes. It is as simple as changing the MaxDop or Max Server Memory on your server.

However, be advised that it is not recommended to change these values unless and until CSS recommends you to do so.

Reference:

Description of the "access check bucket count" and "access check cache quota" options that are available in the sp_configure stored procedure in SQL Server 2008

How to open Management Studio with the Object Explorer and a Query Window from command line

SQL Server Management Studio provides you the option of launching from command line. If you want to launch SSMS with a New Query Window and Object Explorer using command line, then you need to do the following changes.

Tools –> Options will open up the Options page for SSMS.

Under that you would see an At Startup property under Environment –> General. Use the drop down list to select the “Open a Object Explorer and New Query”. The default is shown below in the screen shot.

image

Once you have made the change, you can use the following command to launch SSMS connected to the instance you want:

ssms -S <server name> -E –nosplash

SSMS Command Line Options

—————————
Microsoft SQL Server Management Studio
—————————
Usage:
ssms.exe [-S server_name[\instance_name]] [-d database] [-U user] [-P password] [-E] [file_name[, file_name]] [/?]

    [-S    The name of the SQL Server instance to which to connect]
    [-d    The name of the SQL Server database to which to connect]
    [-E]    Use Windows Authentication to login to SQL Server
    [-U    The name of the SQL Server login with which to connect]
    [-P    The password associated with the login]
    [file_name[, file_name]] names of files to load
    [-nosplash] Supress splash screen
    [/?]    Displays this usage information

WMI Script for changing passwords of SQL Server 2008 and SQL Server 2012 instances

I had previously blogged on how to change the password of SQL Server 2005 and SQL Server 2000 instances using WMI [blog post]. I recently needed to change the passwords of my SQL Server 2008 instances for multiple instances. So, I thought it was high time I modified my script to change the passwords for SQL Server 2008 instances programmatically.

I just updated my script on Tech Net. Here is the link. For SQL Server 2008, I use the namespace:

\root\Microsoft\SqlServer\ComputerManagement10 (SQL Server 2008)

The SQL Server 2012 namespace is: \root\Microsoft\SqlServer\ComputerManagement11 (SQL Server 2012)

All the namespaces can detect lower version services as well till SQL Server 2005. Currently the script provides all the WMI namespaces available till date. You will need to modify the script or add checks in case a namepsace is not present.

If you are using SQL Server 2008 R2, then you need to have this fix applied so that SetServiceAccount can work:

2397020 FIX: “Access is denied. [0x80070005]” error message occurs in SSCM when you try to change the password of an account of SQL Server 2008 R2 in Windows Vista or in a later version of Windows http://support.microsoft.com/default.aspx?scid=kb;EN-US;2397020

If you use SetServiceAccount to change the password, then this will result in a SQL Server service restart. The script provided on the Technet Gallery is for changing both the service account and the password. If only the password needs to be changed for the service account, then use SetServiceAccountPassword. This will change the password and will not automatically restart your SQL Server service.