Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Exclamation Unanswered: Daily deletion of records

    Ladys, Gentlement, I have table that grows anywhere from 200,000 to 1,000,000 records perday. Besides that I need to keep at least 6 months historical data from this same table. The transaction log was purged after each batch when testing data monthly. I'm looking for some way of deleting just one day's data if it meets a criteria. It must remain within the 6 months period of historical data. This is what I've come up with so far"

    select * FROM dbo.Temp_table WHERE datediff(day, DATE_TIME, getdate()) >= 180

    If it meets this criteria I can change the select to a delete? Please Let me know what you think

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Either of these would be faster and make more efficient use of an index on your DATE_TIME column:

    select * FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(day, -180, getdate())

    select * FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(month, -6, getdate())
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    2 million deletes a day and 2 million inserts?

    I think I'd worry about the transaction log a wee bit....

    Code:
    USE Northwind
    GO
    
    CREATE TABLE myTable99(
    	  Col1 int IDENTITY(1,1)
    	, Col2 datetime
    )
    GO
    
    SET NOCOUNT ON
    
    --Lets make some data for 180 days old
    
    DECLARE @x char(10), @y int
    
    SELECT @x=CONVERT(char(10),DATEADD(dd,-180,Getdate()),101), @y = 4
    
    WHILE @y < 24
      BEGIN
    	INSERT INTO myTable99(Col2)
    	SELECT CONVERT(datetime, @x + ' ' + RIGHT('0'+CONVERT(varchar(2),@y),2)+':00:00')
    	SELECT @y = @y + 4
      END
    
    INSERT INTO myTable99(Col2)
    SELECT CONVERT(datetime,@x + ' ' + '23:59:59')
    
    
    SELECT * FROM myTable99
    
    
    -- And basically Delete them in the same manner
    
    
    SELECT @x=CONVERT(char(10),DATEADD(dd,-180,Getdate()),101), @y = 4
    
    WHILE @y < 24
      BEGIN
    	BEGIN TRAN
    	DELETE FROM myTable99
    	 WHERE Col2 <= CONVERT(datetime, @x + ' ' + RIGHT('0'+CONVERT(varchar(2),@y),2)+':00:00')
    	SELECT @y = @y + 4
    	COMMIT TRAN
      END
    BEGIN TRAN
    	DELETE FROM myTable99
    	 WHERE Col2 <=  CONVERT(datetime,@x + ' ' + '23:59:59')
    COMMIT TRAN
    
    SELECT * FROM myTable99
    GO
    
    SET NOCOUNT OFF
    GO
    
    DROP TABLE myTable99
    GO
    
    SELECT @@TRANCOUNT
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Oooops...forgot this...

    CREATE INDEX myIndex99 ON myTable99(Col2)

    GO

    Make sure you have the index...it'll slow down inserts, but the index seek, with the transaction batch deletes should fly....
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  5. #5
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Why can't you just do:

    DELETE FROM dbo.Temp_table WHERE DATE_TIME <=dateadd(day, -180, getdate())

    That will delete all records that are older then 6 months.
    That which does not kill me postpones the inevitable.

  6. #6
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Thumbs up Daily Deletion

    That's what I came up with so far. I just used select for testing in the Anayser. The only thing is it does'nt address the transaction log.

  7. #7
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Question error message

    I've changed the backup type and I continue to get this error. I'm at a lost on this one. The sequence goes like this. I make my select/delete statement followed by a backup the log before running the shrink. I've attempted suppling a path in the statement but I just get another error. The complete statement is:

    select * FROM dbo.INCOMING WHERE DATE_TIME <=dateadd(day, -180, getdate())
    BACKUP LOG wslogdb62 WITH TRUNCATE_ONLY
    DBCC SHRINKFILE(wslogdb62_log, 1)

    Could not locate file 'wslogdb62_log' in sysfiles.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Exactly what is your SELECT statement for, and why do you think you need to combine that with backing up your log? The results of the select statement are not logged. It makes no change to the database.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  9. #9
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Wink error message

    The select is only when testing????

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Is that a question??? Or a statement made in confusion???
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Apr 2003
    Location
    Washington DC area
    Posts
    1,770

    Red face error message

    Just a statement in my case.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •