SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Automated Monitoring Database Size Using sp_spaceused


Automated Monitoring Database Size Using sp_spaceused

Author
Message
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7585 Visits: 3285
I'll look into it Marc. It should be possible but there is one thing I have found with SQL2005 and system tables. Basically you can't query them directly, you can only query the management views therefore coming up with a generic solution may not be possible.

Just to clarify in SQL2000 you have sysobjects as a system table. In SQL2005 you have sysobjects as a view on underlying tables.

LinkedIn Profile

Newbie on www.simple-talk.com
bwaller18
bwaller18
Grasshopper
Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)Grasshopper (12 reputation)

Group: General Forum Members
Points: 12 Visits: 75
I'd like to give this a try on my 2000 engines. Can someone please post the SQL2000 equivalent for sp_DBA_spaceused?
Alleged DBA
Alleged DBA
SSC-Enthusiastic
SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)SSC-Enthusiastic (109 reputation)

Group: General Forum Members
Points: 109 Visits: 368
Why doesn't Microsoft just provide this information in a DMV and be done with it?

Sure would make a few DBAs happy...



Konrad Willmert
Konrad Willmert
SSC Rookie
SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)SSC Rookie (31 reputation)

Group: General Forum Members
Points: 31 Visits: 41
What might it mean if I get negative numbers for the "Unused" column? Would having indexes on a separate filegroup affect these numbers?
Bekir-212336
Bekir-212336
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 39
what if we have partitioned tables Smile; row counts do not show up based on this script; I will work on getting that info to the script. Thanks David
JC-3113
JC-3113
Right there with Babe
Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)Right there with Babe (730 reputation)

Group: General Forum Members
Points: 730 Visits: 620
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
LoveSQL
SSC-Enthusiastic
SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)SSC-Enthusiastic (137 reputation)

Group: General Forum Members
Points: 137 Visits: 154
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
ikinal 11406
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 15
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
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7585 Visits: 3285
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

Newbie on www.simple-talk.com
David.Poole
David.Poole
SSCertifiable
SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)SSCertifiable (7.6K reputation)

Group: General Forum Members
Points: 7585 Visits: 3285
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

Newbie on www.simple-talk.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search