Have you ever wanted or needed to get the data and log file size for all of the databases in your server?
I have seen scripts that would get this information in the past. All of the scripts seemed to use either a cursor or the proc from Microsoft called sp_MSforeachdb. Having seen some of these scripts, I wondered if there might be a different way of doing it (that was also different than you see in my TableSpace Scripts).
After putting some thought into it, I decided on a plan of attack and went to work on building a query that would do the trick. But before I continue, I will advise that running a script like this on large databases may take a while to execute.
Keeping these things in mind (potential slowness when run on large db and wanting to try something different), I came up with this method (tested on Instances with small databases as well as Instances with 2TB Databases).
[codesyntax lang=”tsql”]
Declare @MaxDataSizeInt
select @MaxDataSize = max(d.cntr_value)/1024/1024 --Convert to GB
from sys.dm_os_performance_counters d
where d.object_name like '%databases%'
and d.counter_name = 'Data File(s) Size (KB)'
Group By d.counter_name,d.object_name
Declare
@DbSize table (Database_Id int, DataUsedMB Float)
If @MaxDataSize > 250 --If Largest DB is >250GB then use forEachdb, else use Physical Index Stats
Begin
INSERT INTO @DbSize (Database_Id,DataUsedMB)
exec sp_MSforeachdb
'select db_id(''?'') , (SUM(ps.reserved_page_count)*8)/1024 from ?.sys.dm_db_partition_stats ps';
select
DB_ID(instance_name) Database_Id
, instance_name Database_Name
,cast(pvt.[Data File(s) Size (KB)] as float)/1024 as DataFileSizeMB
,isnull(ca.DataUsedMB,.1) as DataUsedMB
,(cast(pvt.[Data File(s) Size (KB)] as float)/1024) - isnull(ca.DataUsedMB,.1) as DataFreeMB
, case when pvt.[Data File(s) Size (KB)] = 0
Then 0
Else ROUND((((cast(pvt.[Data File(s) Size (KB)] as float)/1024) - isnull(ca.DataUsedMB,.1)) / (cast(pvt.[Data File(s) Size (KB)] as float)/1024))*100, 2)
End DataFreePercent
,cast(pvt.[Log File(s) Size (KB)] as float)/1024 as LogFileSizeMB
,cast(pvt.[Log File(s) Used Size (KB)] as float)/1024 as LogFileUsedMB
,(cast(pvt.[Log File(s) Size (KB)] as float) - pvt.[Log File(s) Used Size (KB)])/1024 LogFreeSizeMB
,pvt.[Percent Log Used] as LogUsedPercent
,100-pvt.[Percent Log Used] as LogFreePercent
from sys.dm_os_performance_counters d
PIVOT (SUM(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)],[Log File(s) Size (KB)],[Log File(s) Used Size (KB)],[Percent Log Used])) pvt
Left Outer Join (Select Database_Id,Null as page_count,DataUsedMB from @DbSize) CA
On (ca.database_id = DB_ID(instance_name))
where object_name like '%databases%'
and pvt.[Data File(s) Size (KB)] is not null
And pvt.[Log File(s) Size (KB)] is not null
And pvt.[Log File(s) Used Size (KB)] is not null
And pvt.[Percent Log Used] is not null
and instance_name <> '_Total'
Order By Database_ID
End
Else
Begin
select
DB_ID(instance_name) Database_Id
, instance_name Database_Name
,cast(pvt.[Data File(s) Size (KB)] as float)/1024 as DataFileSizeMB
,isnull(ca.DataUsedMB,.1) as DataUsedMB
,(cast(pvt.[Data File(s) Size (KB)] as float)/1024) - isnull(ca.DataUsedMB,.1) as DataFreeMB
, case when pvt.[Data File(s) Size (KB)] = 0
Then 0
Else ROUND((((cast(pvt.[Data File(s) Size (KB)] as float)/1024) - isnull(ca.DataUsedMB,.1)) / (cast(pvt.[Data File(s) Size (KB)] as float)/1024))*100, 2)
End DataFreePercent
,cast(pvt.[Log File(s) Size (KB)] as float)/1024 as LogFileSizeMB
,cast(pvt.[Log File(s) Used Size (KB)] as float)/1024 as LogFileUsedMB
,(cast(pvt.[Log File(s) Size (KB)] as float) - pvt.[Log File(s) Used Size (KB)])/1024 LogFreeSizeMB
,pvt.[Percent Log Used] as LogUsedPercent
,100-pvt.[Percent Log Used] as LogFreePercent
from sys.dm_os_performance_counters d
PIVOT (SUM(cntr_value) FOR counter_name IN ([Data File(s) Size (KB)],[Log File(s) Size (KB)],[Log File(s) Used Size (KB)],[Percent Log Used])) pvt
Left Outer Join (SELECT ips.database_id,SUM(page_count) AS page_count,Cast((SUM(page_count)*8) as float)/1024 AS DataUsedMB
FROM sys.dm_db_index_physical_stats(NULL,NULL,NULL,NULL,'sampled') ips
GROUP BY ips.database_id
) CA
On (ca.database_id = DB_ID(instance_name))
where object_name like '%databases%'
and pvt.[Data File(s) Size (KB)] is not null
And pvt.[Log File(s) Size (KB)] is not null
And pvt.[Log File(s) Used Size (KB)] is not null
And pvt.[Percent Log Used] is not null
and instance_name <> '_Total'
Order By Database_ID
End[/codesyntax]
You will see that I did not entirely eliminate the looping mechanism. Truth be told, it is so much faster on servers with Large Databases.
Also take note of the DMV that is in use in this query. I am taking advantage of the performance monitor stats that are exposed through the DMV sys.dm_os_performance_counters. One caveat to this method, is that this DMV shows us the size of the resource database as well. I think it is fine to report that information back – but it won’t change much over time. It is for that purpose that I use the Left Join in the query.
The other trick that I utilized is to Pivot that performance monitor data. I think this works better than to write a bunch of sub-queries to generate the same sort of data layout.
You have probably also noted that I have chosen 250GB as the tipping point in this query. There is no particular reason for that size – just a large enough database size to make the point. For some, the tipping point may be a much smaller database size. Feel free to change this value to suit your needs.
Some other caveats. The perfmon cntr_value data is presented as an Int. For more accurate math operations, I chose to cast many of these to a Float.
I also ran into a case where the perfmon data was reporting 0 as the Data File Size for one of my databases. I had to throw a case statement in for the Data File Used Percent in order to avoid a Divide by 0 error.
I also wrapped the DataUsedMB in an ISNULL. This was due to the model and mssqlsystemresource databases not having data in the dm_db_physical_stats function. I could have left those as NULL, but wanted to show something for them both.
Check it out and Enjoy!
