Wednesday, September 29, 2010

SQL Server Configuration Manager Password Change Denied

This work around may help if you get an access denied error while changing the password of a SQL Server service account in SQL Server Configuration Manager:

- Select Browse on the account name entry and re-enter the server account name

You should now be able to change the password.

Thursday, September 23, 2010

Creating and Storing SQL Passwords

2 great free tools for creating and storing your SQL passwords are:

Creating passwords:

Storing passwords:

Monday, September 6, 2010

SQL Server Best Practices Analyzer

Use the following links to download the appropriate SQL Server Best Practices Analyzer for your SQL Server installation.

The SQL Server Best Practices Analyzer will help you understand where your SQL installation differs from Microsoft best practices.

SQL Server 2008 R1 and R2 (June 2010) -

SQL Server 2005 (August 2008) -

SQL Server 2000 (April 2010) -

Virtual Machine Performance Investigations

This link is a must view if you are investigating or implementing virtual machine performance monitoring:

Friday, August 20, 2010

SQL Server 2008 Query Memory Usage

You may see Hash or Sort warnings in the default trace output and this is typically caused by query memory pressure.

If you would like to know how much memory a query is using, then the DMV sys.dm_exec_query_memory_grants may be able to help you.

An example for using the DMV is shown below:

DECLARE @mgcounter INT
SET @mgcounter = 1
WHILE @mgcounter <= 5 -- return data from dmv 5 times when there is data
          FROM sys.dm_exec_query_memory_grants) > 0
                 SELECT *
                 FROM sys.dm_exec_query_memory_grants mg
                             CROSS APPLY sys.dm_exec_sql_text(mg.sql_handle) -- shows query text
                 -- WAITFOR DELAY '00:00:01' -- add a delay if you see the exact same query in results
                 SET @mgcounter = @mgcounter + 1

Monday, August 16, 2010

SQL Server 2008 sys.dm_os_ring_buffers timestamp column

I have used the below code in SQL Server 2008 to reveal the sys.dm_os_ring_buffers timestamp column in a friendly format. 

SELECT Dateadd(ms,-1 * ((si.cpu_ticks / si.ms_ticks) - rb.TIMESTAMP),
Getdate()) AS time_stamp,
FROM sys.dm_os_ring_buffers rb,
sys.dm_os_sys_info si
ORDER BY time_stamp DESC

Sunday, August 15, 2010

Friday, August 13, 2010

MAP 5.0

The free Microsoft Assessment and Planning Toolkit (MAP) version 5.0 has been available for a while and is extremely useful for performing a quick SQL Server site discovery and audit. The key improvements over MAP version 4 is the inclusion of database, server configuration and login permissions data.

Guest Failover Clustering in a VMware virtualized environment

Interesting article about Microsoft support policy for Guest Failover Clustering in a VMware virtualized environment and why you may want to make use of this capability.

Microsoft SQL Server Migration Assistant for MySQL v1.0

Microsoft SQL Server Migration Assistant for MySQL v1.0 has been released which allows you to migrate from MySQL to SQL Server 2005, SQL Server 2008, SQL Server 2008 R2 and SQL Azure.

Premier Mission Critical

Microsoft offers an add-on to Premier Support called Premier Mission Critical which includes support for SQL Server:

Key extracts:

'After the initial consultation, customers receive a dedicated line for support, available 24 hours a day, seven days a week, with priority access to the most knowledgeable experts. If an incident occurs, Premier Mission Critical provides customers with the fastest problem resolution services available from Microsoft Corp., including a 30-minute response time and an engagement process that begins at an advanced support level.'

'Premier Mission Critical now provides support for the Microsoft .NET Framework, Microsoft BizTalk Server, Microsoft Exchange Server, Microsoft Office SharePoint Server, Microsoft Dynamics CRM, Microsoft System Center, Microsoft SQL Server and Windows Server. Support is currently available worldwide in English.'

'Pricing for Premier Mission Critical will be dependent on the level of engagement with Microsoft and the specific requirements of each customer. Customers wanting more information should contact their Microsoft account representatives.'