Monday 23 May 2016

Get sizes of all databases on a server

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.

Popular Posts