Post reply

Why is sys.master_files showing the size of RBS files as 0?

  • July 8, 2020 at 2:24 pm

    #3768417

    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

    #3768479

    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.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply