Instance Size

 SELECT (SELECT SUM(size)*8/1024/1024  FROM sys.master_files  WHERE type=1) AS logGB,

        (SELECT SUM(size)*8/1024/1024  FROM sys.master_files  WHERE type=0) AS rowGB

Data Files

SELECT database_id, 

       name, 

       physical_name, 

       state_desc, 

       size, 

       max_size, 

       growth, 

       is_read_only, 

       is_percent_growth

  FROM sys.master_files

exec dbo.sp_msForEachDb @command1='SELECT * FROM ?.sys.database_files'

WITH fs AS (SELECT db_name(database_id)   AS DatabaseName,

                   type_desc              AS TypeDesc,

                   Physical_Name          AS Location,

                   (size * 8) /1024       AS SizeInMB,

                   (size * 8) /1024/1024  AS SizeInGB

              FROM sys.master_files

           )

SELECT name,

       (SELECT SUM(SizeInMB) FROM fs WHERE TypeDesc = 'ROWS' AND fs.DatabaseName = db.name) DataFileSizeMB,

       (SELECT SUM(SizeInGB) FROM fs WHERE TypeDesc = 'ROWS' AND fs.DatabaseName = db.name) DataFileSizeGB,

       (SELECT SUM(SizeInMB) FROM fs WHERE TypeDesc = 'Log'  AND fs.DatabaseName = db.name) LogFileSizeMB,

       (SELECT SUM(SizeInGB) FROM fs WHERE TypeDesc = 'Log'  AND fs.DatabaseName = db.name) LogFileSizeGB

  FROM sys.databases db

SELECT

    DB_NAME(database_id) AS database_name,

    type_desc,

    name AS logical_file_name,

    physical_name,

    CAST(size * 8.0 / 1024 AS decimal(18,2)) AS size_mb,

    CASE

        WHEN max_size = -1 THEN NULL

        ELSE CAST(max_size * 8.0 / 1024 AS decimal(18,2))

    END AS max_size_mb,

    CASE

        WHEN is_percent_growth = 1 THEN

            CAST(growth AS varchar(20)) + '%'

        ELSE

            CAST(CAST(growth * 8.0 / 1024 AS decimal(18,2))

                 AS varchar(30)) + ' MB'

    END AS filegrowth

FROM sys.master_files

WHERE physical_name LIKE N'F:\%'

ORDER BY size DESC;

GO

Data File Usage

Move Data Files to a new Folder

If your data files are in the wrong location, you can move them to another drive or folder on the same server using these steps...

ALTER DATABASE myDatabase  

    MODIFY FILE ( NAME = myDatabase_Data,  

                  FILENAME = 'E:\myNewDataFolder\myDatabase_Data.mdf');  

GO

ALTER DATABASE myDatabase 

    MODIFY FILE ( NAME = myDatabase_Log,  

                  FILENAME = 'F:\myNewLogFolder\myDatabase_Log.ldf');  

GO

*** TO BE COMPLETED ***

Bibliography