delete statement not deleting all the data it's supposed to

Question

Post reply

delete statement not deleting all the data it's supposed to

alen teplitsky

SSC-Dedicated

Points: 30014
More actions
December 1, 2010 at 7:30 am

#149422

on our DBA server i have a table that stores all the application and security log info from our SQL servers. it's supposed to store 30 days worth of data at all times. every hour there is a job to delete anything older than 30 days.
once in a while the delete takes too long and i have to delete data manually after a day or so and at this time i start at 40 days. and i've noticed that every time there is always data in there that is longer than 30 days.
is there any reason why it would not be deleting all the data?

Viewing 15 posts - 1 through 15 (of 23 total)

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

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

Care to post the statement and sample "not deleted" data.

Have you confirmed that the jobs have run and NOT deleted the data?

Maybe there's a weird if in the job or something disable that job if there's problem.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Mistake in the code? Without seeing the code, there's not really much that can be said.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 3

This is the table

USE [logs]

GO

/****** Object: Table [dbo].[sql_server_logs] Script Date: 12/01/2010 10:14:34 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[sql_server_logs](

[EventLog] [varchar](255) NULL,

[RecordNumber] [bigint] NOT NULL,

[TimeGenerated] [datetime] NULL,

[TimeWritten] [datetime] NULL,

[EventID] [int] NULL,

[EventType] [int] NULL,

[EventTypeName] [varchar](255) NULL,

[EventCategory] [int] NULL,

[EventCategoryName] [varchar](255) NULL,

[SourceName] [varchar](255) NULL,

[Strings] [varchar](255) NULL,

[ComputerName] [varchar](255) NULL,

[SID] [varchar](255) NULL,

[Message] [varchar](4000) NULL,

[Data] [varchar](255) NULL,

[pk_id] [bigint] IDENTITY(1,1) NOT NULL,

[timestamp] [timestamp] NOT NULL,

CONSTRAINT [PK_sql_server_logs] PRIMARY KEY NONCLUSTERED

(

[pk_id] ASC

)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

and the delete

delete sql_server_logs

where timegenerated < getdate() - 30

and eventlog = 'Security'

print @@rowcount

clustered index on the timegenerated column because most of the data access is for only the last day or so

i checked the statistics in the last few days and they are up to date all the time. and looking at the range scans i see there is data in the table from 6 months ago

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

alen teplitsky (12/1/2010)
looking at the range scans i see there is data in the table from 6 months ago

For the security log?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 5

there is a vb script on all the servers to dump data to a staging table for that server. then this import job runs to copy only the new data and truncate the staging table.

as part of the process it also deletes anything older than 30 days. or it's supposed to. it does delete data because when i do spot checks i see it deleting millions of rows a day, but when it gets stuck for a day or so and i have to manually clear it up i notice that there is very old data in the table when there shouldn't be

SteveBell SSCrazy Points: 2008 More actions · Answer 6

Hi Alan;

Someone with much more expertise than I will be by in a moment to provide correct syntax, but " getdate() - 30" does not subtract 30 days, it subtracts 30 seconds or milliseconds.

I believe that you should investigate the DATEADD function in Books Online.

Steve

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

SwayneBell (12/1/2010)
Hi Alan;
Someone with much more expertise than I will be by in a moment to provide correct syntax, but " getdate() - 30" does not subtract 30 days, it subtracts 30 seconds or milliseconds.
I believe that you should investigate the DATEADD function in Books Online.
Steve

No difference :

SELECT DATEADD(D, -30, GETDATE()), GETDATE() - 30

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

SwayneBell (12/1/2010)
but " getdate() - 30" does not subtract 30 days, it subtracts 30 seconds or milliseconds.

It subtracts 30 days. A 30 sec test would have shown that.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQLRNNR SSC Guru Points: 281334 More actions · Answer 9

SwayneBell (12/1/2010)
Hi Alan;
Someone with much more expertise than I will be by in a moment to provide correct syntax, but " getdate() - 30" does not subtract 30 days, it subtracts 30 seconds or milliseconds.
I believe that you should investigate the DATEADD function in Books Online.
Steve

getdate() - 30 subtracts 30 days. Test the code you will see that it does indeed subtract days and not seconds or milliseconds.

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 10

i have daily SSRS reports hitting that table for SQL app log errors, failed logins, etc that go back a few days and i'm pretty sure it doesn't subtract milliseconds. the table is a few hundred million rows and it deletes 10 million or so rows on a daily basis

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

Is this script scheduled?

Are you getting any errors in the logs?

Are you logging the @@rowcount after each exec?

We don't have much to go on without that info...

sebastien piche Ten Centuries Points: 1070 More actions · Answer 12

i belive getdate -30 return not the same values depend some configuration for me is retuen this

for sql 2000 2005 and 2008

SELECT getdate() - 30,GETDATE()

----------------------- -----------------------

2010-11-01 10:36:50.547 2010-12-01 10:36:50.547

(1 row(s) affected)

into you delete you have

delete sql_server_logs

where timegenerated < getdate() - 30

and eventlog = 'Security'

print @@rowcount

eventlog = 'Security' the data leave into table minus getdate() - 30 have eventlog 'Security' or other word maybe is null you server is case sensitive and you security or SEcuRity ect ..

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

sebastien piche (12/1/2010)
i belive getdate -30 return not the same values depend some configuration for me is retuen this
for sql 2000 2005 and 2008
SELECT getdate() - 30,GETDATE()
----------------------- -----------------------
2010-11-01 10:36:50.547 2010-12-01 10:36:50.547
(1 row(s) affected)
into you delete you have
delete sql_server_logs
where timegenerated < getdate() - 30
and eventlog = 'Security'
print @@rowcount
eventlog = 'Security' the data leave into table minus getdate() - 30 have eventlog 'Security' or other word maybe is null you server is case sensitive and you security or SEcuRity ect ..

No it's always going to be returning 30 days in the past. The end, next problem.

I like your idea of the case sensitive. But since this is all done via automation it's unlikely the issue.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 14

Ninja's_RGR'us (12/1/2010)
Is this script scheduled?
Are you getting any errors in the logs?
Are you logging the @@rowcount after each exec?
We don't have much to go on without that info...

i also have another step for the application log. did it this way to keep the amount of DML to a minimum for each step