SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Enhanced sp_spaceused
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

robvolk
Most Valuable Yak

USA
15658 Posts

Posted - 08/16/2005 :  12:09:45  Show Profile  Visit robvolk's Homepage  Reply with Quote
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 - 01/13/2007 :  20:13:10  Show Profile  Reply with Quote
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!

Edited by - maxim on 01/14/2007 06:27:22
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 01/14/2007 :  07:18:49  Show Profile  Reply with Quote
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



Go to Top of Page

maxim
Yak Posting Veteran

51 Posts

Posted - 01/14/2007 :  16:10:28  Show Profile  Reply with Quote
:( You didn't answer me !

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

Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 01/14/2007 :  20:48:20  Show Profile  Visit jezemine's Homepage  Reply with Quote
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

Edited by - jezemine on 01/14/2007 20:55:57
Go to Top of Page

maxim
Yak Posting Veteran

51 Posts

Posted - 01/15/2007 :  14:43:48  Show Profile  Reply with Quote
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
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2885 Posts

Posted - 01/15/2007 :  16:45:28  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Go to Top of Page

maxim
Yak Posting Veteran

51 Posts

Posted - 01/15/2007 :  17:14:07  Show Profile  Reply with Quote
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!
Go to Top of Page

ksampatk
Starting Member

India
3 Posts

Posted - 04/12/2010 :  11:02:29  Show Profile  Reply with Quote
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
Go to Top of Page

ksampatk
Starting Member

India
3 Posts

Posted - 04/12/2010 :  11:02:30  Show Profile  Reply with Quote
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
Go to Top of Page

ShamanDBA
Starting Member

USA
2 Posts

Posted - 06/14/2010 :  13:20:04  Show Profile  Reply with Quote
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]
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.2 seconds. Powered By: Snitz Forums 2000