Re: Large table structure
From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 09/30/04
- Next message: Jack: "RE: Running a job continuously"
- Previous message: Tibor Karaszi: "Re: Trailing spaces padding out cells?"
- Next in thread: Rachel: "Re: Large table structure"
- Reply: Rachel: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Date: Thu, 30 Sep 2004 18:56:23 +0200
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: Jack: "RE: Running a job continuously"
- Previous message: Tibor Karaszi: "Re: Trailing spaces padding out cells?"
- Next in thread: Rachel: "Re: Large table structure"
- Reply: Rachel: "Re: Large table structure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
