SQL Server- get all database files and space used on an instance. See https://natethedba.wordpress.com/tag/tsql2sday/ . Subject to updates and open to feedback!
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DECLARE @SummaryView BIT = 0 | |
DECLARE @GenFileRenames BIT = 0 | |
DECLARE @IgnoreSpecials BIT = 0 | |
--Only w/ @SummaryView = 0 && @GenFileRenames = 0 | |
DECLARE @LimitBySize bit = 0 | |
DECLARE @MinSizeGB decimal(10,2) = 5.0 | |
DECLARE @MinPctFree decimal(10,2) = 10.0 | |
--Customized view using GB answer w/ SO temp-table | |
CREATE TABLE #FileSizes ( | |
DatabaseName sysname | |
, LogicalName sysname | |
, FileID INT | |
, PhysicalName NVARCHAR(500) | |
, Size DECIMAL(10,2) | |
, UsedSpace DECIMAL(10,2) | |
, FreeSpace DECIMAL(10,2) | |
, PctUsed DECIMAL(10,2) | |
, PctFree DECIMAL(10,2) | |
, [FileGroup] VARCHAR(100) NULL | |
, GrowthSetting VARCHAR(100) NULL | |
, MaxSize VARCHAR(100) NULL | |
, RecoveryModel VARCHAR(100) NULL | |
) | |
EXEC sp_MSforeachdb 'Use [?]; | |
INSERT INTO #FileSizes (DatabaseName, LogicalName, FileID, PhysicalName, Size, UsedSpace, FreeSpace, PctUsed, PctFree, [FileGroup], GrowthSetting, MaxSize) | |
SELECT db_name() [DB Name], dbf.name AS [Logical Name], dbf.file_id [FileID], dbf.physical_name AS [Physical Name] | |
, CAST((dbf.size/128.0) / 1024.0 AS decimal(10,2)) AS [Total Size in GB] | |
, cast(cast(fileproperty(dbf.name, ''SpaceUsed'') AS int)/128.0/1024.0 AS decimal(10,2)) AS [Size Used in GB] | |
, cast((dbf.size/128.0 - cast(fileproperty(dbf.name, ''SpaceUsed'') AS int)/128.0) / 1024.0 AS decimal(10,2)) AS [Available Space In GB] | |
, cast((CASE WHEN dbf.size/128.0 > 0 | |
THEN cast(cast(fileproperty(dbf.name, ''SpaceUsed'') AS int)/128.0 AS decimal(10,2)) / cast(dbf.size/128.0 AS decimal(10,2)) * 100.0 | |
ELSE 0 END) AS decimal(10,2)) [PctUsed] | |
, cast((CASE WHEN dbf.size/128.0 > 0 | |
THEN (cast(dbf.size/128.0 AS decimal(10,2)) - cast(cast(fileproperty(dbf.name, ''SpaceUsed'') AS int)/128.0 AS decimal(10,2))) | |
/ cast(dbf.size/128.0 AS decimal(10,2)) * 100.0 | |
ELSE 0 END) AS decimal(10,2)) [PctFree] | |
, fgrp.name AS [FileGroup] | |
, (case when dbf.is_percent_growth = 1 then cast(dbf.growth as varchar) + '' %'' | |
else cast(cast(dbf.growth / 128.0 as decimal(10,2)) as varchar) + '' MB'' end) as [GrowthSetting] | |
, (case when dbf.max_size <= 0 then ''Unlimited'' else cast(cast(dbf.max_size / 128.0 / 1024.0 as decimal(10,2)) as varchar) end) as [MaxSize] | |
FROM sys.database_files AS dbf | |
LEFT JOIN sys.data_spaces AS fgrp | |
ON dbf.data_space_id = fgrp.data_space_id | |
ORDER BY [FileID] | |
' | |
UPDATE fs SET fs.RecoveryModel = dbs.recovery_model_desc | |
FROM #FileSizes fs | |
INNER JOIN [master].sys.databases dbs | |
ON fs.DatabaseName = dbs.name | |
WHERE fs.RecoveryModel IS NULL | |
IF (@IgnoreSpecials = 1) | |
DELETE FROM #FileSizes | |
WHERE DatabaseName IN ('DBA') --exclude DBA reserved DBs | |
OR DatabaseName LIKE 'Redgate%' --RedGate monitoring | |
OR DatabaseName LIKE 'Spotlight%' OR DatabaseName LIKE 'Foglight%' --Quest monitoring | |
OR DatabaseName LIKE 'dbhealth%' --Database Health Monitor | |
OR DatabaseName LIKE 'Sentry%' --SentryOne monitoring | |
IF (@SummaryView = 0) | |
BEGIN | |
IF (@GenFileRenames = 0) | |
SELECT * | |
FROM #FileSizes | |
WHERE (@LimitBySize = 1 AND Size >= @MinSizeGB AND PctFree >= @MinPctFree | |
OR @LimitBySize = 0) | |
--only LOG files | |
--AND FileID = 2 | |
--filter for a drive letter | |
--AND PhysicalName LIKE 'D:\%' | |
ORDER BY DatabaseName, FileID | |
--alternate sorts, if you want to play w/ it | |
--ORDER BY [Size] DESC, DatabaseName, FileID | |
--ORDER BY FreeSpace DESC, PctFree DESC, DatabaseName, FileID | |
ELSE | |
SELECT DatabaseName, LogicalName, FileID, PhysicalName, Size, UsedSpace, FreeSpace, PctUsed, PctFree, RecoveryModel | |
, [RenameLogicalFileCmd] = 'ALTER DATABASE ' + DatabaseName + ' MODIFY FILE (NAME = ''' + LogicalName + ''', NEWNAME = ''' + DatabaseName | |
+ (CASE WHEN FileID = 2 THEN '_log' ELSE '' END) + ''');' | |
FROM #FileSizes | |
WHERE DatabaseName NOT IN ('master', 'msdb', 'model', 'tempdb', 'distribution', 'ssisdb') --exclude system DBs | |
AND LogicalName NOT LIKE 'ftrow%' --exclude FullText catalog file | |
--check for physical filenames not matching pattern | |
--AND PhysicalName NOT LIKE '%'+DatabaseName+'.mdf' | |
-- AND PhysicalName NOT LIKE '%'+DatabaseName+'_log.ldf' | |
--check for logical file names not matching pattern | |
AND (LogicalName NOT LIKE ''+DatabaseName+'' | |
AND LogicalName NOT LIKE ''+DatabaseName+'_data' | |
AND LogicalName NOT LIKE ''+DatabaseName+'_log' | |
) | |
ORDER BY DatabaseName, FileID | |
END | |
ELSE | |
SELECT DatabaseName, SUM(Size) TotalSize, SUM(UsedSpace) TotalUsed, SUM(FreeSpace) TotalFree | |
, SUM(CASE WHEN FileID<>2 THEN Size ELSE 0 END) TotalDataSize, SUM(CASE WHEN FileID<>2 THEN UsedSpace ELSE 0 END) TotalDataUsed | |
, SUM(CASE WHEN FileID<>2 THEN FreeSpace ELSE 0 END) TotalDataFree | |
, SUM(CASE WHEN FileID=2 THEN Size ELSE 0 END) TotalLogSize, SUM(CASE WHEN FileID=2 THEN UsedSpace ELSE 0 END) TotalLogUsed | |
, SUM(CASE WHEN FileID=2 THEN FreeSpace ELSE 0 END) TotalLogFree | |
FROM #FileSizes | |
GROUP BY DatabaseName | |
ORDER BY DatabaseName | |
DROP TABLE #FileSizes |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment