SQL Server Scripts
open-menu closeme
Home
All SQLServer70 Scripts
Scripts & Commands
Links icon
SQL Server Documentation SQL Server Mgmnt Studio
About
Contact Us
Privacy Statement
rss

  • Spot Low-Memory Conditions with sys.dm_os_process_memory

    calendar Oct 9, 2026 / Oct 9, 2026 · 10 min read · sql server sys.dm_os_process_memory sys.dm_os_sys_memory sys.dm_os_sys_info sys.dm_os_memory_clerks sys.dm_db_xtp_table_memory_stats memory pressure low memory buffer pool monitoring scripts  ·
    Share on: twitter facebook linkedin copy

    sys.dm_os_process_memory is SQL Server's process-level counterpart to the operating system's own memory counters — a single row reporting exactly how much of the machine's memory space the sqlservr.exe process is holding, down to large-page allocations, committed virtual address space, and two flags that fire the …


    Read More

  • Decode PLE and Buffer Cache Hit Ratio via cntr_type Codes

    calendar Sep 6, 2026 / Sep 27, 2026 · 10 min read · sql server sys.dm_os_performance_counters page life expectancy buffer cache hit ratio cntr_type buffer manager sys.dm_os_sys_info sys.dm_exec_requests numa monitoring scripts  ·
    Share on: twitter facebook linkedin copy

    How many seconds should a data page sit in the buffer pool before SQL Server decides it isn't needed anymore, and where does that number actually come from? Page life expectancy is one row among hundreds returned by sys.dm_os_performance_counters, and reading it correctly — along with its neighbor, buffer cache hit …


    Read More

  • Age Open Transactions with sys.dm_tran_active_transactions

    calendar Aug 2, 2026 / Sep 27, 2026 · 9 min read · sql server sys.dm_tran_active_transactions sys.dm_tran_session_transactions sys.dm_exec_sessions long running transactions transaction log blocking monitoring dmv scripts  ·
    Share on: twitter facebook linkedin copy

    A transaction that never commits keeps every log record written after it pinned in place, no matter how much work piles up behind it. sys.dm_tran_active_transactions lists every transaction currently open on the instance, but on its own it only shows a transaction ID and a start time — pairing it with …


    Read More

  • SQL Server Get Table and Index Storage Size Report Script

    calendar May 24, 2026 / Sep 22, 2026 · 7 min read · sql server storage index size table size monitoring sql scripts sys.dm_db_partition_stats sys.indexes sys.tables capacity planning database administration sys.dm_db_index_physical_stats sys.dm_db_index_usage_stats sys.objects sys.partitions  ·
    Share on: twitter facebook linkedin copy

    Knowing how much space each table and each index consumes is the foundation of capacity planning, archive policy, and index cleanup decisions on any SQL Server instance. This T-SQL script reads the partition-level storage statistics out of sys.dm_db_partition_stats and reports the size of every index — clustered, …


    Read More

  • SQL Server Database File Space Usage Report

    calendar May 17, 2026 / Sep 22, 2026 · 8 min read · sql server sys.master_files sys.dm_os_volume_stats database files disk space monitoring sql scripts database administration fileproperty sys.database_files sys.databases  ·
    Share on: twitter facebook linkedin copy

    Running out of space inside a data or log file halts SQL Server transactions immediately — and discovering that a volume is full only after an outage is too late. This script queries sys.master_files and sys.dm_os_volume_stats across every online database on the instance to report each file's allocated size, space …


    Read More

  • SQL Server TempDB Usage and Contention Report

    calendar Apr 17, 2026 / Jun 2, 2026 · 7 min read · sql server tempdb dm db session space usage dm exec sessions dm exec requests dm exec sql text monitoring dba scripts space usage contention performance  ·
    Share on: twitter facebook linkedin copy

    TempDB is a shared system database used by every session in SQL Server. When TempDB space grows or performance slows, it can be difficult to know which sessions are responsible. This script queries sys.dm_db_session_space_usage to show a clear breakdown of TempDB consumption per session, including the current SQL …


    Read More

  • SQL Server Long-Running Queries: Find Active Sessions

    calendar Apr 12, 2026 / Sep 22, 2026 · 6 min read · sql server performance monitoring sql scripts sys.dm_exec_requests sys.dm_exec_sql_text blocking wait types database administration active sessions sys.dm_exec_connections sys.dm_exec_sessions  ·
    Share on: twitter facebook linkedin copy

    Find Active Long-Running Queries in SQL Server This script queries sys.dm_exec_requests and sys.dm_exec_sql_text to show all currently executing queries on the SQL Server instance, sorted by how long they have been running, along with CPU time, I/O activity, blocking status, and wait type information. Purpose and …


    Read More

  • SQL Server Database Size Report for All Databases

    calendar Apr 2, 2026 / Jun 2, 2026 · 6 min read · sql server monitoring sql scripts sys.master_files sys.databases database administration capacity planning storage reporting maintenance scripts  ·
    Share on: twitter facebook linkedin copy

    Report Database Size in MB and GB for All SQL Server Databases This script queries sys.master_files and sys.databases to report the total allocated size in megabytes and gigabytes for every user database on the SQL Server instance, sorted by largest database first. Purpose and Overview Knowing how large each database …


    Read More

  • SQL Server Blocking Detection: Find Blocked Sessions

    calendar Mar 1, 2026 / Jun 2, 2026 · 5 min read · sql server blocking performance dm exec requests dm exec sessions wait types dba scripts monitoring query tuning sys.databases sys.dm_exec_requests sys.dm_exec_sessions sys.dm_exec_sql_text sys.dm_tran_locks  ·
    Share on: twitter facebook linkedin copy

    Blocking in SQL Server occurs when one session holds a lock that another session needs, causing the second session to wait. This T-SQL script queries the dynamic management views to show all currently blocked sessions, the blocking session, wait type, wait duration, and the SQL text involved. Purpose and Overview When …


    Read More

  • Generate sp_spaceused Commands for All User Tables in T-SQL

    calendar Aug 7, 2025 / Oct 4, 2026 · 7 min read · sql server database administration space management database monitoring table analysis system tables database scripts sp_spaceused sys.tables sysobjects sys.objects sys.dm_db_partition_stats  ·
    Share on: twitter facebook linkedin copy

    Checking how much space every table in a database uses means running sp_spaceused once per table, and typing those calls by hand stops being practical after a dozen tables. This one-line query reads the object catalog and writes the sp_spaceused call for every user table, so the whole set can be pasted into a query …


    Read More
    • ««
    • «
    • 1
    • 2
    • »
    • »»

