Problem

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Scenario

Our replication on our clustered database just started to fail. We were getting the following error:

Agent message code 4502. View or function ‘sys.dm_exec_sessions’ has more column names specified than columns defined.

Ok, this is a weird message. How can sys.dm_exec_sessions have more columns that specified?

We checked the definition:

EXEC sp_helptext 'sys.dm_exec_sessions'

And this is what we got:

CREATE VIEW sys.dm_exec_sessions AS
 SELECT *
 FROM OpenRowset(TABLE SYSSESSIONS)


Ok, we can see how this can be a problem – because of the star (*). Any column change can spell disaster. However, we weren’t very sure why this would cause any problems, because this is not any regular user view. It’s a built in SQL Server DMV.

So we checked out a few things:

SELECT 
 SERVERPROPERTY('ResourceVersion'),
 SERVERPROPERTY('ProductVersion'),
 SERVERPROPERTY('ProductLevel')    -- Service Pack

It turns out the installed Service Packs are not the same in all the nodes.

Resolution

Make sure all nodes in the cluster have the same service pack. Sometimes it’s easy to miss because you have to failover to install the SP.

VN:F [1.9.22_1171]
Rating: 7.0/10 (6 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)
View or function ‘sys.dm_exec_sessions’ has more column names …, 7.0 out of 10 based on 6 ratings  
Be Sociable, Share!
  • Tweet