Results 1 to 15 of 15
  1. #1
    Join Date
    May 2003
    Posts
    369

    Unanswered: T-SQL script to report space used for all databases

    Hi all,

    I need a good t-sql script that will create a space usage and size report for all databases on server. Does anyone have a good t-sql script or know where I can run a report to gather details on database size, table size, index size and usage report? I know how to use sp_space_use_db but not to run against 200 databases on server for sql server 2000. Thanks.

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    EXEC sp_msforeachdb @command1="use ? exec sp_spaceused"

  3. #3
    Join Date
    May 2003
    Posts
    369

    thanks one last question

    Works for all but one database with this error:

    Server: Msg 911, Level 16, State 1, Line 1
    Could not locate entry in sysdatabases for database 'IMD'. No entry found with that name. Make sure that the name is entered correctly.


    How can I fix this error to get database information for this one database?

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    is there in fact such a database called IMD on your system?

    also you could try this but I doubt it will fix it:

    EXEC sp_msforeachdb @command1="use [?] exec sp_spaceused"

  5. #5
    Join Date
    May 2003
    Posts
    369

    Resolved thanks!

    It worked and now I can see usage for all databases on server. Once again I appreciate the help. Most of my experience is on Oracle and DB2 UDB so this helps me with the SQL Server 2000 databases we have for our environment to do capacity planning.

    Cheers,
    Scott

  6. #6
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    ok, just be aware that sp_msforeachdb is an undocumented sproc. it's fine to use it for reports like this, but I wouldn't use it in any production code.

  7. #7
    Join Date
    May 2003
    Posts
    369

    Why does T-SQL show different values than Enterprise Manager?

    Hi,

    Well here is one interesting thing. The calculations reported by the
    sp_msforeachdb procedure show different values than those in the Enterprise Manager reports for some of the databases. Why is that? Is there a way to sync up the values reported from sp_msforeachdb and that shown in the Enterprise Manager taskpad for space usage? Here is an example:

    for the gosp database in Enterprise Manager taskpad it reports values of 16.75 MB size and 0.06MB free, but the sp_msforeachdb with sp_space_used_db script shows a negative value for space available. Which is more accurate and is there a way to fix the entry reported by the stored proc script?

  8. #8
    Join Date
    Jan 2009
    Posts
    1
    Try running dbcc updateusage(0), that should fix the -ve space allocation you see.

  9. #9
    Join Date
    Mar 2010
    Posts
    4
    how we can get output of following query in table so we can use it for capacity planning
    sp_msforeachdb with sp_space_used_db

  10. #10
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    define table after columns returned, and i.e.

    create table XXX
    ( name varchar(255),
    rows int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255)
    )


    insert into XXX
    exec master..sp_spaceused 'sysobjects'

  11. #11
    Join Date
    Mar 2010
    Posts
    4

    EXEC @command1="use [?] exec sp_spaceused"

    when I try to following query to add all the information in table so I can use table for capacity planning

    insert into DatabaseInformation
    EXEC @command1="use [?] exec sp_spaceused"

    I get following error

    Msg 137, Level 15, State 2, Line 2
    Must declare the scalar variable "@command1".

    Please help!

  12. #12
    Join Date
    Mar 2010
    Posts
    4

    capacity planning

    Quote Originally Posted by PMASchmed View Post
    define table after columns returned, and i.e.

    create table XXX
    ( name varchar(255),
    rows int,
    reserved varchar(255),
    data varchar(255),
    index_size varchar(255),
    unused varchar(255)
    )


    insert into XXX
    exec master..sp_spaceused 'sysobjects'
    I tried that query after creating table but keep getting message "The object 'sysobjects' does not exist in database 'master' or is invalid for this operation."

  13. #13
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I thought I've posted something similar awhile back, but couldn't find it. I am not sure this is the best approach, so someone may chime in later with a better way.
    Code:
    create table tempdb.dbo.t (
       serverName sysname null
      ,dbName sysname null
      ,name varchar(255)
      ,rows int not null
      ,reserved varchar(255) not null
      ,data varchar(255) not null
      ,index_size varchar(255) not null
      ,unused varchar(255) not null
      ,DateRun datetimeoffset not null default cast(current_timestamp as datetimeoffset)
    )
    go
    exec master.dbo.sp_msforeachdb '
       if db_id(''?'') > 4 begin
          use [?];
          declare @cmd varchar(max);
          declare c cursor local for
          select cmd = ''use [?]; exec sp_spaceused ''''''
           + schema_name(schema_id) + ''.''
           + name + '''''', true'' from sys.objects
             where objectproperty(object_id, ''IsUserTable'') = 1
          open c;
          fetch next from c into @cmd
          while @@fetch_status = 0 begin
             insert tempdb.dbo.t (name, rows, reserved, data, index_size, unused) exec (@cmd);
             fetch next from c into @cmd
          end;
          close c;
          deallocate c;
          update tempdb.dbo.t set serverName = @@servername, dbName = db_name()
             where serverName is null and dbName is null
       end
    '
    select * from tempdb.dbo.t
    go
    drop table t
    go
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  14. #14
    Join Date
    Mar 2010
    Posts
    4

    Error

    when I try to run above script, I get following error

    Msg 2715, Level 16, State 7, Line 1
    Column, parameter, or variable #10: Cannot find data type datetimeoffset.

  15. #15
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Do a search on datetimeoffset and replace it with datetime. This datatype is not available in versions prior to 2008.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •