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

  • SQL Server Get Table and Index Storage Size Report Script

    calendar May 24, 2026 / May 25, 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 / May 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: Find Databases With No Recent Backup

    calendar Apr 19, 2026 / May 22, 2026 · 6 min read · sql server backup recovery msdb monitoring database administration sql scripts backup audit sys.databases  ·
    Share on: twitter facebook linkedin copy

    Finding databases that have not been backed up recently is one of the most important checks a DBA can run. A database that grows unnoticed without a backup is a data loss event waiting to happen. This script queries msdb.dbo.backupset against sys.databases to surface any online user database with no full backup in the …


    Read More

  • SQL Server TempDB Usage and Contention Report

    calendar Apr 17, 2026 / May 22, 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 / May 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  ·
    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 Error Log Search Script: xp_readerrorlog

    calendar Apr 8, 2026 / May 22, 2026 · 7 min read · sql server sql scripts xp_readerrorlog sp_readerrorlog error log database administration monitoring diagnostics maintenance scripts sys.sp_enumerrorlogs  ·
    Share on: twitter facebook linkedin copy

    Search the SQL Server Error Log with xp_readerrorlog This script reads and filters the SQL Server error log using xp_readerrorlog and sys.sp_readerrorlog, supporting keyword search, date range filtering, and access to archived log files — all from T-SQL without touching the file system. Purpose and Overview The SQL …


    Read More

  • SQL Server Top Queries by CPU and IO: dm_exec_query_stats

    calendar Apr 4, 2026 / May 22, 2026 · 6 min read · sql server performance sql scripts sys.dm_exec_query_stats sys.dm_exec_sql_text sys.dm_exec_query_plan query tuning database administration monitoring execution plan  ·
    Share on: twitter facebook linkedin copy

    Find the Top CPU and IO Consuming Queries in SQL Server This script queries sys.dm_exec_query_stats to identify the most resource-intensive queries currently cached in the plan cache, showing logical reads, writes, CPU time, and elapsed time — with the full query text and execution plan for each. Purpose and Overview …


    Read More

  • SQL Server Database Size Report for All Databases

    calendar Apr 2, 2026 / May 22, 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 / May 22, 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

SQLServer70.com

Your Source for SQL Server Scripts
Read More

Recent Posts

  • SQL Server Duplicate and Overlapping Index Detection Script
  • SQL Server Get Table and Index Storage Size Report Script
  • SQL Server Object Dependencies Report: Find All References
  • SQL Server Find Columns by Data Type Across the Database
  • SQL Server List All Foreign Keys with Referenced Tables
  • SQL Server VLF Count Report: Virtual Log File Analysis
  • SQL Server Database File Space Usage Report
  • SQL Server Max Server Memory Calculator Script

Categories

SCRIPTS 47 SQL SERVER ADMINISTRATION 9 SQL SERVER MONITORING 8 SQL SERVER BACKUP RECOVERY 7 SQL SERVER MAINTENANCE 7 SQL SERVER PERFORMANCE 7 SQL SERVER STRUCTURE 6 SQL SERVER SECURITY 3

Series

ADMIN-GUIDE 47

Tags

