SQL Server Data and Log File Size, Free Space and Autogrowth
Shows file size, used and free space, and growth settings. Used for capacity planning and catching bad autogrowth configs.
SELECT DB_NAME() AS db_name,
f.name AS logical_name, f.type_desc, f.physical_name,
CAST(f.size / 128.0 AS DECIMAL(18,1)) AS size_mb,
CAST(FILEPROPERTY(f.name, 'SpaceUsed') / 128.0 AS DECIMAL(18,1)) AS used_mb,
CAST((f.size - FILEPROPERTY(f.name, 'SpaceUsed')) / 128.0 AS DECIMAL(18,1)) AS free_mb,
CASE WHEN f.is_percent_growth = 1 THEN CAST(f.growth AS VARCHAR(10)) + '%'
ELSE CAST(f.growth / 128 AS VARCHAR(10)) + ' MB' END AS growth_setting
FROM sys.database_files f;Verify in a staging environment prior to running in production. This is a read-only diagnostic query; it does not acquire exclusive locks.
- This script is designed for read-only diagnostics; it does not alter schema or modify data directly.
- Prior to executing on production systems under high load, verify the target database version (Min. Version: 2008).
- Exercise caution with mutating actions (e.g., terminating sessions or dropping indexes) referenced in the notes section.
Other SQLSERVER Diagnostic Scripts
View All (100)SQL Server Running Requests and Blocking Chain
Shows active requests, wait types, blocking sessions and the running statement together. The first diagnostic query to run during a slowdown.
Top CPU-Consuming Queries in SQL Server
Lists the top 20 queries by total and average CPU time from the plan cache. Pinpoints the source of CPU pressure at query level.
SQL Server Missing Index Recommendations (Missing Index DMVs)
Ranks the optimizer's missing index suggestions by estimated benefit. Merge them with existing indexes before applying.
SQL Server Index Fragmentation with Rebuild/Reorganize Advice
Measures fragmentation on large indexes and recommends rebuild or reorganize. Lets you plan maintenance windows without wasted work.