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:
Limitations
When using
sp_configure
, you must run eitherRECONFIGURE
orRECONFIGURE WITH OVERRIDE
after setting a configuration option. TheRECONFIGURE WITH OVERRIDE
statement is usually reserved for configuration options that should be used with extreme caution. However,RECONFIGURE WITH OVERRIDE
works for all configuration options, and you can use it in place ofRECONFIGURE
.Note
RECONFIGURE
executes within a transaction. If any of the reconfigure operations fail, none of the reconfigure operations will take effect.Some property pages present information obtained via Windows Management Instrumentation (WMI). To display those pages, WMI must be installed on the computer running SQL Server Management Studio.
Server-level roles
For more information, see Server-level roles.
Execute permissions on sp_configure
with no parameters or with only the first parameter are granted to all users by default. 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 ALTER SETTINGS
permission is implicitly held by the sysadmin and serveradmin fixed server roles.
SQL Server Management Studio
View or change server properties
In Object Explorer, right-click a server, and then select Properties.
In the Server Properties dialog box, select a page to view or change server information about that page. Some properties are read-only.
Transact-SQL
View server properties by using the SERVERPROPERTY built-in 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 SERVERPROPERTY built-in 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 AdventureWorks2022; 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 to100
. The server must be restarted before the change can take effect.EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE; GO EXEC sp_configure 'fill factor', 100; GO RECONFIGURE; GO
For more information, see Server Configuration Options (SQL Server).
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-Related Dynamic Management Views and Functions.
View or change server properties
On the Start menu, point to All Programs, point to Microsoft SQL Server, point to Configuration Tools, and then select SQL Server Configuration Manager.
In SQL Server Configuration Manager, select SQL Server Services.
In the details pane, right-click SQL Server (<instancename>), and then select Properties.
In the SQL Server (<instancename>) Properties dialog box, change the server properties on the Service tab or the Advanced tab, and then select OK.
Restart after changes
For some properties, you might need to restart the server before the change can take effect.
Related content
- Server Configuration Options (SQL Server)
- Connect to the Database Engine
- SET Statements (Transact-SQL)
- SERVERPROPERTY (Transact-SQL)
- sp_configure (Transact-SQL)
- RECONFIGURE (Transact-SQL)
- SELECT (Transact-SQL)
- Configure WMI to Show Server Status in SQL Server Tools
- SQL Server Configuration Manager
- Configuration Functions (Transact-SQL)
- Server-Related Dynamic Management Views and Functions (Transact-SQL)