Automated Monitoring Database Size Using sp_spaceused

Question

Post reply

Automated Monitoring Database Size Using sp_spaceused

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

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

JC-3113 SSCrazy Eights Points: 8371 More actions · Answer 1

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

LoveSQL Old Hand Points: 397 More actions · Answer 2

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

ikinal 11406 Newbie Points: 3 More actions · Answer 3

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

David.Poole SSC Guru Points: 76035 More actions · Answer 4

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

David.Poole SSC Guru Points: 76035 More actions · Answer 5

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

Mat Culpepper SSC Enthusiast Points: 138 More actions · Answer 6

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...