SQLServer70.com

Your Source for SQL Server Scripts
Read More

Recent Posts

  • Spot Low-Memory Conditions with sys.dm_os_process_memory
  • Profile Costly Cached Procs with sys.dm_exec_procedure_stats
  • Check AG Replica Health with sys.dm_hadr_availability_replica_states
  • Decode PLE and Buffer Cache Hit Ratio via cntr_type Codes
  • Audit Server Configuration Drift with sys.configurations
  • Find Ad Hoc Plan Cache Bloat with sys.dm_exec_cached_plans
  • Age Open Transactions with sys.dm_tran_active_transactions
  • Rank Expensive Queries with sys.query_store_runtime_stats

Categories

SCRIPTS 74 SQL SERVER ADMINISTRATION 14 SQL SERVER MAINTENANCE 14 SQL SERVER PERFORMANCE 14 SQL SERVER MONITORING 11 SQL SERVER STRUCTURE 11 SQL SERVER BACKUP RECOVERY 7 SQL SERVER SECURITY 3

Series

SQL SERVER ADMINISTRATION 14 SQL SERVER MAINTENANCE 14 SQL SERVER PERFORMANCE 14 SQL SERVER MONITORING 11 SQL SERVER STRUCTURE 11 SQL SERVER BACKUP RECOVERY 7 SQL SERVER SECURITY 3

Tags

