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:
You can also use the T-SQL function SERVERPROPERTY() to get this information. For example, on this machine, I ran these queries:
This is fine, but what happens when I install Cumulative Update 1 for SQL Server 2008 SP1?
The same SERVERPROPERTY() query yield this result:
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: