Archive

Posts Tagged ‘serverproperty’

SQL Server – How to find Default data and log path for SQL Server 2012

06.05.2013 No comments

To identify default data and log directories in SQL Server 2012 you can use SERVERPROPERTY() function. In SQL Server 2012 two new parameters are added to SERVERPROPERTY() function namely, InstanceDefaultDataPath and InstanceDefaultLogPath which returns the default data and log directories respectively.

It can be used as below:

SELECT [Default Data Path] = SERVERPROPERTY('InstanceDefaultDataPath')

SELECT [Default Log Path]  = SERVERPROPERTY('InstanceDefaultLogPath')

GO

Result Set:

Default Data Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

 

Default Log Path

C:Program FilesMicrosoft SQL ServerMSSQL11.SQL2012MSSQLDATA

 

(1 row(s) affected)

You can also get this information from Server Properties dialog box in SQL Server Management Studio.

To identify this information for earlier version of SQL Server you need to use xp_instance_regread extended stored procedure which returns data from registry.

 

Earlier I posted on identifying default data directory for multiple instances through registry which returns this information for all instances installed on server.

Hope This Helps!

Vishal

SQL Server – How to identify service pack installed

25.12.2011 9 comments

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

How to check SQL Server version – TSQL

13.05.2011 1 comment

The most common way to check SQL Server is to use @@VERSION configuration function. It returns version, architecture, OS version and build date for current instance.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT @@VERSION AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (Intel X86)
       Apr  2 2010 15:53:02
       Copyright (c) Microsoft Corporation
       Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)

(1 row(s) affected)

Another way is to use SERVERPROPERTY() metadata function. For a full list of properties that can be returned, check BOL.

— © 2011 – Vishal (http://SqlAndMe.com)

SELECT SERVERPROPERTY('ProductVersion') AS [Version]

Version
—————————————————————————————————————————————————————————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

 

You can also use extended stored procedures to check SQL Server version:


sp_MSgetversion:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..sp_MSgetversion

Character_Value
——————– ———– ———–
10.50.1600.1         1           3

(1 row(s) affected)


xp_msver:

— © 2011 – Vishal (http://SqlAndMe.com)

EXEC master..xp_msver 'ProductVersion'

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
2      ProductVersion                   655410         10.50.1600.1

(1 row(s) affected)

To see the full list of properties returned by xp_msver, execute it without any arguments.

— © 2011 – Vishal (http://SqlAndMe.com)

 

EXEC master..xp_msver

Index  Name                             Internal_Value Character_Value
—— ——————————– ————– ————————————————————————————————————————————————————————————————
1      ProductName                      NULL           Microsoft SQL Server

2      ProductVersion                   655410         10.50.1600.1

3      Language                         1033           English (United States)

4      Platform                         NULL           NT INTEL X86

5      Comments                         NULL           SQL

6      CompanyName                      NULL           Microsoft Corporation

7      FileDescription                  NULL           SQL Server Windows NT

8      FileVersion                      NULL           2009.0100.1600.01 ((KJ_RTM).100402-1540 )

9      InternalName                     NULL           SQLSERVR

10     LegalCopyright                   NULL           Microsoft Corp. All rights reserved.

11     LegalTrademarks                  NULL           Microsoft SQL Server is a registered trademark of Microsoft Corporation.

12     OriginalFilename                 NULL           SQLSERVR.EXE

13     PrivateBuild                     NULL           NULL

14     SpecialBuild                     104857601      NULL

15     WindowsVersion                   393347078      6.0 (6002)

16     ProcessorCount                   2              2

17     ProcessorActiveMask              3              00000003

18     ProcessorType                    586            PROCESSOR_INTEL_PENTIUM

19     PhysicalMemory                   2041           2041 (2140626944)

20     Product ID                       NULL           NULL

(20 row(s) affected)

-– © 2011 – Vishal (http://SqlAndMe.com)


xp_instance_regread:

DECLARE @returnValue NVARCHAR(100)
EXEC   master..xp_instance_regread
       @rootkey              = N'HKEY_LOCAL_MACHINE',
       
@key                     = N'SOFTWAREMicrosoftMSSQLServerSetup',
       
@value_name       = N'Version',
       
@value                  = @returnValue output
SELECT @returnValue AS [Version]

Version
—————————————————————————————————-
10.50.1600.1

(1 row(s) affected)

Hope This Helps!

Vishal