Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Automated Monitoring Database Size Using sp_spaceused Expand / Collapse
Author
Message
Posted Wednesday, June 6, 2007 4:54 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
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
Post #371855
Posted Thursday, December 6, 2007 7:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, March 10, 2011 3:53 PM
Points: 8, Visits: 50
I'd like to give this a try on my 2000 engines. Can someone please post the SQL2000 equivalent for sp_DBA_spaceused?
Post #430191
Posted Thursday, December 6, 2007 3:16 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Yesterday @ 9:55 AM
Points: 58, Visits: 302
Why doesn't Microsoft just provide this information in a DMV and be done with it?

Sure would make a few DBAs happy...



Post #430472
Posted Wednesday, January 2, 2008 9:41 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, November 2, 2011 12:49 PM
Points: 9, Visits: 40
What might it mean if I get negative numbers for the "Unused" column? Would having indexes on a separate filegroup affect these numbers?
Post #438038
Posted Wednesday, June 17, 2009 9:57 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, March 18, 2014 12:07 PM
Points: 3, Visits: 38
what if we have partitioned tables :); row counts do not show up based on this script; I will work on getting that info to the script. Thanks David
Post #736745
Posted Monday, July 27, 2009 3:00 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, May 22, 2013 8:43 AM
Points: 442, 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
Post #760378
Posted Thursday, April 8, 2010 5:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 27, 2014 1:57 PM
Points: 137, Visits: 124
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
Post #900075
Posted Friday, August 12, 2011 2:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, May 8, 2012 12:45 PM
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
Post #1159525
Posted Friday, August 12, 2011 3:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
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
Post #1159559
Posted Sunday, August 14, 2011 9:05 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, July 18, 2014 9:15 AM
Points: 2,889, Visits: 1,778
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
Post #1159740
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse