Re: Large table structure
From: Rachel (rachel_at_xyz.com)
Date: 10/18/04
- Next message: Jéjé: "SORT_IN_TEMPDB not use tempdb???"
- Previous message: Steven Frank: "SuperSocket Warning"
- In reply to: Kalen Delaney: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 18 Oct 2004 10:02:58 +1000
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
> 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...
> >> 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...
> >> >> 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...
> >> >> > > > 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...
> >> >> > >
> >> >> > >
> >> >> >
> >> >> >
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>
>
- Next message: Jéjé: "SORT_IN_TEMPDB not use tempdb???"
- Previous message: Steven Frank: "SuperSocket Warning"
- In reply to: Kalen Delaney: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
