Large table structure

Hi
Have noticed that some table structures get quite large in size, when you truncate the table it's still large. It doesn't appear to be an index issue. The large tables cause problems in MSDE as the database then reaches the 2 GB limit.
Has anyone seen this before or know why it happens?
Regards
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
> Have noticed that some table structures get quite large in size, when you truncate the table it's
still large.
How do you determine that? What command etc. are you using?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel.goodson@.iwl.com.au> wrote in message news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> Hi
> Have noticed that some table structures get quite large in size, when you truncate the table it's
still large. It doesn't appear to be an index issue. The large tables cause problems in MSDE as
the database then reaches the 2 GB limit.
> Has anyone seen this before or know why it happens?
> Regards
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Tibor
Thank you for your reply. To determine the size of the table, we truncated
the contains of the table and then compared it to a emtpy table in an
identical database. We found the truncated table was unusually large. We
would like to know if there is anyway to prevent this from happening.
Regards
Rachel
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
you truncate the table it's
> still large.
> How do you determine that? What command etc. are you using?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel.goodson@.iwl.com.au> wrote in message
news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
you truncate the table it's
> still large. It doesn't appear to be an index issue. The large tables
cause problems in MSDE as[vbcol=seagreen]
> the database then reaches the 2 GB limit.
ASP.NET resources...
>
|||What command did you use to "truncate the contains for the table".
Also, what commands did you use to check the size.
I.e., please post TSQL. If we didn't know what you did, it is very difficult to guess what is going
on.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel@.xyz.com> wrote in message news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> Hi Tibor
> Thank you for your reply. To determine the size of the table, we truncated
> the contains of the table and then compared it to a emtpy table in an
> identical database. We found the truncated table was unusually large. We
> would like to know if there is anyway to prevent this from happening.
> Regards
> Rachel
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:e912QSVoEHA.896@.TK2MSFTNGP12.phx.gbl...
> you truncate the table it's
> news:OAFiR8PoEHA.3172@.TK2MSFTNGP10.phx.gbl...
> you truncate the table it's
> cause problems in MSDE as
> ASP.NET resources...
>
|||Hi Tibor,
Here's what I did:
Got a database in from a client that surpasses the 2 gig limit.
Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
Noticed after comparing with another database with similar contents that
some tables are oversize.
Truncated the oversize table in query analyzer using the command:
truncate table <tablename>
I then truncated the table on the reference database using the same command.
Went back into the taskpad and the size of the empty table was still huge,
and space is only freed up in the database when the table is dropped. I
thought it might be a large index but it is not the case.
When the table is recreated using a script from an identical table in Object
Browser in Query Analyser, and the data is added back in, the database size
is reduced dramatically.
This issue seems to be getting more and more common and our clients are
asking questions as to why they have to purchase SQL Server when is it not
necessary.
Regards
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
> What command did you use to "truncate the contains for the table".
> Also, what commands did you use to check the size.
> I.e., please post TSQL. If we didn't know what you did, it is very
difficult to guess what is going
> on.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel@.xyz.com> wrote in message
news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
truncated[vbcol=seagreen]
We[vbcol=seagreen]
in[vbcol=seagreen]
when[vbcol=seagreen]
when[vbcol=seagreen]
tables[vbcol=seagreen]
************************************************** ********************
>
|||I suggest you look into DBCC UPDATEUSAGE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Rachel" <rachel@.xyz.com> wrote in message news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
> Hi Tibor,
> Here's what I did:
> Got a database in from a client that surpasses the 2 gig limit.
> Attached it in SQL Server and viewed the taskpad in Enterprise Manager.
> Noticed after comparing with another database with similar contents that
> some tables are oversize.
> Truncated the oversize table in query analyzer using the command:
> truncate table <tablename>
> I then truncated the table on the reference database using the same command.
> Went back into the taskpad and the size of the empty table was still huge,
> and space is only freed up in the database when the table is dropped. I
> thought it might be a large index but it is not the case.
> When the table is recreated using a script from an identical table in Object
> Browser in Query Analyser, and the data is added back in, the database size
> is reduced dramatically.
> This issue seems to be getting more and more common and our clients are
> asking questions as to why they have to purchase SQL Server when is it not
> necessary.
> Regards
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:O5rg5RUpEHA.592@.TK2MSFTNGP11.phx.gbl...
> difficult to guess what is going
> news:%23vSnZ3OpEHA.592@.TK2MSFTNGP11.phx.gbl...
> truncated
> We
> in
> when
> when
> tables
> ************************************************** ********************
>
|||This makes no difference as this relates to the indexes which are fine.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uN1ep5wpEHA.4076@.TK2MSFTNGP12.phx.gbl...
> I suggest you look into DBCC UPDATEUSAGE.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Rachel" <rachel@.xyz.com> wrote in message
news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
command.[vbcol=seagreen]
huge,[vbcol=seagreen]
Object[vbcol=seagreen]
size[vbcol=seagreen]
not[vbcol=seagreen]
in[vbcol=seagreen]
large.[vbcol=seagreen]
wrote[vbcol=seagreen]
&
>
|||Rachel
Please read the docs for DBCC UPDATEUSAGE again. Although it affects the
data stored in the sysindexes table, that does NOT mean it just pertains to
indexes. The command allows you to specify a table name to have its space
allocation information updated. Sysindexes keeps track of all objects in a
database that take up space, which includes tables, indexes and text/image
fields.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Rachel" <rachel@.xyz.com> wrote in message
news:Ov4u6tnqEHA.536@.TK2MSFTNGP09.phx.gbl...
> This makes no difference as this relates to the indexes which are fine.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in
> message news:uN1ep5wpEHA.4076@.TK2MSFTNGP12.phx.gbl...
> news:%23ZAwFtnpEHA.3244@.tk2msftngp13.phx.gbl...
> command.
> huge,
> Object
> size
> not
> in
> large.
> wrote
> &
>
|||Hi Kalen
I did run the command but it made no difference to the size of the tables.
Regards
Rachel
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:OgO2k1nqEHA.3728@.TK2MSFTNGP09.phx.gbl...
> Rachel
> Please read the docs for DBCC UPDATEUSAGE again. Although it affects the
> data stored in the sysindexes table, that does NOT mean it just pertains
to[vbcol=seagreen]
> indexes. The command allows you to specify a table name to have its space
> allocation information updated. Sysindexes keeps track of all objects in a
> database that take up space, which includes tables, indexes and text/image
> fields.
> --
> HTH
> --
> Kalen Delaney
> SQL Server MVP
> www.SolidQualityLearning.com
>
> "Rachel" <rachel@.xyz.com> wrote in message
> news:Ov4u6tnqEHA.536@.TK2MSFTNGP09.phx.gbl...
Manager.[vbcol=seagreen]
database[vbcol=seagreen]
are[vbcol=seagreen]
it[vbcol=seagreen]
size,[vbcol=seagreen]
size,[vbcol=seagreen]
************************************************** ********************
>