Essential SQL Server Cheat Sheet: 80+ Queries for Administration and Monitoring

Instance‑level information

Retrieve any server property using SELECT SERVERPROPERTY('propertyname').

Configuration values

View a specific configuration setting:

SELECT * FROM sys.configurations WHERE name='XX'

Change a configuration value (requires immediate effect with RECONFIGURE WITH OVERRIDE when the value is out of the allowed range):

sp_configure 'XX','0'
RECONFIGURE WITH OVERRIDE

System objects and views

List objects in the current database:

SELECT * FROM sys.all_objects -- all schema‑scoped objects
SELECT * FROM sys.sysobjects   -- all objects

Database‑level system views:

SELECT * FROM sys.databases          -- includes online/offline state
SELECT * FROM sys.sysdatabases       -- deprecated, does not show online state

Global and database‑specific system views

SELECT * FROM sys.database_files   -- one row per file stored in each database
SELECT * FROM sys.master_files      -- one row per file across the whole instance

MSDB‑only system tables

SELECT * FROM dbo.backupset
SELECT * FROM dbo.log_shipping_secondary
SELECT * FROM dbo.restorehistory
SELECT * FROM dbo.sysjobs
SELECT * FROM dbo.sysjobhistory

Useful stored procedures

sp_lock
sp_who
sp_who2
sp_helptext

Examples of lock‑related queries:

sp_lock
SELECT request_session_id spid, OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks WHERE resource_type='OBJECT' ORDER BY request_session_id

Session and process information

SELECT * FROM sys.sysprocesses WHERE blocked>0
SELECT * FROM sys.dm_exec_requests WHERE session_id>50 ORDER BY cpu_time DESC

Backup and restore monitoring

DBCC SQLPERF (LOGSPACE)
SELECT name, log_reuse_wait_desc FROM sys.databases
DBCC LOGINFO
DBCC CHECKDB (msdb)
CHECKPOINT [checkpoint_duration]
SELECT name, State, user_access, is_read_only, recovery_model FROM sys.databases

Backup progress:

SELECT DB_NAME(database_id) AS Exec_DB, percent_complete,
       CASE WHEN estimated_completion_time<36000000 THEN '0' ELSE '' END +
       RTRIM(estimated_completion_time/1000/3600) + ':' +
       RIGHT('0'+RTRIM((estimated_completion_time/1000)%3600/60),2) + ':' +
       RIGHT('0'+RTRIM((estimated_completion_time/1000)%60),2) AS [Time Remaining],
       b.text AS tsql, *
FROM sys.dm_exec_requests
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS b
WHERE command LIKE 'Backup%'
ORDER BY 2 DESC

Restore script generation (full, log, differential):

