Remove Old Data & Shrink the DB - best way?

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  January 21, 2004 at 1:52 pm

  #83914

  I have a SQL Database, one table has may rows. Most all of the data is in a TEXT column (basically a log table). 250K rows now. Clustered index on RID Int Identity column. I want to remove ~ 90% of the "older" rows, for the purpose of "trimming" down the DB size itself. What is the best way?

  I'm thinking... (All T-SQL in Q/A) Backup, copy the ~ 10% of the recs I want to keep to another DB, TRUNCATE the table, SHRINK something???, copy the "kept" records back in.

  Suggestions on the "SHRINK" part please, or other ideas?

  Thanks

  
  
  Once you understand the BITs, all the pieces come together

• vadba

  SSChampion

  Points: 11133

  More actions

  January 22, 2004 at 6:31 am

  #491108

  If you don't care about resetting the rid values, I would just use something like:

  (Assume table has two columns: rid int identity(1,1) and logtext text)

  delete logs

   where rid in (select top 90 percent rid from logs order by rid)

  or, if you wanted to reset the identity values to start from 1, you could do this:

  select logtext

    into #logs_temp

    from logs

   where rid not in (select top 90 percent rid from logs order by rid)

  truncate table logs

  insert logs (logtext) select logtext from #logs_temp

  drop table #logs_temp

  select * from logs

  Then, backup the database or truncate the transaction log, then run DBCC SHRINKDATABASE

  Mike

   

• ThomasH

  SSCertifiable

  Points: 7437

  More actions

  January 23, 2004 at 3:00 pm

  #491472

  Thanks Mike, sorry it took a while. I SELECTed INTO #Temp the recs, TRUNCATEd Table, Backed up, DBCC SHRINKDATABASE, INSERT .. SELECT FROM #Temp.

  Worked fine, brought the DB down to size.

  
  
  Once you understand the BITs, all the pieces come together