SQL SERVER 73 DATABASE ADMINISTRATION 39 SQL SCRIPTS 39 SCRIPTS 24 PERFORMANCE 21 MAINTENANCE SCRIPTS 19 SYS.OBJECTS 19 MONITORING 18 SYS.TABLES 17 DATABASE MAINTENANCE 13 SYS.DATABASES 12 DBCC COMMANDS 11 SYS.INDEXES 11 MSDB 10
All Tags
ACTIVE SESSIONS2 AD HOC QUERIES1 ADMINISTRATION4 ADVANCED OPTIONS1 ALTER INDEX1 ALWAYS ON1 ANSI STANDARD1 AUTOMATION2 BACKUP1 BACKUP AUDIT1 BACKUP COMPRESSION1 BACKUP MONITORING2 BACKUP RECOVERY6 BACKUP VALIDATION1 BACKUPSET1 BLOCKING3 BOTTLENECK ANALYSIS1 BUFFER CACHE HIT RATIO1 BUFFER MANAGER1 BUFFER POOL2 CAPACITY PLANNING2 CARDINALITY ESTIMATION1 CATALOG VIEWS6 CLUSTERED INDEXES1 CNTR_TYPE1 COLUMNS1 COMPATIBILITY VIEWS1 COMPILED PLAN STUB1 CONFIGURATION DRIFT1 CONSISTENCY CHECKS2 CONSTRAINT REPORTING1 CONTENTION1 COPY ONLY BACKUP1 CPU CONFIGURATION1 CPU HISTORY1 CPU TIME1 CPU UTILIZATION1 CROSS DATABASE1 CURSOR PROGRAMMING1 DATA AUDIT1 DATA CACHE1 DATA COMPRESSION1 DATA DICTIONARY1 DATA TYPES1 DATABASE1 DATABASE ADMINISTRATION39 DATABASE CONNECTIONS1 DATABASE CORRUPTION2 DATABASE FILES2 DATABASE INTEGRITY1 DATABASE MAIL1 DATABASE MAINTENANCE13 DATABASE MANAGEMENT3 DATABASE MEMORY1 DATABASE MONITORING2 DATABASE OBJECTS1 DATABASE OPTIMIZATION1 DATABASE SCHEMA1 DATABASE SCRIPTS1 DATABASE SECURITY1 DATABASE STRUCTURE9 DBA SCRIPTS9 DBA TOOLS1 DBCC1 DBCC CHECKDB4 DBCC CHECKIDENT2 DBCC COMMANDS11 DBCC DBINFO1 DBCC DBREINDEX2 DBCC FREEPROCCACHE1 DBCC LOGINFO1 DBCC SHRINKFILE2 DEPENDENCIES1 DEV RESTORE1 DIAGNOSTICS1 DIFFERENTIAL BACKUP1 DISK IO1 DISK LATENCY1 DISK SPACE2 DM DB MISSING INDEX DETAILS1 DM DB MISSING INDEX GROUP STATS1 DM DB SESSION SPACE USAGE1 DM EXEC REQUESTS2 DM EXEC SESSIONS3 DM EXEC SQL TEXT1 DM OS WAIT STATS1 DMV4 DOCUMENTATION1 DUPLICATE INDEXES1 DYNAMIC SQL3 ERROR LOG1 EXECUTION PLAN1 FAILOVER READINESS1 FILEPROPERTY1 FOR SYSTEM_TIME1 FOREIGN KEYS2 FRAGMENTATION2 FULL BACKUP1 GRANT STATEMENTS1 HEAP TABLES2 HISTORY TABLE1 IDENTITY COLUMNS2 IDENTITY RESEED1 IMPACT ANALYSIS1 INDEX FRAGMENTATION2 INDEX MAINTENANCE5 INDEX OPTIMIZATION1 INDEX REBUILDING1 INDEX SIZE1 INDEX TUNING2 INDEXES4 INDEXING1 INFORMATION_SCHEMA2 INFORMATION_SCHEMA.TABLE_CONSTRAINTS1 INFORMATION_SCHEMA.TABLES1 INTEGRITY CHECK2 IO STALL1 JOB MONITORING1 KILL SPID1 LOG MANAGEMENT1 LOGIN MANAGEMENT1 LONG RUNNING TRANSACTIONS1 LOW MEMORY1 MAINTENANCE2 MAINTENANCE SCRIPTS19 MAX SERVER MEMORY1 MAXDOP1 MEMORY1 MEMORY CLERKS1 MEMORY CONFIGURATION1 MEMORY PRESSURE2 METADATA7 MISSING INDEXES1 MONITORING18 MSDB10 MSDB.DBO.BACKUPMEDIAFAMILY1 MSDB.DBO.BACKUPSET1 NONCLUSTERED INDEXES1 NUMA2 OBJECT DEPENDENCIES1 OBJECT SEARCH1 OPEN TRANSACTIONS1 OPTIMIZATION2 OPTIMIZE FOR AD HOC WORKLOADS1 ORPHAN USERS1 PAGE COMPRESSION1 PAGE CORRUPTION1 PAGE LIFE EXPECTANCY1 PARAMETER SNIFFING1 PERFORMANCE21 PERFORMANCE MONITORING1 PERFORMANCE TUNING8 PERMISSIONS2 PLAN CACHE4 POINT IN TIME QUERY1 POINT IN TIME RESTORE1 PRIMARY KEYS3 PROCEDURE CACHE1 QUERY OPTIMIZATION2 QUERY OPTIMIZER1 QUERY PERFORMANCE1 QUERY STORE2 QUERY TUNING3 REBUILD REORGANIZE1 RECONFIGURE1 RECOVERY1 REFACTORING1 REFERENTIAL INTEGRITY1 REPORTING3 RESTORE1 RESTORE VERIFYONLY1 RING BUFFER1 ROLE MEMBERSHIPS1 ROW COMPRESSION1 SCHEMA ANALYSIS2 SCHEMA AUDIT1 SCHEMA DOCUMENTATION1 SCHEMA MANAGEMENT1 SCRIPTS24 SECURITY4 SECURITY AUDIT2 SERVER MONITORING1 SESSION MANAGEMENT1 SIZE_IN_BYTES1 SP CONFIGURE1 SP_CHANGE_USERS_LOGIN1 SP_CONFIGURE3 SP_EXECUTESQL1 SP_PKEYS2 SP_READERRORLOG1 SP_SEND_DBMAIL1 SP_SPACEUSED1 SP_UPDATESTATS1 SPACE MANAGEMENT1 SPACE USAGE1 SQL1 SQL SCRIPTS39 SQL SERVER73 SQL SERVER AGENT2 SQL SERVER MONITORING1 SQLSERVER PROCESS UTILIZATION1 STATISTICS2 STORAGE2 STORAGE ERROR1 STORED PROCEDURES2 SUSPECT PAGES1 SYNCHRONIZATION HEALTH1 SYS.ALL_COLUMNS1 SYS.ALL_OBJECTS1 SYS.ALLOCATION_UNITS3 SYS.AVAILABILITY_GROUPS1 SYS.AVAILABILITY_REPLICAS1 SYS.COLUMNS9 SYS.COMPUTED_COLUMNS1 SYS.CONFIGURATIONS4 SYS.DATABASE_FILES2 SYS.DATABASE_PERMISSIONS1 SYS.DATABASE_PRINCIPALS2 SYS.DATABASE_ROLE_MEMBERS1 SYS.DATABASES12 SYS.DM_DB_INDEX_PHYSICAL_STATS9 SYS.DM_DB_INDEX_USAGE_STATS5 SYS.DM_DB_MISSING_INDEX_DETAILS1 SYS.DM_DB_MISSING_INDEX_GROUP_STATS1 SYS.DM_DB_MISSING_INDEX_GROUPS1 SYS.DM_DB_PARTITION_STATS3 SYS.DM_DB_STATS_PROPERTIES1 SYS.DM_DB_XTP_TABLE_MEMORY_STATS1 SYS.DM_EXEC_CACHED_PLANS2 SYS.DM_EXEC_CONNECTIONS1 SYS.DM_EXEC_PLAN_ATTRIBUTES1 SYS.DM_EXEC_PROCEDURE_STATS1 SYS.DM_EXEC_QUERY_PLAN1 SYS.DM_EXEC_QUERY_STATS2 SYS.DM_EXEC_REQUESTS3 SYS.DM_EXEC_SESSIONS4 SYS.DM_EXEC_SQL_TEXT6 SYS.DM_EXEC_TEXT_QUERY_PLAN1 SYS.DM_HADR_AVAILABILITY_GROUP_STATES1 SYS.DM_HADR_AVAILABILITY_REPLICA_STATES1 SYS.DM_HADR_DATABASE_REPLICA_CLUSTER_STATES1 SYS.DM_HADR_DATABASE_REPLICA_STATES1 SYS.DM_IO_VIRTUAL_FILE_STATS1 SYS.DM_OS_BUFFER_DESCRIPTORS1 SYS.DM_OS_MEMORY_CLERKS2 SYS.DM_OS_PERFORMANCE_COUNTERS2 SYS.DM_OS_PROCESS_MEMORY1 SYS.DM_OS_RING_BUFFERS2 SYS.DM_OS_SCHEDULERS1 SYS.DM_OS_SYS_INFO5 SYS.DM_OS_SYS_MEMORY1 SYS.DM_OS_VOLUME_STATS1 SYS.DM_OS_WAIT_STATS1 SYS.DM_OS_WAITING_TASKS1 SYS.DM_PDW_NODES_EXEC_PROCEDURE_STATS1 SYS.DM_SERVER_SERVICES1 SYS.DM_SQL_REFERENCED_ENTITIES1 SYS.DM_SQL_REFERENCING_ENTITIES1 SYS.DM_TRAN_ACTIVE_TRANSACTIONS2 SYS.DM_TRAN_LOCKS1 SYS.DM_TRAN_SESSION_TRANSACTIONS1 SYS.FOREIGN_KEY_COLUMNS2 SYS.FOREIGN_KEYS4 SYS.IDENTITY_COLUMNS1 SYS.INDEX_COLUMNS7 SYS.INDEXES11 SYS.KEY_CONSTRAINTS3 SYS.LOGIN_TOKEN1 SYS.MASTER_FILES4 SYS.OBJECTS19 SYS.PARTITIONS8 SYS.PROCEDURES1 SYS.QUERY_STORE_PLAN1 SYS.QUERY_STORE_QUERY1 SYS.QUERY_STORE_QUERY_TEXT1 SYS.QUERY_STORE_RUNTIME_STATS1 SYS.QUERY_STORE_RUNTIME_STATS_INTERVAL1 SYS.SCHEMAS8 SYS.SERVER_PRINCIPALS2 SYS.SP_ENUMERRORLOGS1 SYS.SQL_EXPRESSION_DEPENDENCIES1 SYS.SQL_MODULES2 SYS.STATS1 SYS.TABLES17 SYS.TYPES2 SYS.VIEWS1 SYSCOLUMNS1 SYSDATABASES1 SYSINDEXES3 SYSJOBHISTORY1 SYSJOBS1 SYSMAIL_ACCOUNT1 SYSMAIL_ADD_ACCOUNT_SP1 SYSMAIL_EVENT_LOG1 SYSMAIL_MAILITEMS1 SYSMAIL_PROFILE1 SYSOBJECTS10 SYSTEM IDLE PROCESS1 SYSTEM TABLES4 SYSTEM VERSIONING1 T-SQL2 TABLE ANALYSIS1 TABLE INVENTORY1 TABLE MAINTENANCE1 TABLE SIZE1 TABLE STATISTICS1 TEMPDB1 TEMPORAL TABLES1 TRANSACTION LOG3 TRANSACTION LOG BACKUP1 UPDATE STATISTICS2 USECOUNTS1 VIEWS1 VIRTUAL LOG FILES1 WAIT STATISTICS1 WAIT TYPES3 XP_CMDSHELL1 XP_READERRORLOG1 XTP1
[A~Z][0~9]
SQLServer70.com

Copyright  SQLSERVER70.COM. All Rights Reserved

to-top