How to get database complete size details
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