You are here:Home » SQL Server » Script to get Database Files detail - SQL Server

Script to get Database Files detail - SQL Server

The DBA needs to perform some day to day activities to monitor SQL servers and databases as everything is OK or not. Some of the tasks are based on daily, some are on weekly and some are on a monthly. But database activities and monitoring and its details are important because databases are growing day to day and accordingly we need to check out the disk space.

For that we should have all the database details and statistics like database file current size,growth and maximum size.We can do check the databases properties and file size for each of them manually. We should have the script to check it for all or specific databases Which can ease to get the details and monitoring with same. Here I will write a simple script which will give the specific database files and information for the same. Let us drive with it, here we will create one database and add one filegroup and secondary files in that.
 
USE MASTER
GO

-- Creating database 
CREATE DATABASE DatabASeFileDetails
GO

-- Adding a new filegroup to database 
ALTER DATABASE DatabASeFileDetails
ADD FILEGROUP NewFileGroup;
GO

--Adding a new secondary and log files to a database to above created filegroup
ALTER DATABASE DatabASeFileDetails 
ADD FILE 
(
    -- New secondary files added here
    NAME = FileGroupDB_Data_1,
    FILENAME = 'C:\DatabASeFileDetails_Data_1.ndf',
    SIZE = 15 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 5 MB
),
(
    -- New log file added here
    NAME = FileGroupDB_Log_1,
    FILENAME = 'C:\DatabASeFileDetails_Log_1.ldf',
    SIZE = 5 MB,
    MAXSIZE = 100 MB,
    FILEGROWTH = 5 MB
)
TO FILEGROUP NewFileGroup; -- Defining filegroup name here
GO
Now we have created databases and done with new filegroup and database files. We have turned to run the script which we actually want, which returns with database details.
 
-- Using that database
USE DATABASEFILEDETAILS
GO

SELECT  
  DB_NAME(DBID) AS DatabaseName, 
  Name AS LogicalFileName,
  CASE 
    WHEN FILEID = 1 
     THEN 'Primary File'
       WHEN FILEID = 2 THEN 'Log File'
     ELSE 'Secondary File' 
  END AS FileDescription,
  FILEGROUP_NAME(groupid) AS FileGroup,
  CAST( ((CAST (SIZE AS NUMERIC(18,2) )*8)/1024) AS NUMERIC(18,2)) AS [FileSize(MB)],
  CASE status  
    WHEN 0 
     THEN  'No growth'
    WHEN 2 
     THEN CAST(CAST(((CAST (growth AS INT )*8)/1024) AS INT) AS VARCHAR(1000)) + ' [growth(MB)]'
    ELSE CAST(growth AS VARCHAR(1000)) + ' [growth(%)]'
  END AS Growth,
  CASE maxsize
    WHEN 0 
      THEN 'No growth'
    WHEN -1 
      THEN 'File will grow until the disk is full'
    ELSE CAST(CAST(((CAST (maxsize AS NUMERIC(18,2) )*8)/1024) AS NUMERIC(18,2)) AS VARCHAR(1000))
  END AS [MaxFileSize(MB)],
  FileName AS PhysicalFilePath
FROM  SYS.SYSALTFILES
WHERE DBID > 4
AND DB_NAME(DBID) = 'DatabASeFileDetails'
ORDER BY DBID,FileId 

GO

In the above snapshot we have compared database script results and database property, so we can have idea everything are ok and fine. What you are using? Please fill your comments if I missed something.