MSSQL Database Files

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


DECLARE @DBInfo TABLE ( ServerName       VARCHAR(100),                         DatabaseName     VARCHAR(100),                         FileSizeMB       INT,                         LogicalFileName  sysname,                         PhysicalFileName NVARCHAR(520),                         Status           sysname,                         Updateability    sysname,                         RecoveryMode     sysname,                         FreeSpaceMB      INT,                         FreeSpacePct     VARCHAR(7),                         FreeSpacePages   INT,                         PollDate         datetime) 
DECLARE @command VARCHAR(5000) 
SELECT @command = 'Use [' + '?' + '] SELECT @@servername                                                                   AS ServerName,                                             ' + '''' + '?' + '''' + '                                                      AS DatabaseName,                                             CAST(sysfiles.size/128.0 AS int)                                               AS FileSize,                                             sysfiles.name                                                                  AS LogicalFileName,                                            sysfiles.filename                                                              AS PhysicalFileName,                                             CONVERT(sysname,DatabasePropertyEx(''?'',''Status''))                          AS Status,                                             CONVERT(sysname,DatabasePropertyEx(''?'',''Updateability''))                   AS Updateability,                                             CONVERT(sysname,DatabasePropertyEx(''?'',''Recovery''))                        AS RecoveryMode,                                             CAST(sysfiles.size/128.0 - CAST(FILEPROPERTY(sysfiles.name, ' + '''' +                                                 'SpaceUsed' + '''' + ' ) AS int)/128.0 AS int)                             AS FreeSpaceMB,                                             CAST(100 * (CAST (((sysfiles.size/128.0 -CAST(FILEPROPERTY(sysfiles.name,                                                 ' + '''' + 'SpaceUsed' + '''' + ' ) AS int)/128.0)/(sysfiles.size/128.0))                                                 AS decimal(4,2))) AS varchar(8)) + ' + '''' + '%' + '''' + '               AS FreeSpacePct,                                             GETDATE() AS PollDate                                       FROM dbo.sysfiles' INSERT INTO @DBInfo (ServerName,                      DatabaseName,                      FileSizeMB,                      LogicalFileName,                      PhysicalFileName,                      Status,                      Updateability,                      RecoveryMode,                      FreeSpaceMB,                      FreeSpacePct,                      PollDate) EXEC sp_MSForEachDB @command 
SELECT ServerName,        DatabaseName,        FileSizeMB,        LogicalFileName,        PhysicalFileName,        Status,        Updateability,        RecoveryMode,        FreeSpaceMB,        FreeSpacePct,        PollDate   FROM @DBInfo  ORDER BY ServerName,           DatabaseName 

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