Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Problem with query result Expand / Collapse
Author
Message
Posted Thursday, March 12, 2009 5:01 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
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 :D

Can anyone explain me what am i doing wrong.
Sorry

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 7921 40405288 23075475 17329813



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

result:
INamXYZ 89
Post #674074
Posted Thursday, March 12, 2009 5:35 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 12, 2009 6:42 AM
Points: 28, Visits: 4
use sp_helpdb
Post #674093
Posted Thursday, March 12, 2009 5:48 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, October 15, 2010 8:23 AM
Points: 371, Visits: 437
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
Post #674111
Posted Thursday, March 12, 2009 1:46 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, May 6, 2014 10:29 AM
Points: 186, Visits: 811
Thanks a lot Jamie (everybody). Your answer was very useful to my work.

Regards,
JMSM ;)
Post #674666
Posted Thursday, March 12, 2009 2:02 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 11:41 AM
Points: 42,442, Visits: 35,496
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 2008, MVP
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

Post #674686
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse