Find SQL Server and Server information using a one line query

The following are the questions which are frequently asked to a DBA.

  • What the Version of SQL Server?
  • The Server on which it is hosted is running on Windows 32 bit or 64 bit?
  • What is the RAM available on the server?
  • How many processors does the Server has?

All these questions come at a time when he is trying to fix a high impact issue. Most of the questions will be from the Managers who will try to get ahead of the DBA and keep the customer happy. No harm with that but the DBA will start fixing the issue only after responding to these questions which come every now and then during the incident.

Even I was a part of such a situation recently. Someone on the call would ask these questions to the DBA (for many there is no difference between the DBA and the System Admin), after someone would ping on the Instant Messenger the same set of questions.

Time has taught me some good lessons. One of them is that before I start fixing any issues, I would make a note of the SQL Server\Windows configurations. One of the quickest and the one which I always use for this purpose is the xp_msver extended stored procedure.

Executing the following query will give enough information about the overall environment to the DBA.

EXEC xp_msver

When this command is executed without any parameters, the output spans 20 rows. All the values in the Name column can be passed as arguments to this stored procedure.

exec xp_msver 'ProcessorCount'
exec xp_msver 'ProductVersion', 'Platform'

With this output in hand, I usually move on to sp_configure. Now that the Lights, Camera are on I move on to “Action” to fix a High Severity issue.

3 thoughts on “Find SQL Server and Server information using a one line query

  1. Andy

    You can also use

    SELECT SERVERPROPERTY(‘productversion’), SERVERPROPERTY (‘productlevel’), SERVERPROPERTY (‘edition’)

    1. PradeepAdiga Post author

      SERVERPROERTY was surely an option. But xp_msver provides OS info along with SQL Server

Comments are closed.