Enhanced sp_spaceused - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Enhanced sp_spaceused

Author

Topic

robvolk
Most Valuable Yak

15732 Posts

Posted - 2005-08-16 : 12:09:45

I can't remember if I posted this already or not:

CREATE PROCEDURE sp_space @sortbyrows bit=0 AS
SET NOCOUNT ON
select cast(object_name(id) as varchar(50)) AS name, sum(CASE WHEN indid<2 THEN rows END) AS rows,
sum(reserved)*8 AS reserved, sum(dpages)*8 AS data, sum(used-dpages)*8 AS index_size, sum(reserved-used)*8 AS unused
from sysindexes with (nolock) where indid in(0,1,255) and id>100
GROUP BY id with rollup
ORDER BY CASE WHEN @sortbyrows=1 THEN sum(CASE WHEN indid<2 THEN rows END) ELSE sum(reserved)*8 END desc

Usage:

EXEC sp_space --show stats sorted by reserved space size
EXEC sp_space 1 --show stats sorted by row count

It basically displays the results of sp_spaceused, but broken out by each table, and it rolls it up to a grand total. Naturally, for the most accurate results you should run DBCC UPDATEUSAGE before running this sproc.

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-01-13 : 20:13:10

Please, forgive my question...

I run your sp and see "total data = 466" But in what units ? 466 bytes??

The host company who host my site give 250 Mb space to sql server...

does it means that my "data" showed on by your sp can up to 250000 ?

Thanks for your sp!

X002548
Not Just a Number

15586 Posts

Posted - 2007-01-14 : 07:18:49

I guess this the best (if only) use of nolock I have ever seen

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-01-14 : 16:10:28

:( You didn't answer me !

which the units of the field “data” generated by this procedure?

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-14 : 20:48:20

this is a free forum. you get what you pay for.

if you want to know the units, why don't you read the query and try to figure it out? it's hitting the sysindexes table. so lookup the sysindexes table in BOL and see what the units of the column in question are:

http://msdn2.microsoft.com/en-us/library/ms190283.aspx

There you'll find that dpages contains the number of pages used. that, combined with the knowledge that there is 8kb on a page, is your answer. If you didn't know a page is 8kb, you can google that too:

http://www.google.com/search?q=page+size+sql+server

the first link that comes up has it. so the units of the "data" column in the function above are in kb.

moral: google is your friend.

edit: fixed link.

www.elsasoft.org

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-01-15 : 14:43:48

thanks jezemine!

However, when i run sp_spaceused the results are diferent (most diferent) from the results with this procedure!

If you can answer this :
When my host company tell that i only have 200 Mb for use sql, thats the data field * 1024 right?

or my database uses more memory than the listed in the field “data”?
I am not very exeperiente in mssql.
I only wanted to use this procedure to know the total size of my database and when he is full

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2007-01-15 : 16:45:28

if you just want to know the size of the db, sp_spaceused is adequate, look at the first result set it returns for that.


use AdventureWorks
exec sp_spaceused

database_name  database_size      unallocated space
-------------- ------------------ ------------------
AdventureWorks 181.94 MB          15.16 MB

reserved           data               index_size         unused
------------------ ------------------ ------------------ ------------------
168736 KB          84096 KB           77960 KB           6680 KB

www.elsasoft.org

maxim
Yak Posting Veteran

51 Posts

Posted - 2007-01-15 : 17:14:07

thanks very much jezemine

I try this procedure and receive a total size = 13 Mb

Then i clear my custom table "backups" and then my total size up to 16 Mb !!!!!

This fields space is bigger in log file than sql table

If i can't find a way to clean my log file i'll go to 200 Mb soon!

Thanks Jezemine!

ksampatk
Starting Member

3 Posts

Posted - 2010-04-12 : 11:02:29

Try below query to fetch SQL Database Size

select db_name(dbid),str(convert(dec(15),sum(size))* 8192/ 1048576,10,2)+ N' MB' as Size from sys.sysaltfiles
group by dbid order by 2 desc
GO

Sampath Karnatakam - DBA Architect

ksampatk
Starting Member

3 Posts

Posted - 2010-04-12 : 11:02:30

ShamanDBA
Starting Member

2 Posts

Posted - 2010-06-14 : 13:20:04

Here is a real simple one

SELECT
DatabaseName,
ROUND(SUM(DataSize)/1024,0) AS DataSizeMB,
ROUND(SUM([LogSize])/1024,0) AS LogSizeMB
FROM
(
SELECT
DB_NAME(database_id) AS DatabaseName,
CAST(
CASE [type]
WHEN 0 THEN
(CAST([size] AS float) * CAST(8192 AS float))/1024
ELSE
0
END
AS float)AS DataSize,
CAST(
CASE [type]
WHEN 1 THEN
(CAST([size] AS float) * CAST(8192 AS float))/1024
ELSE
0
END
AS float)AS LogSize
FROM sys.[master_files]
) DatabaseSizes
GROUP BY [DatabaseName]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources