Post reply

Problem with query result

  • March 12, 2009 at 5:01 am

    #132237

    I need to get the number of databases on one instance with Total Sapce Used, Total Space Alocated and Total Free Space

    The point is that in the second query result Q2: the result of the number of databases appear completely out of the reality 😀

    Can anyone explain me what am i doing wrong.

    Sorry :sick:

    Thanks and regards,

    JMSM 😉

    Q1: select count(name) from sys.sysdatabases

    result:

    89

    Q2: select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'

    from db_name..tbl_name a, sys.sysdatabases b

    result:

    INamXYZ[font="Arial Black"]7921[/font]404052882307547517329813

    Q3: select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance'

    from sys.sysdatabases

    result:

    INamXYZ 89

  • March 12, 2009 at 5:35 am

    #958196

    use sp_helpdb

  • March 12, 2009 at 5:48 am

    #958213

    select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'

    from db_name..tbl_name a, sys.sysdatabases b

    Your count is high because your are joining with whatever db_name..tbl_name is producing 7921 records.

    quick fix, run two queries:

    select @@servername as 'instance name', (select count(name) from sys.sysdatabases) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'

    from db_name..tbl_name a, sys.sysdatabases b

  • March 12, 2009 at 1:46 pm

    #958690

    Thanks a lot Jamie (everybody). Your answer was very useful to my work.

    Regards,

    JMSM 😉

  • March 12, 2009 at 2:02 pm

    #958708

    JMSM (3/12/2009)

    Q2: select @@servername as 'instance name', count(*) as 'Nº of DBs by Instance', sum(a.size) as 'espaço alocado (mb)', sum(a.used) as 'espaço utilizado (mb)', sum(a.size)-sum(a.used) as 'espaço livre (mb)'

    from db_name..tbl_name a, sys.sysdatabases b

    You've got a cross join (Cartesian product) there. Each row of db_name..tbl_name is going to match to each and every row on sysdatabases.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 5 posts - 1 through 4 (of 4 total)

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