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!
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