extracting database sizes using sp_spaceused

Question

Post reply

extracting database sizes using sp_spaceused

susan.bartrum

Mr or Mrs. 500

Points: 534
More actions
June 26, 2008 at 6:18 am

#208990

Hello!
I am quite new to SQL Server and I have been trying all week to format or load the output of the database sizes when you run sp_spaceused (with no parameters).
I want to be able to store this information elsewhere so I can track database growth but I am unable to get it to be in a nice usable manner!
I have trawled through lots of sys views and I have not found anything usable yet.
I cannot believe it is not possible so it has got to be my newness to this that is the brick wall!
Can anyone help before drives me completely to distraction!:w00t:
Thank you
Sue

Viewing 11 posts - 1 through 10 (of 10 total)

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

jezemine SSCrazy Eights Points: 8059 More actions · Answer 1

if you create a table with the same columns as the result set of sp_spaceused, you can insert into it like this:

insert mytable exec sp_spaceused

---------------------------------------
elsasoft.org

susan.bartrum Mr or Mrs. 500 Points: 534 More actions · Answer 2

Thank you for replying so quickly and I thought it would be something like that but I am still having trouble, created a table and have tried what you suggest but I get the message :

Msg 213, Level 16, State 7, Procedure sp_spaceused, Line 113

Insert Error: Column name or number of supplied values does not match table definition.

This is what I have done :

create table #soo

(database_name varchar(max),

database_size varchar(max),

unallocated_space varchar(max),

reserved varchar(max),

data varchar(max),

index_size varchar(max),

unused varchar(max))

insert #soo exec sp_spaceused

jezemine SSCrazy Eights Points: 8059 More actions · Answer 3

oops, sorry. what I suggested won't work in this case because sp_spacused returns two result sets. you can't use insert/exec if the proc returns two result sets.

another option would be to look at the code for sp_spacused and write your own proc that does the same thing, but only returns *one* result set. then you *could* use insert/exec.

procs that return more than one result set are very lame, imo. 🙁

---------------------------------------
elsasoft.org

susan.bartrum Mr or Mrs. 500 Points: 534 More actions · Answer 4

Thank you, I have just extracted the code from sp_spaceused and will now spending a "happy" afternoon writing my own from it!

Luckily I think I have just found something in SQL ServerCentral that does roughly what I want, so I wont be completely in the dark.

:crazy:

MannySingh SSChampion Points: 12465 More actions · Answer 5

sp_helptext sp_spaceused

Then use the underlying Query Structure to Format your TABle Structure and Query

Maninder
www.dbanation.com

susan.bartrum Mr or Mrs. 500 Points: 534 More actions · Answer 6

Thank you.

Now looked at the code, so that will keep me out of trouble for a little while!

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 7

susan.bartrum (6/26/2008)
Thank you.
Now looked at the code, so that will keep me out of trouble for a little while!

If you want to stay out of trouble even longer, don't declare all the columns in a table as VARCHAR(MAX)... that will slow down any code you write against the table.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 8

Here is a script I wrote to collect the space for the drives and the databases.

http://www.mssqltips.com/tip.asp?tip=1510

susan.bartrum Mr or Mrs. 500 Points: 534 More actions · Answer 9

Thank you very much to both Jeff for the advice and Ken for a most brilliant script.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 10

susan.bartrum (6/26/2008)
I have trawled through lots of sys views and I have not found anything usable yet.

Another tip would be...

When you find a useful MS provided stored procedure such as Master.dbo.sp_SpaceUsed, go take a look and see where they get their information from. Notice I didn't say "how" to do something similar? I think sp_SpaceUsed is one of the worst written MS stored procedures there is and don't recommend it in any way, shape, or form as a good example of programming, technique, or documentation of a stored procedure... but it does have good information (if you study the code) as to where to get some of the information and how to condition that information for human consumption.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)