SELECT CONVERT(CHAR(100),SERVERPROPERTY('Servername')) AS Server,
       bs.database_name, bs.backup_start_date, bs.backup_finish_date,
       CASE bs.type WHEN 'D' THEN 'Database' WHEN 'L' THEN 'Log' END AS backup_type,
       bs.backup_size, bmf.logical_device_name, bmf.physical_device_name,
       bs.name AS backupset_name, bs.description,
       'RESTORE DATABASE ['+bs.database_name+'] FROM DISK=N''' + bmf.physical_device_name + '''WITH NORECOVERY;' AS RestoreCmd
FROM msdb.dbo.backupset bs
JOIN msdb.dbo.backupmediafamily bmf ON bmf.media_set_id = bs.media_set_id
WHERE bs.backup_start_date > DATEADD(DAY,-1,GETDATE())
ORDER BY bs.backup_finish_date

Always On and Availability Groups

SELECT dc.database_name, d.synchronization_health_desc, d.synchronization_state_desc, d.database_state_desc
FROM sys.dm_hadr_database_replica_states d
JOIN sys.availability_databases_cluster dc ON d.group_database_id = dc.group_database_id
WHERE d.is_local = 1

SELECT * FROM sys.database_mirroring

SELECT * FROM sys.dm_hadr_cluster_members
SELECT * FROM sys.dm_hadr_instance_node_map
SELECT * FROM sys.dm_hadr_cluster
SELECT * FROM sys.dm_hadr_name_id_map
SELECT * FROM sys.dm_hadr_cluster_networks
SELECT * FROM sys.availability_group_listeners

FILESTREAM and FILETABLE

SELECT SERVERPROPERTY('FilestreamShareName')
SELECT db_name(database_id), * FROM sys.database_filestream_options
SELECT object_name(object_id), * FROM sys.filetables
SELECT FileTableRootPath() + [file_stream].GetFileNamespacePath(), name FROM testdb.dbo.table1

Job management

SELECT sj.Name,
       CASE WHEN sja.start_execution_date IS NULL THEN 'Not running'
            WHEN sja.start_execution_date IS NOT NULL AND sja.stop_execution_date IS NULL THEN 'Running'
            ELSE 'Not running' END AS RunStatus
FROM msdb.dbo.sysjobs sj
JOIN msdb.dbo.sysjobactivity sja ON sj.job_id = sja.job_id
WHERE sja.session_id = (SELECT MAX(session_id) FROM msdb.dbo.sysjobactivity)
ORDER BY RunStatus DESC

Start a job:

EXECUTE msdb.dbo.sp_start_job N'job_name'

Index information and maintenance

SELECT a.name index_name, c.name table_name, d.name column_name
FROM sysindexes a
JOIN sysindexkeys b ON a.id=b.id AND a.indid=b.indid
JOIN sysobjects c ON b.id=c.id
JOIN syscolumns d ON b.id=d.id AND b.colid=d.colid
WHERE a.indid NOT IN (0,255) AND c.name='XX'

Generate execution plan without running the query:

SET SHOWPLAN_ALL ON;
GO
SELECT XXX;
GO
SET SHOWPLAN_ALL OFF;
GO

Rebuild or reorganize indexes (online where supported):

ALTER INDEX ALL ON table_name REBUILD WITH (online=on)
ALTER INDEX index_name ON table_name REBUILD WITH (online=on)
ALTER INDEX ALL ON table_name REORGANIZE
ALTER INDEX index_name ON table_name REORGANIZE

Space usage

SELECT name, size*8/1024 AS TotalSize_MB,
       CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS UsedSize_MB,
       size*8/1024 - CAST(FILEPROPERTY(name,'SpaceUsed') AS int)*8/1024 AS Availabesize_MB
FROM sys.master_files WHERE database_id = DB_ID(N'DBNAME')

Top 10 tables by data size (method 1):

CREATE TABLE #tabName(...);
DECLARE @name VARCHAR(100);
DECLARE cur CURSOR FOR SELECT name FROM sysobjects WHERE xtype='u' ORDER BY name;
OPEN cur;
FETCH NEXT FROM cur INTO @name;
WHILE @@FETCH_STATUS=0
BEGIN
    INSERT INTO #tabName EXEC sp_spaceused @name;
    FETCH NEXT FROM cur INTO @name;
END
CLOSE cur; DEALLOCATE cur;
SELECT TOP 10 table_name, data_size, rowsNum FROM #tabName ORDER BY CONVERT(INT,SUBSTRING(data_size,0,LEN(data_size)-2)) DESC;

Alternative method using sys.tables and allocation units:

SELECT TOP 10 t.NAME AS TableName, s.Name AS SchemaName,
       SUM(a.total_pages)*8/1024 AS TotalSpace_MB,
       SUM(a.used_pages)*8/1024 AS UsedSpace_MB,
       (SUM(a.total_pages)-SUM(a.used_pages))*8/1024 AS UnusedSpace_MB
FROM sys.tables t
JOIN sys.indexes i ON t.OBJECT_ID = i.object_id
JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
JOIN sys.allocation_units a ON p.partition_id = a.container_id
JOIN sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
GROUP BY t.Name, s.Name
ORDER BY TotalSpace_MB DESC;

Security and permissions

USE dbname;
EXEC sp_helprotect @username = 'username';
GRANT EXECUTE TO "username";

Miscellaneous useful queries

Identify identity column name:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='table' AND COLUMNPROPERTY(OBJECT_ID('table'),COLUMN_NAME,'IsIdentity')=1

Get identity seed: SELECT IDENT_SEED('table') Get identity increment: SELECT IDENT_INCR('table') Get current identity value: SELECT IDENT_CURRENT('table') Reset identity seed: DBCC CHECKIDENT (table, RESEED, XX) Find DDL changes via trace: SELECT * FROM sys.traces then open the trace file with SQL Profiler.

This cheat sheet consolidates essential T‑SQL snippets for SQL Server administrators, enabling quick inspection of server properties, configuration, system metadata, performance counters, backup/restore status, replication health, Always On availability groups, FILESTREAM settings, job states, index health, space consumption, and security permissions.