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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

-- 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.