View or change server properties (SQL Server)

Applies to: SQL Server

This article describes how to view or change the properties of an instance of SQL Server by using SQL Server Management Studio, Transact-SQL, or SQL Server Configuration Manager.

Steps depend on the tool:

SQL Server Management Studio
Transact-SQL
SQL Server Configuration Manager

Limitations

When you use sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option. The RECONFIGURE WITH OVERRIDE statement is usually reserved for configuration options that require extreme caution. However, RECONFIGURE WITH OVERRIDE works for all configuration options, and you can use it in place of RECONFIGURE.

Note

RECONFIGURE runs within a transaction. If any of the reconfigure operations fail, none of the reconfigure operations take effect.

Some property pages present information obtained through Windows Management Instrumentation (WMI). To display those pages, you must install WMI on the computer running SQL Server Management Studio.

Server-level roles

For more information, see Server-level roles.

All users are granted execute permissions on sp_configure with no parameters or with only the first parameter. To execute sp_configure with both parameters to change a configuration option or to run the RECONFIGURE statement, a user must be granted the ALTER SETTINGS server-level permission. The sysadmin and serveradmin fixed server roles implicitly hold the ALTER SETTINGS permission.

SQL Server Management Studio

In Object Explorer, right-click a server and select Properties. For more information about each page of the Server Properties dialog box, see Server Properties window.

Transact-SQL

View server properties by using the built-in SERVERPROPERTY function

Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example uses the built-in SERVERPROPERTY function in a SELECT statement to return information about the current server. This scenario is useful when there are multiple instances of SQL Server installed on a Windows-based server, and the client must open another connection to the same instance that is used by the current connection.
```
SELECT CONVERT (sysname, SERVERPROPERTY('servername'));
GO
```

View server properties by using the sys.servers catalog view

Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example queries the sys.servers catalog view to return the name (name) and ID (server_id) of the current server, and the name of the OLE DB provider (provider) for connecting to a linked server.
```
USE master;
GO

SELECT name,
       server_id,
       provider
FROM sys.servers;
GO
```

View server properties by using the sys.configurations catalog view

Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example queries the sys.configurations catalog view to return information about each server configuration option on the current server. The example returns the name (name) and description (description) of the option, its value (value), and whether the option is an advanced option (is_advanced).
```
SELECT name,
       description,
       value,
       is_advanced
FROM sys.configurations;
GO
```

Change a server property by using sp_configure

Connect to the Database Engine.
From the Standard bar, select New Query.
Copy and paste the following example into the query window and select Execute. This example shows how to use sp_configure to change a server property. The example changes the value of the fill factor option to 100. The server must be restarted before the change can take effect.
```
USE master;
GO

EXECUTE sp_configure 'show advanced options', 1;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'fill factor', 100;
GO

RECONFIGURE;
GO

EXECUTE sp_configure 'show advanced options', 0;
GO

RECONFIGURE;
GO
```
For more information, see Server configuration options.

SQL Server Configuration Manager

Some server properties can be viewed or changed by using SQL Server Configuration Manager. For example, you can view the version and edition of the instance of SQL Server, or change the location where error log files are stored. These properties can also be viewed by querying the Server dynamic management views and functions.