System‑Administration Functions
SQL Server provides a set of system‑stored procedures and functions that help administrators monitor, configure, and manage databases and the server instance.
Key System‑Administration Stored Procedures
| Procedure | Description |
|---|---|
sp_who | Returns information about current users, sessions, and processes. |
sp_who2 | Extended version of sp_who with additional columns. |
sp_helpdb | Displays information about databases on the server. |
sp_helptext | Shows the definition of a stored procedure, trigger, view, or function. |
sp_configure | Shows or changes server configuration options. |
sp_lock | Displays current lock information. |
sp_resetstatus | Resets the status of a suspect or emergency mode database. |
sp_databases | Lists databases and size information. |
sp_spaceused | Reports the amount of space used by a database or object. |
Common Usage Examples
Viewing Active Sessions
EXEC sp_who2;
Checking Database Size
EXEC sp_spaceused N'MyDatabase';
Modifying a Server Option
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'max server memory (MB)', 4096;
RECONFIGURE;