Sys.master_files system view is having the all information
about the database in the server. This view is under MSDB database.
The columns of interest for retrieving database size information are:
Column Name
|
Description
|
database_id
|
ID of the database to
which the file belongs to
|
type_desc
|
Description of file
type. It can be ROWS, LOG, FILESTREAM or FULLTEXT
|
size
|
File size in number
of 8KB pages
|
Using this information we can retrieve database sizes using below query:
Use below sql script to get the database size
SELECT [Database Name],
cast(DataFile as decimal(10,2)) AS [Data File(s)],
cast(LogFile as decimal(10,2)) AS [Log File(s)],
cast((DataFile +
LogFile) as decimal(10,2)) AS [Total
size(Data + Log)]
FROM (SELECT DB_NAME(Database_id) AS [Database Name],
size *
8.0 / 1024
AS SizeInMB,
CASE
WHEN
TYPE = 0 THEN 'DataFile'
ELSE
'LogFile'
END AS
FileType
FROM sys.master_files) D
PIVOT ( MAX(SizeInMB)
FOR
FileType IN (DataFile,
LogFile))
FinalTable
|
No comments:
Post a Comment
If you have any doubt, please let me know.