Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB???? 
Author Message
 Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB????

Hello,

This seems like a silly question, but my database (not the LOG file) has
grow to several hundred of MBs.

This is apparently, because there are tools in the system that allow you to
replicate databases for testing purposes, so we took something that was
60mbs to 80mbs, 200mbs etc. etc...

So now i'm trying to figure out which tables do I need to purge to bring
down the sys to a smaller size, since most of the data its junk anyways.

Is there a procedure or a tool that will allow me to see if my saleshistory
accounts for 150mbs or 68% of the db size?

Thanks for the help.

 - Sergio



Sat, 22 May 2004 04:09:24 GMT
 Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB????

Sergio,

try this:

CREATE TABLE #TempSpaceUsed
(name SYSNAME,
rows INT,
reserved VARCHAR(10),
data VARCHAR(10),
index_size VARCHAR(10),
unused VARCHAR(10))

INSERT #TempSpaceUsed
EXEC sp_MSforeachtable 'sp_spaceused ''?'''

Now do a TOP n from the temp table order by requiredcolumn desc

Dinesh.


Quote:
> Hello,

> This seems like a silly question, but my database (not the LOG file) has
> grow to several hundred of MBs.

> This is apparently, because there are tools in the system that allow you
to
> replicate databases for testing purposes, so we took something that was
> 60mbs to 80mbs, 200mbs etc. etc...

> So now i'm trying to figure out which tables do I need to purge to bring
> down the sys to a smaller size, since most of the data its junk anyways.

> Is there a procedure or a tool that will allow me to see if my
saleshistory
> accounts for 150mbs or 68% of the db size?

> Thanks for the help.

>  - Sergio



Sat, 22 May 2004 04:26:11 GMT
 Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB????
If you haven't figured this out yet I have a thought for you.  Try using
  sp_spaceused.  By itself it will report the reserved space of the
whole database.  Follow it with the table name and it will specifics of
reserved for that table.

use Northwind
go
sp_spaceused
go
sp_spaceused 'Categories'

Quote:
> So now i'm trying to figure out which tables do I need to purge to bring
> down the sys to a smaller size, since most of the data its junk anyways.

> Is there a procedure or a tool that will allow me to see if my saleshistory
> accounts for 150mbs or 68% of the db size?

> Thanks for the help.

>  - Sergio



Tue, 01 Jun 2004 01:53:29 GMT
 Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB????

Quote:

> Is there a procedure or a tool that will allow me to see if my saleshistory
> accounts for 150mbs or 68% of the db size?

I know that SQL Server 7 had, as part of the enterprise manager, a way
to view ALL the details for a table.  How big the indexes were, each
table, etc.  Very useful, but it apparently didn't get moved to SQL
2000's.

www.swynk.com has a script that does that, and there's a program
called SQLInsider that does that (among lots of other things).



Tue, 01 Jun 2004 23:59:59 GMT
 Who knows how to figure how much diskspace (or %) does a table take in relation to the whole DB????
Sergio,

Have a look at sp_spaceused
Hope this helps, DK MCP



Quote:

> > Is there a procedure or a tool that will allow me to see if my
saleshistory
> > accounts for 150mbs or 68% of the db size?

> I know that SQL Server 7 had, as part of the enterprise manager, a way
> to view ALL the details for a table.  How big the indexes were, each
> table, etc.  Very useful, but it apparently didn't get moved to SQL
> 2000's.

> www.swynk.com has a script that does that, and there's a program
> called SQLInsider that does that (among lots of other things).



Mon, 07 Jun 2004 18:48:29 GMT
 
 [ 5 post ] 

 Relevant Pages 

1. help figuring out why query doing full table scan

2. How does SET RELATION know what it knows?

3. Relations between tables n:m-relation

4. relation between tables of different db

5. DB DiskSpace Problem

6. ANYONE KNOW HOW TO FIGURE OUT THIS ONE?

7. Can't figure it out (I know youve been there)

8. Relations, Relation,s Relations 2.6

9. OLE DB Provider for ORACLE is doing a TABLE SCAN

10. Import All tables in an Access DB w/o knowing their names

11. Insufficient diskspace - 250Mb table >2Gb free space

12. Insufficient diskspace - 250 Mb table - 2Gb free space


 
Powered by phpBB® Forum Software