QUERY: GET SPACE USED/FREE in All Databases

  • Orlando Colamatteo

    SSC Guru

    Points: 182269

    For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"

    https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it

    __________________________________________________________________________________________________
    There are no special teachers of virtue, because virtue is taught by the whole community. --Plato

  • Jeff Moden

    SSC Guru

    Points: 996436

    Orlando Colamatteo (2/15/2016)


    For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"

    https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it%5B/quote%5D

    What really ticks me off about that CONNECT item (I've seen it before) is that it IS an internal sproc that IS used by MS somewhere in SQL Server (as you suggested, Maintenance Plans?). It's apparently been proven to skip items and that means it's broken but MS just keeps on using it. Heh... the same person that wrote sp_SpaceUsed (why on Earth would they have written it to handle just one table and still resort to RBAR?) must have written the sp_msforeach procs.

    Hat's off to Aaron for giving it the try with MS. I gave up on CONNECT a long time ago. IIRC, the built in numbers table or function request is a decade or so old and it's still open. I even posted about it to try to drum up support for it (got more than 200 additional votes for it) and nothing has happened with it. Erland Sommarskog must be fit to be tied.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • Ed Wagner

    SSC Guru

    Points: 286979

    Jeff Moden (2/15/2016)


    Orlando Colamatteo (2/15/2016)


    For someone who has made so much hay off helping others slay RBAR processing, I don't blame you one iota for not wanting to review something you have no opportunity of fixing. Just to add insult to injury for the guy who put his best foot forward to get the foreach db proc fixed, the connect item earned the dreaded "Closed as Won't Fix"

    https://connect.microsoft.com/SQLServer/feedback/details/752629/disable-sp-msforeachdb-by-default-or-fix-it%5B/quote%5D

    What really ticks me off about that CONNECT item (I've seen it before) is that it IS an internal sproc that IS used by MS somewhere in SQL Server (as you suggested, Maintenance Plans?). It's apparently been proven to skip items and that means it's broken but MS just keeps on using it. Heh... the same person that wrote sp_SpaceUsed (why on Earth would they have written it to handle just one table and still resort to RBAR?) must have written the sp_msforeach procs.

    Now that's something I'm going to investigate when I get home. In the end, MS is going to fix what they want to fix and ignore everything else, but I still want to know.

  • SQL_Elvis

    SSC-Addicted

    Points: 421

    Removed

  • usenet

    Right there with Babe

    Points: 749

    The last query posted by SQL_Elvis is actually wrong:
    CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT) UsedSpaceMB,
     should be

    CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,

  • Jeff Moden

    SSC Guru

    Points: 996436

    usenet - Wednesday, April 5, 2017 4:19 AM

    The last query posted by SQL_Elvis is actually wrong:
    CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT) UsedSpaceMB,
     should be

    CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,

    Good eye.  Hopefully, it not being caught until 9 months later means that no one has used it yet and hasn't been burned by the result... yet.

    @SQL_Elvis... if you're still around, can you edit the code?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • usenet

    Right there with Babe

    Points: 749

    actually, there is another "glitch" in the code:
    The column "DbName" should read logical_filename instead, since it is definitely not the database name.
    I've made some adjustments to the code and added bringing in the growth settings, since I usually need both information at the same time. Revised script is:


    DECLARE @DBName varchar(300)
    DECLARE @SQL varchar(max)

    SELECT @SQL =
    'DECLARE @TABLE TABLE
    ( DBName varchar(255),
     LogicalFileName VARCHAR(255),
     Filelocation VARCHAR(255),
     CurrentSizeMB DECIMAL(20,2),
     UsedSpaceMB  DECIMAL(20,2),
     FreeSpaceMB DECIMAL(20,2),
     State tinyint,
     max_sizeMB int,
     GrowthMB_or_Percent int,
     is_percent_growth bit,
     can_grow bit,
     is_readonly bit,
     is_media_read_only bit
    ) ' + CHAR(13)
    EXECUTE(@SQL)
    Declare mycursor CURSOR for
    select name from SYS.databases s
    order by s.name
    open mycursor
    fetch next from mycursor into @DBName
    while (@@FETCH_STATUS <> -1)
    BEGIN
    SELECT @SQL = @SQL +
    'USE [' +@DBName+']
     INSERT INTO @TABLE
     SELECT DB_Name() as DBName,
     name AS LogicalFileName,
    physical_name Filelocation,
    size/128.0 AS CurrentSizeMB,
    CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,
    size/128.0 - CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB ,
    state ,
    case when (max_size = -1 or max_size=0) then max_size else max_size / 128 END,
    case when is_percent_growth = 1 then growth else growth / 128 end,
    is_percent_growth, case when growth=0 then 0 else 1 end, is_read_only, is_media_read_only
    FROM sys.database_files; '  + CHAR(13)
    fetch next from mycursor into @dbname
    END
    CLOSE mycursor
    DEALLOCATE mycursor
    SELECT @SQL = @SQL + ' SELECT * FROM @TABLE ORDER BY LogicalFileName ;'
    EXECUTE(@SQL)

  • Jeff Moden

    SSC Guru

    Points: 996436

    usenet - Wednesday, April 5, 2017 7:43 AM

    actually, there is another "glitch" in the code:
    The column "DbName" should read logical_filename instead, since it is definitely not the database name.
    I've made some adjustments to the code and added bringing in the growth settings, since I usually need both information at the same time. Revised script is:


    DECLARE @DBName varchar(300)
    DECLARE @SQL varchar(max)

    SELECT @SQL =
    'DECLARE @TABLE TABLE
    ( DBName varchar(255),
     LogicalFileName VARCHAR(255),
     Filelocation VARCHAR(255),
     CurrentSizeMB DECIMAL(20,2),
     UsedSpaceMB  DECIMAL(20,2),
     FreeSpaceMB DECIMAL(20,2),
     State tinyint,
     max_sizeMB int,
     GrowthMB_or_Percent int,
     is_percent_growth bit,
     can_grow bit,
     is_readonly bit,
     is_media_read_only bit
    ) ' + CHAR(13)
    EXECUTE(@SQL)
    Declare mycursor CURSOR for
    select name from SYS.databases s
    order by s.name
    open mycursor
    fetch next from mycursor into @DBName
    while (@@FETCH_STATUS <> -1)
    BEGIN
    SELECT @SQL = @SQL +
    'USE [' +@DBName+']
     INSERT INTO @TABLE
     SELECT DB_Name() as DBName,
     name AS LogicalFileName,
    physical_name Filelocation,
    size/128.0 AS CurrentSizeMB,
    CAST(FILEPROPERTY(name,''SpaceUsed'')/128 AS INT) UsedSpaceMB,
    size/128.0 - CAST(FILEPROPERTY(name,''SpaceUsed'') AS INT)/128.0 AS FreeSpaceMB ,
    state ,
    case when (max_size = -1 or max_size=0) then max_size else max_size / 128 END,
    case when is_percent_growth = 1 then growth else growth / 128 end,
    is_percent_growth, case when growth=0 then 0 else 1 end, is_read_only, is_media_read_only
    FROM sys.database_files; '  + CHAR(13)
    fetch next from mycursor into @dbname
    END
    CLOSE mycursor
    DEALLOCATE mycursor
    SELECT @SQL = @SQL + ' SELECT * FROM @TABLE ORDER BY LogicalFileName ;'
    EXECUTE(@SQL)

    Wouldn't it be easier just to add some functions to a call on sys.Master_Files?  No cursor required. 😉

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • usenet

    Right there with Babe

    Points: 749

    Jeff, i tend to disagree.
    If you want to get the actual usage information (via fileproperty or sp_spaceused) is only available for the current database. So you need to loop through all databases one way or another and get the connection context into that db.
    that's why i use this script.
    if there is another way to do it without a cursor, i am all ears - never too old to learn a new trick!

  • bjorn.c.w

    Valued Member

    Points: 71

    Also, 
    you can add this to the select-statement for the cursor:

    select name from SYS.databases s WHERE state = 0

    The script will fail if it hits on databases that is put Offline for a reason.

  • Jeff Moden

    SSC Guru

    Points: 996436

    usenet - Wednesday, April 5, 2017 9:01 AM

    Jeff, i tend to disagree.
    If you want to get the actual usage information (via fileproperty or sp_spaceused) is only available for the current database. So you need to loop through all databases one way or another and get the connection context into that db.
    that's why i use this script.
    if there is another way to do it without a cursor, i am all ears - never too old to learn a new trick!

    Apologies... I didn't see the Used/Free space information in your code before.  I do now.  But that does bring up another question... what do you actually do with that information?  Hopefully, you don't use it to justify a shrink of any data files.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • usenet

    Right there with Babe

    Points: 749

    No, of course not. This information is part of my daily run of checks on 50+ SQL Instances, goes into a dashboard and triggers proactive growth of data files when they near fullness.
    Or, when databases grow too fast too often, they get migrated to another system. We have instances that are cheaper (mostly for legacy or low frequency usage), others are more expensive (Enterprise Edition, multi node AlwaysOn etc). So we try to have the databases with easy load on the cheaper ones, unless the patterns change. And the actual data usage is one small part of the pattern

  • Jeff Moden

    SSC Guru

    Points: 996436

    usenet - Friday, September 8, 2017 10:06 AM

    No, of course not. This information is part of my daily run of checks on 50+ SQL Instances, goes into a dashboard and triggers proactive growth of data files when they near fullness.
    Or, when databases grow too fast too often, they get migrated to another system. We have instances that are cheaper (mostly for legacy or low frequency usage), others are more expensive (Enterprise Edition, multi node AlwaysOn etc). So we try to have the databases with easy load on the cheaper ones, unless the patterns change. And the actual data usage is one small part of the pattern

    Heh... sorry... Had to ask that question.  A lot of people do such crazy things.

    Very good into on what you use the information for.  Thanks for taking the time to post it.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Forum FAQ

  • usenet

    Right there with Babe

    Points: 749

    Yes, I've seen too many instances where the accidental DBA was shrinking data files every night.

    Always fun to see their faces when you come in, see that, rebuild all indexes and then ask them "show me your performance issue again, please".

    About the actual free / used data, that information is worth a lot in terms of capacity planning. Since I track this for every DB across the clients, it's easy to predict how much storage a new server will need, based on x years of past data for the DBs 🙂

    Only reliable to a certain degree, but so far never let me down - with the notable exception of Sharepoint databases, which are a completely different beast

    And while I have your attention: I'd like to take the time to say a heartfelt THANK YOU to you for giving so much here and on your blog. You are doing a great job serving the community with your knowledge and experience!

    Best regards,

    Andy

  • Squatt Gmail

    SSC-Addicted

    Points: 422

    And to add to the warnings about sp_msforeachdb or sp_msforeachtable, if you work with silly people that have used Reserved Names for objects, well... you can guess the rest.

    JSC

Viewing 15 posts - 16 through 30 (of 30 total)

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