It all started so innocently.
A SQL Server 2005 installation. An installation of SQL Workbench, sorry, “Microsoft SQL Server Management Studio.” And a need to find out what’s going on. Click, click, click – Activity Monitor. Double-click. Uh-oh.
(Incidentally, why is it that the Alt+PrintScreen option sometimes does that to an image? It’s annoying…)
So many potential reasons for failure in this message. Let’s see:
- “Unable to execute requested command.” – that’s vague.
- “Unable to launch Activity Monitor. You may not have sufficient permissions. (ActivityMonitor)” – I’m sysadmin. This isn’t an issue.
- “View or function ‘master.sys.dm_exec_sessions’ has more column names specified than columns defined. (Microsoft SQL Server, Error: 4502)” – OK, it must be this.
Googling for this gave me the following from the SQLServerCentral forums: SQL Server Management Studio is the wrong version for the server. This sounds, y’know, wrong, but, just for kicks, I’m going to try running the query through SSMS, to see if the GUI is to blame. Oh dear:
Which implies to me that this might not be the case, but let’s humour ’em anyway. SQLWB’s Help/About shows SQLWB version 9.00.1399, which corresponds neatly with the version of SQL Server installed. So it’s not that.
Further googling throws up a page on the SQLMusings blog, which discusses a potential issue when running SQL Server in a clustered environment – it is possible that all versions of SQL Server that make up the cluster have not been patched to the same level. Easy enough to check – and this is not the problem. (I was going by file version / size / creation date for the SQLServr.EXE file – this was 9.00.1399 / ?35MB? / Nov 2005 across all servers. Identical, anyway, and what was expected.)
(Apologies for vagueness with the file size – this was a while ago, and google isn’t helping me…)
Anyway, identical SQL Server patch levels across the servers, based upon a cursory inspection. What to look at next?
The SQLMusings post mentioned above includes a SQL query to run, so I ran it. The query:
SELECT SERVERPROPERTY('ResourceVersion'), SERVERPROPERTY('ProductVersion'), SERVERPROPERTY('ProductLevel') -- Service Pack
This checks a value from the Resource Database, and a couple of values from the master database. And, if all is well, will bring back something like the following (from a nice, fresh, proper installation, such as that on my laptop):
However, on the server(s) in question, we got:
The Resource Database, bless it, thinks it’s running SQL Server 2005 SP3. And the master database thinks it’s running SQL Server 2005 RTM.
(1) How did this happen?
(2) How do we fix this?
(1) In this particular instance, SQL Server had not been installed by SQL Server DBAs, but by a sysadmin / infrastructure support team with no SQL Server experience, training or knowledge. They managed the basic RTM software installation, but didn’t run the service pack installation, and just copied every database file they could from the existing environment into the identical place on the new environment. Fortunately, the basic infrastructure configuration was compatible, so things mostly worked.
(2) To fix this? God knows. I reckon that a service pack installation would have failed as the Resource database already thought it was SPd. Finding another SQL2005 RTM server and copying back the mssqlresourcedb files may or may not have worked.
In the event, as there were quite a few other things wrong with the server installation / configuration, mostly as a result of this (SMK, anyone?), we recommended that the whole cluster be scragged and rebuilt from scratch.
To end on a question – what would *you* have recommended?