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

If you like this post, do like my Facebook Page -> SqlAndMe
EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

  1. Kevin Robinson
    October 5, 2012 at 10:38 pm

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

  2. Guru Bharadwaj
    May 23, 2013 at 6:30 am

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

  3. Shalu
    August 27, 2014 at 9:44 pm

    Thanks Vishal

  4. Michael Papalabrou
    October 8, 2014 at 8:25 pm

    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!

  5. Jim L.
    November 13, 2014 at 3:31 am

    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.
    November 13, 2014 at 3:39 am

    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
    January 10, 2015 at 1:40 am

    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
    March 19, 2015 at 6:31 pm

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

  9. Howard Emerich
    May 9, 2015 at 9:02 pm

    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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 316 other followers

%d bloggers like this: