Steve Hindmarsh's SQL Blog

SQL 2008 – cluster repair failure

Had an interesting issue recently, where I had some issues installing a SQL 2008 cluster on Windows 2008 R2 – installing a slipstreamed SQL 2008 with SP1.   After finally installing the cluster correctly – testing SQL failover correctly etc – I was satisfied I had a properly functional SQL Server installation.

However,  I then had a failure attempting to apply Cumulative Update 6…

CU6 failed to apply with error :

‘A failure was detected for a previous installation, patch, or repair during configuration for features [SQL_Engine_Core_Shared,CommonFiles,SQL_WRITER,SQL_Browser_Redist,]. In order to apply this patch package (KB968369), you must resolve any issues with the previous operation that failed. View the summary.txt log to determine why the previous operation failed. ‘

Note – to look in detail at installation failures – look at directory C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Log

A little investigation led me to believe that I needed to run the repair option from the SQL installer.

ie – from the SQL server installer – select maintenance, repair option..

To my horror, my repair then failed :-

Note – I’ve erased the SQL instance name in the above screenshot…

I now assumed that, due to previous errors, I’d got a rogue process still running – but after rebooting the server, I still got the same error.

Now stumped, I decided it was time to  downloaded the excellent Sysinternals tool..  specifically the process explorer…

With process explorer, I was easily able to identify that a WMI process was using the DLL mentioned above.  I killed the process, reran CU6 – everything worked fine.

I strongly recommend every production SQL DBA should spend some time familiarising themsevles with the sysinternals toolset.

Every DBA should read this..

Came across a very interesting article recently whilst reading a Joe Celko  blog and Tony Rogerson comment.  Although it’s not directly related to SQL Server,  and is a few years old now, I think every DBA should read this – here

Changing recovery model removes plans from procedure cache for a database ?

Did you know that when you change the recovery model of a database – plans for that database are removed from cache ? I didn’t…

SQL 2008 :-

I was doing some performance tuning today, using DMV sys.dm_exec_query_stats  to look at summary information for database performance.  I normally find this a very useful DMV as a starting point for any database tuning. However, to my surprise, today this DMV held very few rows for the database I was attempting to tune.

A little bit of investigation revealed that my oldest plan in cache for this database was created just after the recovery model had been changed from FULL to SIMPLE.   Now, I know that certain SQL Server operations can flush cache, for example changing the max degree of parallelism on a server – but I’d never come across this before..

I wonder how many DBAs are inadvertently flushing procedure cache if they are switching recovery models often – for example, to help with bulk-logged performance ?

Here’s a simple test script to demonstrate :-

1. Create a database

CREATE DATABASE stevetest

2. Confirm the recovery model is full.

SELECT name, recovery_model_desc FROM sys.databases

3  Create a test proc in the database.

USE stevetest

CREATE PROC steveproc
AS
SELECT * FROM sysindexes ORDER BY dpages DESC

4. Run the proc and check there is an entry in the procedure cache now

EXEC steveproc

SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.dbid = DB_ID('stevetest')

Will return 1 row.

5. Now change the recovery model

ALTER DATABASE database stevetest SET RECOVERY SIMPLE

6. Now look in the procedure cache again, the plan for my new proc has gone…

SELECT *
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE  qt.dbid = DB_ID('stevetest')

Will return 0 rows.

I see the same behaviour switching between all 3 recovery models.