Automated Monitoring Database Size Using sp

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss Content Posted by David Poole » Automated Monitoring Database Size Using...

21 posts, Page 2 of 3

««1 2 3 »»

Automated Monitoring Database Size Using sp_spaceused

Rate Topic

Display Mode

Topic Options

Author

Message

David.Poole

Posted Wednesday, June 06, 2007 4:54 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 PM
Points: 2,749, Visits: 1,405

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

Post #371855

bwaller18

Posted Thursday, December 06, 2007 7:41 AM

Forum 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

Dexter Jones

Posted Thursday, December 06, 2007 3:16 PM

Valued Member

Group: General Forum Members
Last Login: Thursday, February 14, 2013 1:56 PM
Points: 52, Visits: 265

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

Konrad Willmert

Posted Wednesday, January 02, 2008 9:41 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, November 02, 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

Bekir-212336

Posted Wednesday, June 17, 2009 9:57 AM

Forum Newbie

Group: General Forum Members
Last Login: Thursday, June 07, 2012 7:26 AM
Points: 3, Visits: 36

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

JC-3113

Posted Monday, July 27, 2009 3:00 PM

SSC-Addicted

Group: General Forum Members
Last Login: Thursday, May 16, 2013 7:44 PM
Points: 442, Visits: 619

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

LoveSQL

Posted Thursday, April 08, 2010 5:12 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, February 12, 2013 10:54 AM
Points: 137, Visits: 116

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

ikinal 11406

Posted Friday, August 12, 2011 2:04 PM

Forum Newbie

Group: General Forum Members
Last Login: Tuesday, May 08, 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

David.Poole

Posted Friday, August 12, 2011 3:14 PM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 PM
Points: 2,749, Visits: 1,405

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

Post #1159559

David.Poole

Posted Sunday, August 14, 2011 9:05 AM

SSCrazy

Group: General Forum Members
Last Login: Thursday, May 16, 2013 3:16 PM
Points: 2,749, Visits: 1,405

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

Post #1159740

« Prev Topic | Next Topic »

21 posts, Page 2 of 3

««1 2 3 »»

Permissions