Need Help to write Purge Job on Large table

• madhuu1905

  Mr or Mrs. 500

  Points: 556

  More actions

  February 2, 2012 at 7:25 am

  #252296

  Hi Friends ,

  i need help in writing Purge Script on One table Name Logins, Requirement is we need 90 Days Retention, partitioning, Purge rate is 10k batches. Please find the Table structure below

  [dbo].[Logins](

  [AccountNumber] [varchar](20) NOT NULL,

  [Loginid] [varchar](10) NOT NULL,

  [ProductCode] [varchar](3) NOT NULL,

  [SubProductCode] [varchar](3) NOT NULL,

  [TranDate] [datetime] NOT NULL,

  [TranAmount] [numeric](19, 2) NOT NULL,

  [VarcharData1] [varchar](50) NULL,

  [SubscriptionTypeId] [smallint] NOT NULL,

  [CreateDate] [datetime] NOT NULL

  Please help on this please

• Loundy

  SSCertifiable

  Points: 6080

  More actions

  February 2, 2012 at 7:29 am

  #1441819

  this will delete anything older than 90 days based on CreatedDate

  DELETE FROM dbo.logins

  WHERE CreateDate < CONVERT(CHAR(10),DATEADD(DD,-90,GETDATE()),121);

  i.e, anything older than 2011-11-04 (assuming its run today)

  [font="Times New Roman"]There's no kill switch on awesome![/font]