Javascript required
Skip to content Skip to sidebar Skip to footer
Home / How to Check What Sql Server Version Is Installed

How to Check What Sql Server Version Is Installed

In this article, we will explore how to find the SQL Server version details with various methods.

Introduction

One of the important responsibilities of a database administrator is to keep the SQL Server installation up-to-date. The installation list for the update process can be created from the following items:

  • Service packs
  • Cumulative updates
  • General Distribution Releases (GDR)
  • Version upgrades

On the other hand, the SQL Server edition upgrade might be taken to the agenda by the database administrators to obtain new features. Such as, for some cases, we might be faced an issue that needs to determine the service pack level of the SQL Server. For this reason, the database administrator may always need to know the version and edition details of the SQL Server with whole details or at least know how to obtain it. Now, we will go through different methods on how to find version details of the SQL Server.

Finding the SQL Server version with SSMS

SQL Server Management Studio (SSMS) is a very helpful and handy tool in order to manage the SQL Server installations. Also, when we connect to any instance of the SQL Server with the help of SSMS, we can see that the product version is shown in the Object Explorer tab between two parentheses. This set of numbers defines the product version of the SQL Server. Now, we will learn how to convert the version details from this meaningless set of numbers:

To find SQL Server version

In the above screenshot, the product version is specified as 15.0.1400.75 but this set of numbers is not much meaningful and therefore we should convert it to more meaningful and useful information. The first two-digits (15) define the numeric definition of the SQL Server product name. The following table shows SQL Server versions against the product name so that we can find out the product name rather quickly:

SQL Server Product Version

SQL Server Product Name

15.0.xxxx.xx

SQL Server 2019

14.0.xxxx.xx

SQL Server 2017

13.0.xxxx.xx

SQL Server 2016

12.0.xxxx.xx

SQL Server 2014

11.0.xxxx.xx

SQL Server 2012

10.5.xxxx.xx

SQL Server 2008 R2

10.0.xxxx.xx

SQL Server 2008

9.0.xxxx.xx

SQL Server 2005

8.0.xxxx.xx

SQL Server 2000

However, we achieved the product name only with the help of the first two-digits but we need more details about the SQL Server instance. The remaining numbers (1400.75) provide assistance to uncover service pack level and cumulative update level details. In order to find out these details, we can look at the BuildNumbers website so that we can easily figure out all the detailed information about the SQL Server instance:

SQL Server version number details.

For our example, the version number is 15.0.1400.75 and it comes up to SQL Server 2019 CTP 2.4. (Community Technology Preview 2.4) which was released on March 26, 2019, as shown in the table. At the same time, this table helps us to discover the latest version of the SQL Servers.

Finding the SQL Server version with query

We can use the @@VERSION function to find out all version details of the SQL Server instance. The @@VERSION function returns a one-line string output and this output also provides all the necessary information about the SQL Server.

The output of this function will be involved in the following information:

  • SQL Server Version
  • Processor Architecture
  • SQL Server Build Date
  • Copyright Statement
  • SQL Server Edition
  • Operating System Version

Now, we will run the following query and get the details of the SQL Server:

SELECT @ @ VERSION AS 'SQL Server Version Details'

@@VERSION function output and SQL Server version details

"Microsoft SQL Server 2019 (CTP2.4) – 15.0.1400.75 (X64) Mar 16 2019 11:53:26 Copyright (C) 2019 Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows 10 Pro 10.0 <X64> (Build 17763: )"

As we can see, the output of the @@VERSION function returns sufficiently information about SQL Server. We will convert this information into a more meaningful form with the help of the following table:

SQL Server version

Microsoft SQL Server 2019 (CTP2.4)-15.0.1400.75

Processor architecture

X64

SQL Server build date

Mar 16 2019 11:53:26

Copyright statement

Copyright (C) 2019 Microsoft Corporation

SQL Server edition

Enterprise Evaluation Edition (64-bit)

Operating system version

Windows 10 Pro 10.0 <X64> (Build 17763: )

Finding the SQL Server version in the SQL Server error logs

SQL Server error log file records user-defined events and specific system events so that we can use it to troubleshoot SQL Server issues. Other than this, the SQL Server error log file helps us to determine the version details of the SQL Server. In the first few lines of the error log file, it contains detailed information about the version information about the SQL Server. By default, the error log is placed under "Program Files\Microsoft SQL Server\MSSQL.n\MSSQL\LOG\". The other option which helps us to determine the error log file location is the SERVERPROPERTY function. The following query returns the location of the error log file:

SELECT SERVERPROPERTY ( 'ErrorLogFileName' ) AS 'Error log file location'

SQL Server errorlog file path

Now, we will open the error log file in the specified location:

Find SQL Server version with help of the errorlog file

The entry of the error log file provides all details about the SQL Server build. The other method to read the error log is sp_readerrorlog procedure and the first line of the resultset indicates the SQL Server instance details:

sp_readerrorlog procedure resultset

Conclusion

In this article, we mentioned how to find out the SQL Server version with the different methods and also learned how to convert it to more meaningful information. After reading this article, you should be able to distinguish which SQL Server product version number corresponds exactly to which specific definition and other detailed information.

  • Author
  • Recent Posts

Esat Erkec

How to Check What Sql Server Version Is Installed

Source: https://www.sqlshack.com/how-to-find-sql-server-version/