SQL Server Service Packs and Hotfixes

What is Service Pack and Hot fixes in SQL Server?

With respect to SQL Server, if SQL Server has some in house error/bugs. We can’t resolve it. To remove product level error Microsoft has implemented two ways.

1. Service Packs


2. Hot fixes

 

What is the difference between a Service Pack and a Hotfix?

Service Packs normally have three properties:

·    Provide security fixes

• Correct software errors


• Enhance performance

Service Packs don’t add new functionality or change the interface dramatically. Service Packs are bundled into a programmed delivery method, and are cumulative. That means that you can install Service Pack three without applying Service Pack two, or even one. They are for general use — pretty much everyone should install the Service Pack

 A Hotfix is usually a specific security or software flaw that is addressed in code. There may or may not be a packaged delivery method — some Hotfixes just come with instructions of how and where to copy the patch. Hotfixes are normally not for everyone — Microsoft states that you should only apply the patch if you're having the specific problem it addresses. Even then, some Hotfixes are only available from a Microsoft support representative.

 

How to check which Service Pack installed?

Run below SQL Server command

SELECT SERVERPROPERTY('productversion')

, SERVERPROPERTY ('productlevel')

, SERVERPROPERTY ('edition')

GO

If you’re using SQL Server version 7, you need a different command:

SELECT @@VERSION

GO

 

The first line of what you will see shows the version number of SQL Server software installed on your server. The last three digits (called the build number) of the version number are used to determine the Service Pack installed on your SQL Server.

 

How to find which Hotfix applied on SQL Server?

It's a bit more difficult to determine the Hotfix number, since each one will be different, and many may be installed. To determine the Hotfixes on your server, download the program called HFNetChk (http://support.microsoft.com/kb/305385) from Microsoft. There's a commercial version of this tool by the way that will keep your servers up to date, but this is the free one Microsoft provides.

There are now stored procedures you can use to show you the build number on your server, such as:

EXEC sp_server_info 

GO

and 

EXEC master..xp_msver

GO

 

How to download/get the latest service packs?

You can download the latest service packs from the below URL

http://technet.microsoft.com/en-us/sqlserver/bb331754.aspx