Post reply

How to programatically determine all DB Drive usage

  • April 26, 2005 at 7:46 am

    #88881

    I have been asked to develop a spreadsheet that details exacly how much spaced each database uses in my company. I know I can manually go through them in Enterprise Manager, but seriously, I have over 30 SQL Servers and well over 1000 databases to document!

    Is there a way to write a script that will walk through each database on a server, and report the space usage for the databases and log files? Can I expand this to pass it a list of servers?

     

     

    Thanks,

    Clif

    "Give a lazy man a job, and he will find an easy way to do it!"

  • April 26, 2005 at 7:59 am

    #554879

    Here is a script on this site that can get you started:

    http://www.sqlservercentral.com/scripts/contributions/687.asp



    Michelle

  • April 26, 2005 at 8:53 am

    #554914

    The script in this example needs more documentation to fully understand it. Like, what is the sp sp_MSforeachdb?

  • April 26, 2005 at 8:59 am

    #554920

    It's basically an hidden cursor that you can use to execute a dynamic sql statement against each database

    run this to see what I mean :

    EXEC sp_MSforeachdb 'Select ''?'''

    it'll return 1 select statement for each database on the server with the dbname.

    this will return the number of objects in the database :

    EXEC sp_MSforeachdb 'Select ''?'' as DbName, count(*) as Objects from ?.dbo.SysObjects'

  • April 26, 2005 at 9:01 am

    #554921

    I forgot to mention that there's also another one like this :

    sp_msForEachTable that does pretty much the same thing but for all tables in the database. Those sps are however unsupported by Microsoft, which means that they won't help you if you need troubleshooting and that they might not be there in the next release of sql server.

  • April 27, 2005 at 5:32 am

    #555160

    Hi. Did you make the sps work? I have a different solution that I modified using a script oroginally done by a Mr. Eli Lieber. Its quite a bit of work but let me know so I can post it. Patrick.

  • April 27, 2005 at 5:41 am

    #555162

    Yes, I modified the script to use a temp table, but it worked fine. Thank you everyone for your help! I now showed my boss that we have 690 GB of databases!

  • April 27, 2005 at 8:36 am

    #555253

    'display all db sizes in mb for a server

    Option Explicit

    dim objDB

    with CreateObject("SQLDMO.SQLServer")

     .LoginSecure = TRUE

     .Connect "."

     for each objDB in .Databases

      wscript.echo objDB.Name & vbtab & objDB.Size

     next

    end with

Viewing 8 posts - 1 through 7 (of 7 total)

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