Post reply

Automated Monitoring Database Size Using sp_spaceused

  • July 27, 2009 at 3:00 pm

    #1030763

    Hi Folks

    can someone tell me if there is a way to just select certain attributes from sp_spaceused

    I am trying this out as an example:

    USE AdventureWorks;

    GO

    EXEC sp_spaceused @updateusage = N'True';

    GO

    it gives me al the columns and data

    but I would like to be able to just select the database_name,database_size, and maybe unused as an example

    is that possible or do I need to look somewher else to get that info

    Thanks

    Jim

  • April 8, 2010 at 5:12 pm

    #1148722

    Does this work on SQL 2008?

    I am getting the following error.

    Any help is appreciated.

    Thanks

    Msg 451, Level 16, State 1, Procedure sp_DBA_spaceused_AllTables2, Line 49

    Cannot resolve collation conflict for column 2 in GROUP BY statement.


    rb

  • August 12, 2011 at 2:04 pm

    #1368210

    When I try to run the code given, I get some results [for 3 dbs], but the following

    error for the remaining

    [

    [Microsoft][ODBC SQL Server Driver][SQL Server]A cursor with the name 'hCForEachDatabase' already exists.(42000,16915)

    Any ideas?

    Thanks,

    Ihor Kinal

    ikinal@ieee.org

  • August 12, 2011 at 3:14 pm

    #1368240

    the original article was written using SQL2005 but there appears to be subtle differences in SQL2008.

    I'll have a look into it ASAP.

    LinkedIn Profile

  • August 14, 2011 at 9:05 am

    #1368390

    The problem seems to be that the underlying objects for the query are system views.

    If you take an exact copy of sp_spaceused and create your own copy in the master database then it simply doesn't work outside of the context of the master database even though your code is identical!

    It seems that the original sp_spaceused, being on the sys schema treats other sys views as if they were local where as the bespoke sp_spaceused being on the dbo schema treats them as if they reside in the master database.

    Perhaps the best way of dealing with this issue is to capture the SQL thrown at the DB by the built in report "Disk Used By Top Tables" and put it in the MODEL database and all user databases.

    LinkedIn Profile

  • May 8, 2012 at 8:26 am

    #1484543

    The line:

    MIN(row_count) AS Rows,

    Returns incorrect results for partitioned tables.

    It shoudl read:

    SUM(row_count) as Rows,

    to correctly sum the rowcount across all partitions.

    As for the exclusion of XML data, does the subquery (LOBDATA) against sys.dm_partition_stats joined to sys.internal_tables not return this metric?

    Just curious...

Viewing 6 posts - 16 through 20 (of 20 total)

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