July 8, 2020 at 2:24 pm
Dear Everyone
I hope you are all safe in these strange times.
I am trying to find the size of the database datafiles, transaction log files and RBS files in my SharePoint database where filestreaming is enabled and I tried to run the query below but I get the size of the RBS files as 0.
Any ideas why?
SELECT
db.name AS DBName,
type_desc AS FileType,
mf.size* 8.0 / 1024 / 1024 AS 'Size In GB'
FROM
sys.master_files mf
INNER JOIN
sys.databases db ON db.database_id = mf.database_id
where db.name not in ('master','msdb','model','temp')
I can find the size in sys.database_files but nothing to join master_files with database_files
Any ideas?
Stay Safe
KY
July 8, 2020 at 4:09 pm
You can't dynamically join all <db name>.sys.database_files for all databases with master.sys.master_files. You can join for a particular db and easily get the sizes that way.
If you want to try and find a way to get this done regularly, you can loop through all databases and get this, which is cumbersome. As this isn't likely something that matters minute to minute, what I'd likely do here is build a process to loop through all databases, scan for the sizes, and then store this in some admin database with the db name/id. Then run your ad hoc query against that data. having this info once a day is likely fine.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply