Trying to keep up with Version Numbers….

  • Article

Updated. See information at the bottom of the post

I can’t begin to tell you how many times I find myself seeing a version number for SQL Server and not knowing exactly what service pack or cumulative update the build belongs to without quite a bit of pain. I have to admit years ago (ok how about a decade ago) we just had to worry about service packs so I practically had these numbers memorized, but when we introduced the cumulative update model, there were too many build numbers to remember.

If you happen to have an ERRORLOG file or can query the SQL Server Engine, you do have some information you can use to determine if a build is RTM or Service Pack.

On my laptop I have SQL Server 2008 Service Pack 1. How do I know this?

First at the top of the ERRORLOG file is version information:

2009-06-02 15:39:54.73 Server      Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64)
    Mar 29 2009 10:22:31 2007.0100.2531.00
    Copyright (c) 1988-2008 Microsoft Corporation
    Developer Edition (64-bit) on Windows NT 6.0 <X64> (Build 6001: Service Pack 1)

Notice right after the major version of SQL Server it has (SP1) and right before the version (or build) number.

This same information is available by running the following query:

select @@version

You can also use the T-SQL function SERVERPROPERTY() to get this information. For example, on this machine, I ran these queries:

select SERVERPROPERTY('productversion'), SERVERPROPERTY('productlevel')

and got these results

10.0.2531.0    SP1

This is fine, but what happens when I install Cumulative Update 1 for SQL Server 2008 SP1?

The same SERVERPROPERTY() query yield this result:

10.0.2710.0    SP1

So you cannot tell from any T-SQL query whether you are running a cumulative update. Furthermore, there may be other times where you don’t have the ERRORLOG or can’t run a T-SQL query to see this data. For me, many of these situations are when I’m evaluation a problem by looking at a user dump file. I can see the version by examining the properties of SQLSERVR.EXE in the dump, but I don’t know if this belongs RTM, a service, pack, or cumulative update.

I thought there might be a need to build a table that shows all of these release versions. But after searching the web a bit, I found that my friend from PASS Bill Graziano already has such a site posted at:

https://www.sqlteam.com/article/sql-server-versions

This link has a table with major SQL Server versions, a link to the fix article to download the fix, and a date when the version was released.

So now I have a reference point myself for those situations where I know a build number but am not quite sure what cumulative update it belongs to.

Since the time of the original post, one of my colleagues Mark House pointed out to me another great resource for version numbers at:

https://www.sqlsecurity.com/FAQs/SQLServerVersionDatabase/tabid/63/Default.aspx

 

Bob Ward
Microsoft