Using sys.master_files
catalog view we can get information about the size of database files.
For all databases, this view contains a row per file
of database.
We will try to get column database_id which is ID
of the database to which file belongs to, second column type_desc which is the description
of file type like ROWS, LOG, FILESTREAM or FULLTEXT, and third size which is the file
size in the number of 8kb pages.
We can retrieve database sizes using below query:
SELECT DB_NAME(database_id) AS [BD Name],
(CASE WHEN Type_Desc = 'ROWS' THEN 'Data File(s)'
WHEN
Type_Desc = 'LOG' THEN 'Log File(s)'
ELSE
Type_Desc END) AS [File Des] ,
CAST( ((SUM(Size)* 8) / 1024.0) AS DECIMAL(18,2) ) AS [Size in MB]
FROM sys.master_files
-- We can query for particular
database by uncomment following line
-- WHERE database_id = DB_ID('Database Name')
GROUP BY GROUPING
SETS
(
(DB_NAME(database_id), Type_Desc),
(DB_NAME(database_id))
)
ORDER BY DB_NAME(database_id), Type_Desc DESC
GO
|
No comments:
Post a Comment
Please do not enter any spam link in the comment box.