Purging old records in SQL table

• santhosh411

  Ten Centuries

  Points: 1155

  More actions

  August 9, 2012 at 3:39 pm

  #260585

  Hi,

  simple scripts Purging the old records in table.

  I have more than 1Lakh records, can any one tell me simple way to purge this old records.

  i am using below scripts

  delete from table_name where date_created < dateadd(d,-365,GETDATE())

  delete from events where datediff(m,datecolumn,getdate()) >= 365

  DELETE FROM <tableName> WHERE datecolumn < getdate()-365

  Above scripts are taking more time and some times we are not gaining the Space also.

  Please help on this.

• Igor Micev

  SSC-Dedicated

  Points: 33109

  More actions

  August 9, 2012 at 4:11 pm

  #1524147

  Hi,

  Try this code may help you

  ALTER TABLE table_name NOCHECK CONSTRAINT ALL

  ALTER TABLE table_name DISABLE TRIGGER ALL

  make your delete

  ALTER TABLE table_name CHECK CONSTRAINT ALL

  ALTER TABLE table_name ENABLE TRIGGER ALL

  Regards

  IgorMi

  Igor Micev,My blog: www.igormicev.com

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 9, 2012 at 4:12 pm

  #1524148

  You can start by reading this article: http://www.sqlservercentral.com/articles/T-SQL/67898/.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  August 9, 2012 at 4:13 pm

  #1524149

  santhosh411 (8/9/2012)

  ---

  Hi,

  simple scripts Purging the old records in table.

  I have more than 1Lakh records, can any one tell me simple way to purge this old records.

  i am using below scripts

  delete from table_name where date_created < dateadd(d,-365,GETDATE())

  delete from events where datediff(m,datecolumn,getdate()) >= 365

  DELETE FROM <tableName> WHERE datecolumn < getdate()-365

  Above scripts are taking more time and some times we are not gaining the Space also.

  Please help on this.

  Also, if you are expecting disk space to be released back to the OS by deleting records, not going to happen. The space you reclaim will be in the database.