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:
