Determine table size

Hi,

I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?

Thanks,
Mica

Hi Mica Cooper,

To Check table sizes, Select the db name in the enterprise under Databases,
In the Main Menu Click View and select Taskpad, you will get 3 tabs :
General, Table Info and Wizards, Select Table Info and you'll see all the
tables listed with rows count and size

Mario Aoun
"Mica Cooper" wrote:

> Hi,
>
> I have a client with a database that is running over 1G in size. Looking a
t
> the tables, I can't find any that would seem to be larger than a 1-2 meg
> with total size should be around 30meg. Is there any way to find out table
> sizes?
>
> Thanks,
> Mica
>
>
>

Use sp_spaceused or see: http://vyaskn.tripod.com/ sp_show_b...bl
es.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @ http://vyaskn.tripod.com/


"Mica Cooper" <Mica.Cooper@removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@TK2MSFTNGP10.phx.gbl...
Hi,

I have a client with a database that is running over 1G in size. Looking at
the tables, I can't find any that would seem to be larger than a 1-2 meg
with total size should be around 30meg. Is there any way to find out table
sizes?

Thanks,
Mica

Database size is a fuzzy thing.  I don't know your level of SQL experience,
so forgive me if I start off with some basics.

What do you mean, when you say "over 1GB in size"?  Right click on the
database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go to
properties.  On the first tab ("General"), there's a "Size", and then a
"Space Available".  Is this the 1GB you're talking about?  What does the
"Space Available" have beside it?

If that is the size that you're talking about, that includes the log file as
well as the actual data file.  Click on the next tab "Data Files", and see
how much space is allocated.  Then go to the "Transaction Log" tab, and see
the same thing there.  If the transaction log is huge, it may be as simple
as either adjusting your backup procedures, or changing the "Recovery Model"
on the "Options" tab.

The other thing that can happen is that SQL database grow automatically, but
the default settings do not let the databases shrink automatically.  To
shrink a database, right click on the database, and go to "All Tasks", then
"Shrink Database".  I seem to have the best luck with shrinking by clicking
on the "Files" button on the first form, then manually setting the desired
database file sizes by selecting first the data (.mdf) file, then the log
(.ldf) file.  You likely have to either do a complete backup or change the
"Recovery Model" before you'll get a lot of space out of the log file.

To find out the actual size of a particular table, you can execute the
"sp_spaceused <tablename>" function in Query Analyzer.  It will give you the
number of rows, reserved size, data size, index size, and unused size.

I hope this helped!  I'm not a SQL expert, and I don't play one on TV.  But
post back with your results and any new questions, if you need more
guidance.

Clint
"Mica Cooper" <Mica.Cooper@removethis.aisus.com> wrote in message
news:eMQy0MrkFHA.1444@TK2MSFTNGP10.phx.gbl...
> Hi,
>
> I have a client with a database that is running over 1G in size. Looking
> at the tables, I can't find any that would seem to be larger than a 1-2
> meg with total size should be around 30meg. Is there any way to find out
> table sizes?
>
> Thanks,
> Mica
>

Mario,
That is COOL! In all the years I've used SQL Server I have never seen that.
Its exactly what I was looking for.
Mica

"Mario Aoun" < MarioAoun@discussion
s.microsoft.com> wrote in message
news:8AD1DCE1-0E27-463B-BE9E- 59D00D5137C2@microso
ft.com...
> Hi Mica Cooper,
>
> To Check table sizes, Select the db name in the enterprise under
> Databases,
> In the Main Menu Click View and select Taskpad, you will get 3 tabs :
> General, Table Info and Wizards, Select Table Info and you'll see all the
> tables listed with rows count and size
>
> Mario Aoun
> "Mica Cooper" wrote:
> 

Clint,

I have already shrank the db and it didn't change the size. I am looking for
a problem becuase I know the db shouldn't be anywhere near that large and
this info should help. Mario's post was great. I have NEVER seen that before
and it told the story pretty quick.

So guess what? Look at the view told me what a dunce I am. The problem is
the log file is autmatically growing...1.3G. I should have seen that by
looking at the .LDF. Gotta go fix that now.

Thanks Guys,
Mica




"Clint" <nobody@nowhere.non> wrote in message
news:O6TXngrkFHA.1044@tk2msftngp13.phx.gbl...
> Database size is a fuzzy thing.  I don't know your level of SQL
> experience, so forgive me if I start off with some basics.
>
> What do you mean, when you say "over 1GB in size"?  Right click on the
> database in SQL Server Enterprise Manager (I'm assuming SQL 2000), and go
> to properties.  On the first tab ("General"), there's a "Size", and then a
> "Space Available".  Is this the 1GB you're talking about?  What does the
> "Space Available" have beside it?
>
> If that is the size that you're talking about, that includes the log file
> as well as the actual data file.  Click on the next tab "Data Files", and
> see how much space is allocated.  Then go to the "Transaction Log" tab,
> and see the same thing there.  If the transaction log is huge, it may be
> as simple as either adjusting your backup procedures, or changing the
> "Recovery Model" on the "Options" tab.
>
> The other thing that can happen is that SQL database grow automatically,
> but the default settings do not let the databases shrink automatically.
> To shrink a database, right click on the database, and go to "All Tasks",
> then "Shrink Database".  I seem to have the best luck with shrinking by
> clicking on the "Files" button on the first form, then manually setting
> the desired database file sizes by selecting first the data (.mdf) file,
> then the log (.ldf) file.  You likely have to either do a complete backup
> or change the "Recovery Model" before you'll get a lot of space out of the
> log file.
>
> To find out the actual size of a particular table, you can execute the
> "sp_spaceused <tablename>" function in Query Analyzer.  It will give you
> the number of rows, reserved size, data size, index size, and unused size.
>
> I hope this helped!  I'm not a SQL expert, and I don't play one on TV.
> But post back with your results and any new questions, if you need more
> guidance.
>
> Clint
> "Mica Cooper" <Mica.Cooper@removethis.aisus.com> wrote in message
> news:eMQy0MrkFHA.1444@TK2MSFTNGP10.phx.gbl... 
>
>