sp_spaceused

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Administration » sp_spaceused

sp_spaceused

Rate Topic

Display Mode

Topic Options

Author

Message

Henry_Lee

Posted Friday, October 24, 2008 5:54 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:14 AM
Points: 305, Visits: 1,316

Good day all,

I have a question about sp_spaceused, but I wonder if the question actually pertains to all system sp's.

I'm trying to get the size for all db's on the server (SS2000 SP4). This works fine: EXEC sp_msforeachdb 'USE ? EXEC sp_spaceused'.

However, I'd like to tweak sp_spaceused so it outputs on line line, so I can insert it into a table. I'd rather not alter the original proc, so I created a new one called sp_spaceused_modified. Before I made any changes to my new proc, I figured I'd test it: EXEC sp_msforeachdb 'USE ? EXEC sp_spaceused_modified'.

When I test it, the database size is reported incorrectly. Every other field is returned correctly - unallocated space, reserved, data, index_size, and unused. However, for each database on the server the size is reported as the size of the master db.

Again, I tested the new proc BEFORE I made any changes to it. I ran both from the master database in QA, they're both owned by dbo, I'm a sysadmin on the box. However, in EM I noticed the new proc I created is of type "User", while the original is of type "Sytem".

So, my question is this: Is there something inherent in that "type" designation that is causing the 2 procs to behave differently? If so, can I change that designation?

Thanks

Post #591112

Henry_Lee

Posted Friday, October 24, 2008 6:29 AM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 7:14 AM
Points: 305, Visits: 1,316

Ok, I found the answer in this post: http://weblogs.asp.net/anjanaram/archive/2005/05/03/405434.aspx. Thank you AnjanaRam for this post.

from the post:
"For other system tables , it really doesn't matter if the stored procedure gets listed as 'user' or 'system' type. We would only need to create the Stored Procedure in master database with a prefix as 'sp_' and it works. But in order to access the sysfiles table from the right database, we need to explicitly change it to 'system' type.

And this can be done by running the Stored procedure ' sp_MS_marksystemobject.'

Execute sp_MS_marksystemobject 'system stored proc name'"

Post #591139

« Prev Topic | Next Topic »

Permissions