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.
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:
This has been "fixed" with SQL Server 2008+, @@VERSION now also returns service pack level for SQL Server:
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!
This is a personal weblog. The opinions expressed here are my own and not of my employer. For accuracy and official references refer to MSDN, Microsoft TechNet, Books Online. I or my employer do not endorse any of the tools / applications / books / concepts mentioned here on my blog. I have simply documented my personal experiences on this blog.
- Backup & Recovery (11)
- Catalog Views (25)
- Certification (1)
- Common Table Expressions (6)
- Database Mail (1)
- Management Studio (38)
- Management Views and Functions (11)
- Partitioning (3)
- Service Pack Releases (2)
- SQL Agent (7)
- SQL Bugs (2)
- SQL Configuration (30)
- SQLServer (164)
- Uncategorized (1)
- Undocumented Functions (21)
- Working With Data (14)