DB Size Info from master db

DB Size Info from master db

Post by Charles Davi » Sun, 12 Dec 1999 04:00:00



Hi.

I have written a VB6 program that uses ADO to connect to over 60
different SQL Servers in order to collect info about all the db's on
each server to then produce a consolidated report of our enterprise.

I need DBName, Data Space Allocated (MB), Log Space Allocated (MB) and
Data Space Utilized (MB) for each DB on each of the Servers.

I need to collect this from both v6.5 and v7.0 Servers.

I cannot use SQLDMO against two different SQL versions. I tried to use
the Databases Collection, but my program would not work against v6.5
servers, only 7.0.

Instead, I would like to retrieve the required info by running a SELECT

the version of the Server I connect to.

I would need a separate SELECT for v6.5 as well as v7.0.

This program will run weekly in order to spot growth trends and other
such DBA.

My program is running well now against either version of the Servers,
but I am using sp_help to only get the DBNames on the servers.  I need
more info.

Can anyone provide me the two SELECT statements that will accurately
retrieve for me the info I need from both versions of SQL Server?

Other suggestions would be appreciated, of course.

Many thanks.

--Charles

 
 
 

DB Size Info from master db

Post by Tibor Karasz » Tue, 14 Dec 1999 04:00:00


Charles,

There's a proc on www.dbmaint.com which does a check if the space usage is
higher than a supplied value. You can use the code in that as a base. It
works on both 6.5 and 7.0...

--
Tibor Karaszi
MCDBA, MCSE, MCSD, MCT, SQL Server MVP
Cornerstone Sweden AB
Please reply to the newsgroup only, not by email.


> Hi.

> I have written a VB6 program that uses ADO to connect to over 60
> different SQL Servers in order to collect info about all the db's on
> each server to then produce a consolidated report of our enterprise.

> I need DBName, Data Space Allocated (MB), Log Space Allocated (MB) and
> Data Space Utilized (MB) for each DB on each of the Servers.

> I need to collect this from both v6.5 and v7.0 Servers.

> I cannot use SQLDMO against two different SQL versions. I tried to use
> the Databases Collection, but my program would not work against v6.5
> servers, only 7.0.

> Instead, I would like to retrieve the required info by running a SELECT

> the version of the Server I connect to.

> I would need a separate SELECT for v6.5 as well as v7.0.

> This program will run weekly in order to spot growth trends and other
> such DBA.

> My program is running well now against either version of the Servers,
> but I am using sp_help to only get the DBNames on the servers.  I need
> more info.

> Can anyone provide me the two SELECT statements that will accurately
> retrieve for me the info I need from both versions of SQL Server?

> Other suggestions would be appreciated, of course.

> Many thanks.

> --Charles


 
 
 

DB Size Info from master db

Post by Charles Davi » Wed, 15 Dec 1999 04:00:00


Thanks for the info, but, in the meantime, I found an article on the
Microsoft KnowledgeBase that told how to use SQLDMO against v6.5 or v7.0
servers from the same VB program, which was exactly what I wanted.

Now, I use the Databases Collection to get the sizing info I need.

Many thanks for your reply anyway.

--Charles


> Charles,

> There's a proc on www.dbmaint.com which does a check if the space usage is
> higher than a supplied value. You can use the code in that as a base. It
> works on both 6.5 and 7.0...

> --
> Tibor Karaszi
> MCDBA, MCSE, MCSD, MCT, SQL Server MVP
> Cornerstone Sweden AB
> Please reply to the newsgroup only, not by email.



> > Hi.

> > I have written a VB6 program that uses ADO to connect to over 60
> > different SQL Servers in order to collect info about all the db's on
> > each server to then produce a consolidated report of our enterprise.

> > I need DBName, Data Space Allocated (MB), Log Space Allocated (MB) and
> > Data Space Utilized (MB) for each DB on each of the Servers.

> > I need to collect this from both v6.5 and v7.0 Servers.

> > I cannot use SQLDMO against two different SQL versions. I tried to use
> > the Databases Collection, but my program would not work against v6.5
> > servers, only 7.0.

> > Instead, I would like to retrieve the required info by running a SELECT

> > the version of the Server I connect to.

> > I would need a separate SELECT for v6.5 as well as v7.0.

> > This program will run weekly in order to spot growth trends and other
> > such DBA.

> > My program is running well now against either version of the Servers,
> > but I am using sp_help to only get the DBNames on the servers.  I need
> > more info.

> > Can anyone provide me the two SELECT statements that will accurately
> > retrieve for me the info I need from both versions of SQL Server?

> > Other suggestions would be appreciated, of course.

> > Many thanks.

> > --Charles

 
 
 

1. Restore Master DB to db other than Master?

We would like to run some queries against a MS SQL (6.5, sp5a) master db
from an 'out-of-service' server.  We have a copy of the original master as
SQL tape
backup.  Can we restore this tape backup copy to a database - say one called
master restore - on another server with same version as original.  Or can
you restore a master db backup only to a db named master.

Thanks
John Klaas
Data Administration
Babson College
Babson Park, MA
voice: 781-239-4585

2. Error -2147467259 (80004005)

3. auto-collect info from a master DB

4. Problem in DB Maintenacne Plan - verify Backup takes 30720 hours to complete!!!!!!!!

5. SP5a master DB size

6. How to add a balance report use T-sql

7. To reduce Master DB Size

8. Powerbuilder ODBC drivers ?

9. Sp5a master DB size

10. SQL 6.5 master db size

11. Connecting to SQL Server through ODBC driver with master db as default db

12. master db probelm - cannont delete tables in the db

13. issue re: x-db ownership chaining and master db system tables