Home > SQL Configuration, SQLServer > SQL Server – How to identify service pack installed

SQL Server – How to identify service pack installed


Identifying current service pack installed for SQL Server can be difficult if you don’t know which command to use! Most of the time I have seen people using @@VERSION to check for SQL Server service pack level, which is not correct as it returns the service pack level of operation system and not SQL Server.

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2005 – 9.00.5000.00 (Intel X86)

       Dec 10 2010 10:56:29

       Copyright (c) 1988-2005 Microsoft Corporation

       Express Edition on Windows NT 6.0 (Build 6002: Service Pack 2)

Here the service pack reported is for Operating System. Service Pack for SQL Server can be identified by using SERVERPROPERTY() function as below, This returns SP4 which is the service pack level of SQL Server on my system:

SELECT SERVERPROPERTY('ProductLevel')

Result Set:

SP4

This has been "fixed" with SQL Server 2008+, @@VERSION now also returns service pack level for SQL Server:

SELECT @@VERSION

Result Set:

Microsoft SQL Server 2008 (SP3) – 10.0.5500.0 (Intel X86)

       Sep 22 2011 00:28:06

       Copyright (c) 1988-2008 Microsoft Corporation

       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

As you can see from the output, both SQL Server and Windows service pack are displayed. You can use SERVERPROPERTY('ProductLevel') for a cleaner output.

 

Hope This Helps!

Vishal

  1. Kevin Robinson
    05.06.2012 10:38 | #1

    Thanks for share this excellent post with us is really interesting, keep up the good work

  2. Guru Bharadwaj
    23.05.2013 06:30 | #2

    Awesome. Thanks for the info saved me time looking around.

  3. Michael Papalabrou
    08.08.2014 08:25 | #3

    Indeed, they fixed it at SQL10 but THEY BROKE IT AGAIN with SQL11 SP2. After installing SP2 @@version only reports 11.0.5058.0 (X64) and no SP level. Well done!

  4. Shalu
    27.08.2014 09:44 | #4

    Thanks Vishal

  5. Jim L.
    13.11.2014 03:31 | #5

    Ummm…doesn’t work for 2012…

    select @@version
    Microsoft SQL Server 2012 – 11.0.5058.0 (X64)
    May 14 2014 18:34:29
    Copyright (c) Microsoft Corporation
    Express Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) (Hypervisor)

    SELECT SERVERPROPERTY(‘ProductLevel’)
    SP2

  6. Jim L.
    13.11.2014 03:39 | #6

    I guess the only way to ensure the correct information is to check it via MS Website
    http://support.microsoft.com/kb/321185/en-us

  7. DumasLein
    10.01.2015 01:40 | #7

    Determining What Microsoft SQL Server Edition is Installed (in Spanish):

    http://www.sysadmit.com/2015/01/mssql-saber-la-version-de-sql-server-instalada.html

  8. Thami Nkosi
    19.03.2015 06:31 | #8

    Thank you Vishal. My boss even applauded me for executing the right command.

  9. Howard Emerich
    09.05.2015 09:02 | #9

    entering SELECT SERVERPROPERTY(‘ProductLevel’) at a command prompt gives me:
    ‘SELECT’ is not recognized as an internal or external command, operable program or batch file. How do I find the sp level of SQL 2005?

  1. No trackbacks yet.