SQL SERVER MONITORING: SQL Query Database 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),  --freesizeMB = (totalsizeMB-usedsizeMB), percentused = (usedsizeMB /totalsizeMB)*100,  percentfree = ((totalsizeMB-usedsizeMB)/totalsizeMB)*100; select * from #database_info; drop table #database_info;

Here is the T-SQL which you can use to run on any version of SQL Server to fetch the result.

USE MASTER Go SELECT @@SERVERNAME Servername,@@VERSION SQLVersion, CONVERT(VARCHAR(25), DB.name) AS dbName, CONVERT(VARCHAR(25), suser_sname(sid)) AS dbOwner, filename AS FileName, CONVERT(VARCHAR(10), DATABASEPROPERTYEX(name, 'status')) AS [Status], (SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid !=0 ) AS DataFiles, (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0) AS [Data MB], (SELECT COUNT(1) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS LogFiles, (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) AS [Log MB], (SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid!=0)+(SELECT SUM((size*8)/1024) FROM sysaltfiles WHERE DB_NAME(dbid) = DB.name AND groupid=0) TotalSizeMB, convert(sysname,DatabasePropertyEx(name,'Updateability')) Updateability, convert(sysname,DatabasePropertyEx(name,'UserAccess')) UserAccess , convert(sysname,DatabasePropertyEx(name,'Recovery')) RecoveryModel , convert(sysname,DatabasePropertyEx(name,'Version')) Version , CASE cmptlevel WHEN 60 THEN '60 (SQL Server 6.0)' WHEN 65 THEN '65 (SQL Server 6.5)' WHEN 70 THEN '70 (SQL Server 7.0)' WHEN 80 THEN '80 (SQL Server 2000)' WHEN 90 THEN '90 (SQL Server 2005)' WHEN 100 THEN '100 (SQL Server 2008)' END AS [compatibility level], CONVERT(VARCHAR(20), crdate, 103) + ' ' + CONVERT(VARCHAR(20), crdate, 108) AS [Creation date], ISNULL((SELECT TOP 1 CASE TYPE WHEN 'D' THEN 'Full' WHEN 'I' THEN 'Differential' WHEN 'L' THEN 'Transaction log' END + ' – ' + LTRIM(ISNULL(STR(ABS(DATEDIFF(DAY, GETDATE(),Backup_finish_date))) + ' days ago', 'NEVER')) + ' – ' + CONVERT(VARCHAR(20), backup_start_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_start_date, 108) + ' – ' + CONVERT(VARCHAR(20), backup_finish_date, 103) + ' ' + CONVERT(VARCHAR(20), backup_finish_date, 108) + ' (' + CAST(DATEDIFF(second, BK.backup_start_date, BK.backup_finish_date) AS VARCHAR(4)) + ' '+ 'seconds)' FROM msdb.dbo.backupset BK WHERE BK.database_name = DB.name ORDER BY backup_set_id DESC),'-') AS [Last backup] FROM sysdatabases DB ORDER BY dbName, [Last backup] DESC, NAME

OUTPUT:

Method :2