How big are your SQL Server database files? Where are they?

How big are your SQL Server database files? Where are they?

This is another quick piece that I run when I take on a new project.  Not too extensive, it just returns some brief details regarding your database files, sizes and locations.  Run it for one database, or every db on the server.  You might also consider using this within an Agent job, to report the file state to your DBA Team, on a recurring basis.
 
IF OBJECT_ID('usp_DatabaseDetails','P') <> 0
 DROP PROCEDURE dbo.usp_DatabaseDetails
GOCREATE PROCEDURE dbo.usp_DatabaseDetails (
 @DBName VARCHAR(100) = NULL
 )
AS
 SET NOCOUNT ON;
/*
Returns database file/filegroup details for the given @DBName. Informational only, helps WHEN assessing new environment(s).

EXEC dbo.usp_DatabaseDetails @DBName = 'MINE' -- Details for the given @DBName
EXEC dbo.usp_DatabaseDetails @DBName = '*'    -- Details for all databases on the server
EXEC dbo.usp_DatabaseDetails @DBName = 'HELP' -- Ask for 'help' regarding the procedure function

Auth: Your Name
Date: XX/XX/XXX
*/
IF(@DBName IS NULL OR @DBName = 'HELP')
BEGIN    
    PRINT 'HELP for usp_DatabaseDetails'
     PRINT ''
     PRINT 'This procedure will return file/filegroup details for the given @DBName, in this format:'
     PRINT ''
     PRINT '1) Database Name'
     PRINT '2) Logical name for all the database files, mdf, ldf and ndf'
     PRINT '3) Physical SIZE of each files in megabytes (MB)'
     PRINT '4) Drive letter upon which each of file resides'
     PRINT '5) Filegroup type '
     PRINT '6) Filegroup location, with the full path'
   RETURN
END
IF(@DBName IS NULL)
BEGIN    
    SELECT
         DB_Name() [DatabaseName],
         SUBSTRING(name,1,50) [Logical Name],
         SIZE/128 [SIZE in MB],
         SUBSTRING(FileName,1,1) [Drive],
         CASE groupid WHEN 1 THEN 'Primary'
             WHEN 0 THEN 'Log' ELSE 'Secondary' END [FileGroup Type],
         SUBSTRING(FileName,1,255) [Location]
     FROM
         sysfiles
END

IF(@DBName IS NOT NULL AND @DBName <> '*')
BEGIN
    IF NOT EXISTS(SELECT name FROM master..sysdatabases WHERE name = @DBName)
     BEGIN
        IF(@DBName='HELP')
        BEGIN
            RETURN;
        END
 
        IF(@DBName <> 'HELP')
        BEGIN
           PRINT 'The given @DBName '''+LTRIM(@DBName)+''' is not a database on this server.'
           RETURN;
        END
    END
 
     EXEC('SELECT '''+@DBName+''' [DatabaseName], SUBSTRING(Name,1,50) [Logical Name], 
    SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]FROM '+@DBName+'..sysfiles')

END
IF(@DBName = '*')
BEGIN    
    EXEC sp_msforeachdb 'SELECT UPPER(''?'') [DatabaseName],SUBSTRING(Name,1,50)   
    [Logical Name],SIZE/128 [MB],SUBSTRING(FileName,1,1) [Drive],CASE groupid
    WHEN 1 THEN ''Primary''
    WHEN 0 THEN ''Log''
    ELSE ''Secondary''
    END [FileGroup Type],
    SUBSTRING(FileName,1,255) [Location]
FROM ?..sysfiles'  END
 

SET NOCOUNT OFF;

Query for SQL Server database file size

Query for SQL Server database file size

The third party tools are not owned by every organization but DBAs. In production environment; it is not recommended to let database physical file grow automatically. Then a certain question comes in the mind, how to manage the file when to add more space to the files; so that the production environment don’t stop. The below script is key tool to know the database file sizing and their growth. I am managing some production servers with the help of the same query, I am monitoring it proactively and making sure the databases never have the disk space issue.

DECLARE @p_SQL varchar(1000) – Create Table #database_info             (                         databaseid                                            smallint                        ,                         databasename                                     sysname                        ,                         filesequenceid                                     smallint                         ,                         filegroupname                                     varchar(200)               ,                         databasefilname                                  varchar(1000)             ,                         totalsizeMB                                          decimal(10,2)               ,                         usedsizeMB                                         decimal(10,2)                ,                         freesizeMB                                            decimal(10,2)               ,                         percentused                                         decimal(10,2)                ,                         percentfree                                           decimal(10,2)             ); – SET @p_SQL = ‘USE [?]; Insert #database_info (databaseid, databasename, filesequenceid, filegroupname, databasefilname, totalsizeMB, usedsizeMB) Select db_id(), db_name(), groupid, rtrim(name), filename, Cast(size/128.0 As Decimal(10,2)), Cast(Fileproperty(name, ”SpaceUsed”)/128.0 As Decimal(10,2)) From dbo.sysfiles Order By groupId Desc;’ – Exec sp_MSforeachdb @p_SQL – Update #database_info SET freesizeMB = totalsizeMB – usedsizeMB, percentused = (usedsizeMB/totalsizeMB)*100, percentfree = ((totalsizeMB-usedsizeMB)/totalsizeMB)*100; – select * from #database_info; drop table #database_info;

The result will look like: