SQL Data Disk Space - 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

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Data Disk Space

Author

Topic

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-21 : 04:01:01

Hi everyone

Would it be possible to do a query like:
"select * from tablename where companyid = 2" and then see how much physical disk space the returned rows take up on the hard drive?

My company would like to charge customers for the amount of data in megabytes stored on our server, but we have about 200 companies' data in the DB, so I need to run queries for each company and then see how much space they're using.

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-21 : 04:21:00

Use sp_spaceused stored procedure to see the space used by the table.

--------------------------------------------------
S.Ahamed

superhero
Yak Posting Veteran

52 Posts

Posted - 2007-06-21 : 04:29:03

Yes but the table has about 200 companies in it, I need to find out how much space each company is using in the DB - just getting total table space will not allow me to create individual invoices...

pbguy
Constraint Violating Yak Guru

319 Posts

Posted - 2007-06-21 : 05:37:11

Try this...check whether it is correct...

create table #tt(name varchar(50), rows char(11), reserved varchar(50), data varchar(50), index_size varchar(50)
,unused varchar(50)) -- create temp table

Insert #tt
Exec sp_spaceused 'your table'-- insert the values from the stored proc

declare @rows int, @data_size decimal(15,2), @index_size decimal(15,2), @company_cnt int, @sing_row_size decimal(15,2)

--Take the info back to the variable
Select @rows = Cast(rows as int),
@data_size = Cast(Left(data, Charindex(' ', data) -1) as int),
@index_size = Cast(Left(index_size, Charindex(' ', index_size) -1) as int)
from #tt

--find the single row size
Set @sing_row_size = (@data_size + @index_size) / @rows

--Find the no. of rows from the table for the concern company
Select @company_cnt = Count(*)
from <your table>
Where company_id = 2

Select 'Total is ' + cast((@sing_row_size * @company_cnt) as varchar(50))

--------------------------------------------------
S.Ahamed

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-06-21 : 11:57:12

Hmmmmmm.....this may be time to think about moving each customer onto it's own database....

1. from a charging point of view.
2. from a security point of view.
3. from a 1 customer wants to restore, but nobody else does point of view.
4. from a our database is corrupt and it's better to have only 1 angry customer than 200 point of view.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources