Re: Large table structure
From: Kalen Delaney (replies_at_public_newsgroups.com)
Date: 10/05/04
- Next message: Sean Shanny: "How to get executing sql and pids via sql from a command line...."
- Previous message: Sue Hoegemeier: "Re: sql event viewer error"
- In reply to: Rachel: "Re: Large table structure"
- Next in thread: Rachel: "Re: Large table structure"
- Reply: Rachel: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 4 Oct 2004 18:48:08 -0700
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: Sean Shanny: "How to get executing sql and pids via sql from a command line...."
- Previous message: Sue Hoegemeier: "Re: sql event viewer error"
- In reply to: Rachel: "Re: Large table structure"
- Next in thread: Rachel: "Re: Large table structure"
- Reply: Rachel: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
