How to get database complete size details
We can use below script to get complete details of SQL database
use master
go
BEGIN
SET nocount
ON
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('tempdb..#DBFileInfo')) BEGIN DROP TABLE
#dbfileinfo END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#LogSizeStats')) BEGIN DROP TABLE
#logsizestats END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#DataFileStats')) BEGIN DROP TABLE
#datafilestats END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#FixedDrives')) BEGIN DROP TABLE
#fixeddrives END
CREATE TABLE #fixeddrives ( DriveLetter VARCHAR(10), MB_Free DEC(20, 2) )
CREATE TABLE #datafilestats ( DBName VARCHAR(255), DBId INT, FileId TINYINT, [FileGroup] TINYINT, TotalExtents DEC(20, 2), UsedExtents
DEC(20, 2), [Name] VARCHAR(255), [FileName] VARCHAR(400) )
CREATE TABLE #logsizestats ( DBName VARCHAR(255) NOT NULL PRIMARY KEY CLUSTERED, DBId INT, LogFile REAL, LogFileUsed REAL, Status BIT )
CREATE TABLE #dbfileinfo ( [ServerName] VARCHAR(255), [DBName] VARCHAR(65), [LogicalFileName] VARCHAR(400), [UsageType] VARCHAR (30), [Size_MB] DEC(20, 2),
[SpaceUsed_MB] DEC(20, 2),
[MaxSize_MB] DEC(20, 2), [NextAllocation_MB] DEC(20, 2),
[GrowthType] VARCHAR(65), [FileId] SMALLINT, [GroupId] SMALLINT, [PhysicalFileName] VARCHAR(400), [DateChecked] DATETIME )
DECLARE
@SQLString VARCHAR(3000) DECLARE @MinId INT DECLARE
@MaxId INT
DECLARE @DBName VARCHAR(255) DECLARE
@tblDBName TABLE ( RowId INT IDENTITY(1, 1), DBName VARCHAR(255), DBId INT)
INSERT INTO @tblDBName (DBName, DBId) SELECT [Name], DBId FROM master..sysdatabases WHERE ( Status & 512 ) = 0 /*NOT IN
(536,528,540,2584,1536,512,4194841)*/
ORDER BY
[Name]
INSERT INTO #logsizestats (DBName, LogFile, LogFileUsed, Status) EXEC ('DBCC sqlperf(logspace) WITH
no_infomsgs')
UPDATE
#logsizestats SET
DBId = Db_id(DBName)
INSERT INTO #fixeddrives
EXEC master..Xp_fixeddrives
SELECT @MinId = Min(RowId), @MaxId = Max(RowId) FROM
@tblDBName
WHILE ( @MinId <= @MaxId
) BEGIN SELECT @DBName =
[DBName] FROM
@tblDBName WHERE
RowId = @MinId
SELECT
@SQLString = 'SELECT
ServerName = @@SERVERNAME,' + ' DBName = '''
+
@DBName + ''',' + ' LogicalFileName = [name],' +
' UsageType = CASE WHEN (64&[status])=64 THEN
''Log'' ELSE ''Data'' END,' + ' Size_MB =
[size]*8/1024.00,' + ' SpaceUsed_MB =
NULL,' + '
MaxSize_MB = CASE [maxsize] WHEN -1 THEN -1 WHEN 0 THEN [size]*8/1024.00 ELSE
maxsize/1024.00*8 END,'+ ' NextExtent_MB = CASE WHEN (1048576&[status])=1048576
THEN ([growth]/100.00)*([size]*8/1024.00) WHEN [growth]=0 THEN 0 ELSE
[growth]*8/1024.00 END,'+ ' GrowthType = CASE WHEN (1048576&[status])=1048576 THEN
''%'' ELSE ''Pages'' END,'+ ' FileId = [fileid],' +
' GroupId = [groupid],' + ' PhysicalFileName=
[filename],' + ' CurTimeStamp =
GETDATE()' + 'FROM
[' + @DBName + ']..sysfiles'
PRINT
@SQLString
INSERT INTO #dbfileinfo
EXEC (@SQLString)
UPDATE
#dbfileinfo SET
SpaceUsed_MB = Size_MB /
100.0 * (SELECT
LogFileUsed FROM
#logsizestats WHERE
DBName = @DBName) WHERE
UsageType = 'Log' AND DBName = @DBName
SELECT
@SQLString = 'USE ['
+ @DBName + '] DBCC SHOWFILESTATS
WITH NO_INFOMSGS'
INSERT
#datafilestats (FileId, [FileGroup], TotalExtents, UsedExtents, [Name], [FileName]) EXECUTE(@SQLString)
UPDATE
#dbfileinfo SET
[SpaceUsed_MB] = S.[UsedExtents]
* 64 /
1024.00 FROM
#dbfileinfo AS F INNER JOIN #datafilestats AS
S ON F.[FileId] = S.[FileId] AND F.[GroupId] = S.[FileGroup] AND F.[DBName] = @DBName
TRUNCATE TABLE #datafilestats
SELECT @MinId = @MinId + 1 END
SELECT
[ServerName],
[DBName], [LogicalFileName],
[UsageType] AS
SegmentName,
B.MB_Free AS
FreeSpaceInDrive, [Size_MB], [SpaceUsed_MB], [Size_MB] -
[SpaceUsed_MB] AS
FreeSpace_MB,
Cast((
[Size_MB] - [SpaceUsed_MB] ) / [Size_MB] AS DECIMAL(4, 2)) AS
FreeSpace_Pct, [MaxSize_MB], [NextAllocation_MB], ( [Size_MB] -
[SpaceUsed_MB] ) - (
[NextAllocation_MB] ) AS
alert_switch, ( B.MB_Free ) + ( ( [Size_MB] -
[SpaceUsed_MB] ) - ( [NextAllocation_MB] )
) AS will_be_on_drive, CASE
MaxSize_MB WHEN
-1 THEN Cast(Cast(( [NextAllocation_MB]
/ [Size_MB] ) * 100 AS INT ) AS VARCHAR(10)) + ' %' ELSE 'Pages' END AS
[GrowthType],
[FileId], [GroupId], [PhysicalFileName], CONVERT(SYSNAME, Databasepropertyex([DBName], 'Status')) AS Status, CONVERT(SYSNAME, Databasepropertyex([DBName], 'Updateability')) AS
Updateability,
CONVERT(SYSNAME, Databasepropertyex([DBName], 'Recovery')) AS RecoveryMode, CONVERT(SYSNAME, Databasepropertyex([DBName], 'UserAccess')) AS UserAccess, CONVERT(SYSNAME, Databasepropertyex([DBName], 'Version')) AS Version,
[DateChecked] FROM #dbfileinfo AS
A LEFT JOIN #fixeddrives AS
B ON Substring(A.PhysicalFileName, 1, 1) = B.DriveLetter ORDER BY ( [Size_MB] - [SpaceUsed_MB] ) - ( [NextAllocation_MB] )
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#DBFileInfo')) BEGIN DROP TABLE
#dbfileinfo END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#LogSizeStats')) BEGIN DROP TABLE
#logsizestats END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#DataFileStats')) BEGIN DROP TABLE
#datafilestats END
IF EXISTS (SELECT 1 FROM tempdb..sysobjects WHERE [Id] = Object_id('Tempdb..#FixedDrives')) BEGIN DROP TABLE
#fixeddrives END
SET nocount OFF END
Below is the query results screen shot captured from the server
This script is used and tested in 2012 server