SQL SERVER 46 DATABASE ADMINISTRATION 34 SQL SCRIPTS 33 PERFORMANCE 15 MAINTENANCE SCRIPTS 13 SYS.TABLES 12 SYSOBJECTS 10 DBA SCRIPTS 9 MONITORING 9 SYS.DATABASES 9 DATABASE MAINTENANCE 8 MSDB 8 SYS.OBJECTS 8 BACKUP RECOVERY 6
All Tags
ACTIVE SESSIONS2 ADMINISTRATION2 AUTOMATION2 BACKUP1 BACKUP AUDIT1 BACKUP COMPRESSION1 BACKUP MONITORING2 BACKUP RECOVERY6 BACKUP VALIDATION1 BACKUPSET1 BLOCKING2 BOTTLENECK ANALYSIS1 CAPACITY PLANNING2 CLUSTERED INDEXES1 COLUMNS1 CONSTRAINT REPORTING1 CONTENTION1 COPY ONLY BACKUP1 CPU CONFIGURATION1 CPU HISTORY1 CPU UTILIZATION1 CURSOR PROGRAMMING1 DATA TYPES1 DATABASE1 DATABASE ADMINISTRATION34 DATABASE CONNECTIONS1 DATABASE FILES1 DATABASE INTEGRITY1 DATABASE MAINTENANCE8 DATABASE MANAGEMENT3 DATABASE MONITORING2 DATABASE OBJECTS1 DATABASE OPTIMIZATION1 DATABASE SCRIPTS1 DATABASE SECURITY1 DATABASE STRUCTURE4 DBA SCRIPTS9 DBA TOOLS1 DBCC1 DBCC CHECKDB1 DBCC CHECKIDENT1 DBCC COMMANDS5 DBCC DBINFO1 DBCC DBREINDEX2 DBCC LOGINFO1 DEPENDENCIES1 DEV RESTORE1 DIAGNOSTICS1 DIFFERENTIAL BACKUP1 DISK SPACE1 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 DUPLICATE INDEXES1 DYNAMIC SQL2 ERROR LOG1 EXECUTION PLAN1 FILEPROPERTY1 FOREIGN KEYS2 FRAGMENTATION2 FULL BACKUP1 GRANT STATEMENTS1 HEAP TABLES2 IDENTITY COLUMNS1 IMPACT ANALYSIS1 INDEX MAINTENANCE3 INDEX OPTIMIZATION1 INDEX REBUILDING1 INDEX SIZE1 INDEX TUNING2 INDEXES4 INDEXING1 INFORMATION_SCHEMA.TABLE_CONSTRAINTS1 INFORMATION_SCHEMA.TABLES1 INTEGRITY CHECK1 JOB MONITORING1 KILL SPID1 LOG MANAGEMENT1 LOGIN MANAGEMENT1 MAINTENANCE SCRIPTS13 MAX SERVER MEMORY1 MAXDOP1 MEMORY CONFIGURATION1 METADATA1 MISSING INDEXES1 MONITORING9 MSDB8 MSDB.DBO.BACKUPMEDIAFAMILY1 MSDB.DBO.BACKUPSET1 NONCLUSTERED INDEXES1 NUMA1 OBJECT DEPENDENCIES1 OBJECT SEARCH1 OPEN TRANSACTIONS1 OPTIMIZATION2 ORPHAN USERS1 PERFORMANCE15 PERFORMANCE MONITORING1 PERFORMANCE TUNING5 PERMISSIONS2 POINT IN TIME RESTORE1 PRIMARY KEYS2 QUERY OPTIMIZATION2 QUERY TUNING3 REBUILD REORGANIZE1 RECOVERY1 REFACTORING1 REFERENTIAL INTEGRITY1 REPORTING3 RESTORE1 RESTORE VERIFYONLY1 RING BUFFER1 ROLE MEMBERSHIPS1 SCHEMA ANALYSIS2 SCHEMA AUDIT1 SCHEMA DOCUMENTATION1 SCHEMA MANAGEMENT1 SCRIPTS3 SECURITY3 SECURITY AUDIT2 SERVER MONITORING1 SESSION MANAGEMENT1 SP CONFIGURE1 SP_CHANGE_USERS_LOGIN1 SP_CONFIGURE2 SP_PKEYS1 SP_READERRORLOG1 SP_SPACEUSED1 SPACE MANAGEMENT1 SPACE USAGE1 SQL1 SQL SCRIPTS33 SQL SERVER46 SQL SERVER AGENT1 SQL SERVER MONITORING1 SQLSERVER PROCESS UTILIZATION1 STATISTICS1 STORAGE2 STORED PROCEDURES1 SYS.ALL_COLUMNS1 SYS.ALL_OBJECTS1 SYS.ALLOCATION_UNITS2 SYS.COLUMNS3 SYS.COMPUTED_COLUMNS1 SYS.CONFIGURATIONS1 SYS.DATABASE_FILES1 SYS.DATABASE_PERMISSIONS1 SYS.DATABASE_PRINCIPALS2 SYS.DATABASE_ROLE_MEMBERS1 SYS.DATABASES9 SYS.DM_DB_INDEX_PHYSICAL_STATS5 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_STATS1 SYS.DM_EXEC_QUERY_PLAN1 SYS.DM_EXEC_QUERY_STATS1 SYS.DM_EXEC_REQUESTS2 SYS.DM_EXEC_SESSIONS2 SYS.DM_EXEC_SQL_TEXT3 SYS.DM_OS_PERFORMANCE_COUNTERS1 SYS.DM_OS_RING_BUFFERS1 SYS.DM_OS_SYS_INFO1 SYS.DM_OS_VOLUME_STATS1 SYS.DM_OS_WAIT_STATS1 SYS.DM_OS_WAITING_TASKS1 SYS.DM_SQL_REFERENCED_ENTITIES1 SYS.DM_SQL_REFERENCING_ENTITIES1 SYS.DM_TRAN_ACTIVE_TRANSACTIONS1 SYS.DM_TRAN_LOCKS1 SYS.FOREIGN_KEY_COLUMNS1 SYS.FOREIGN_KEYS3 SYS.INDEX_COLUMNS2 SYS.INDEXES5 SYS.KEY_CONSTRAINTS1 SYS.MASTER_FILES2 SYS.OBJECTS8 SYS.PARTITIONS3 SYS.SCHEMAS2 SYS.SERVER_PRINCIPALS1 SYS.SP_ENUMERRORLOGS1 SYS.SQL_EXPRESSION_DEPENDENCIES1 SYS.SQL_MODULES1 SYS.TABLES12 SYS.TYPES1 SYS.VIEWS1 SYSCOLUMNS1 SYSDATABASES1 SYSINDEXES2 SYSJOBHISTORY1 SYSJOBS1 SYSOBJECTS10 SYSTEM IDLE PROCESS1 SYSTEM TABLES3 T-SQL3 TABLE ANALYSIS1 TABLE SIZE1 TABLE STATISTICS1 TEMPDB1 TRANSACTION LOG2 TRANSACTION LOG BACKUP1 UPDATE STATISTICS1 VIEWS1 VIRTUAL LOG FILES1 WAIT STATISTICS1 WAIT TYPES3 XP_READERRORLOG1
[A~Z][0~9]
SQLServer70.com

Copyright  SQLSERVER70.COM. All Rights Reserved

to-top