Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
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
You can’t perform that action at this time.