delete tables

Question

delete tables

hodhod26

Mr or Mrs. 500

Points: 515
More actions
June 27, 2011 at 5:56 am

#252580

Hi All,
i have database file reach to 60 GB :w00t: and i want to know which table contain the most record to delet it so can any one help me how to determine this table and how to delet the record from it.
Regards.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

i have a better question: how do you know, businesswise, you can arbitrarily delete data just because teh table is huge?

wouldn't big tables mean they are busy and in use, and shouldn't be touched at all?

if the database is large, is it in FULL recovery mode? do you ever take any backups (FULL and also Transaciton log?)

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 2

Ditto. Buy more space. Make sure you're backing up both the db and logs so the logs don't fill up the drive uselessly and don't ever delete data without your managers signed approval... and even then archive it somewhere else just in case.

hodhod26 Mr or Mrs. 500 Points: 515 More actions · Answer 3

this data is related to antivirus servers which running from 3 yeares or more we need to delet old data because this size of data make the server so slowely.

Lowell SSC Guru Points: 323518 More actions · Answer 4

hodhod26 (6/27/2011)
this data is related to antivirus servers which running from 3 yeares or more we need to delet old data because this size of data make the server so slowely.

analyze the execution plans of queries hitting the data. add any required indexes, or fix the queries to use the indexes. I've got tables that have a billion or more rows of data, and with proper indexing they are nice and fast (sub second responses).

alternatively, you could archive out some of the data, but it exists for a reason....someone needs to determine what to archive out, and so far this post hasn't really given us enough to give you a decent suggestion.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

john_jakob Mr or Mrs. 500 Points: 587 More actions · Answer 5

If you just want to know which tables have the most rows, try a query like this:

--

-- Number of Rows In Each Table

--

select s.name AS 'Schema', o.name AS 'Table', i.rows AS 'Rows'

from sys.sysobjects o

inner join sys.sysindexes i on o.id = i.id

inner join sys.schemas s on s.schema_id = o.uid

where i.indid < 2

and o.name NOT LIKE 'sys%' -- Exclude System Tables

order by s.name, i.rows DESC

Greg Roberts-134214 SSCommitted Points: 1686 More actions · Answer 6

To tag along with this advice, also be carefull that the table you want to shrink does not have other tables with FK references. You are looking into a very deep rabbit hole.

Also, get your Director or CIO's written approval, in triplicate, before you do anything.

As this is an AV system you are working with, contact the vendor. They most likely have an archiving solution that will satisfy your maintenance needs, and your audit history needs as well.

Greg Roberts

Graeme100 SSCrazy Eights Points: 8054 More actions · Answer 7

Why don't you archive the data to a csv that can be zipped up into a fraction of the space..that way you have a fall back if the data is required.

You can archive it by month and year..

Just a thought !!

Just deleting data seems risky to me..

HTH

Graeme