SQL Server Performance Forum – Threads Archive

Deleting All Records

Hi All, Bear with me this is my Post post on the Forum. I run the Following Script Manually every few days: declare @OldestInboundID as int
delete from inbound where datediff(d, dt, getdate()) > 17
set @OldestInboundID = (select top 1 inboundid from inbound order by inboundid)
delete from roads where inboundid < @OldestInboundID What the above does is remove records from one table (inbound) that are older than 17 days old then uses the InboundID to delete the Older records from the Second table (roads) as it does not contain dates. The problem is this runs OK 99% of the time but on two occasions now it has deleted
every record in the Roads Table. Is it possible that the variable @OldestInboundID could be losing it contents? I have now ammended thsi script to the Following:
if @OldestInboundID > 0 AND @OldestInboundID IS NOT NULL
BEGIN
delete from roads where inboundid < @OldestInboundID
END Will this solve my problem? Regards,
Sean Martin

Hi Sean, it seems odd… yes the validation that you are proposing may be worthwhile but I would not expect it to cause deletion of all of the roads…? only if @OldestInboundIF had some memory of a previous select which is higher than all inboundids in roads and there were no records in inbound would that make sense to me…? Is the script that you run exactly those 4 statements, or is this an extract of something bigger? Cheers
Twan

Hi Twan, Here is a copy of the Exact script that I run in Query Analyzer every day/few Days. The tables Roads and Inbound would be getting Updated Constantly would this have an affect? I know that InboundID can never be null in inbound as its an Auto Increment Column
/* Beginning Of Script */
declare @OldestInboundID as int
SELECT count(*) as ResetCount from ResetsFaults
select count(*) as InboundCount from inbound
select count(*) as messageDelayCount from messagedelay
select count(*) as delayCount from delays
select count(*) as RoadsCount from roads delete from ResetsFaults where datediff(d, dt, getdate()) > 17
delete from inbound where datediff(d, dt, getdate()) > 17
delete from messagedelay where datediff(d, timesent, getdate()) > 17
delete from delays where datediff(d, timesent, getdate()) > 17
set @OldestInboundID = (select top 1 inboundid from inbound order by inboundid)
delete from roads where inboundid < @OldestInboundID SELECT count(*) as ResetCount from ResetsFaults
select count(*) as InboundCount from inbound
select count(*) as messageDelayCount from messagedelay
select count(*) as delayCount from delays
select count(*) as RoadsCount from roads /*End of Script */ Ragards,
Sean

What sequence do SQl commands get executed in. Can the Second Command begin before the first begins within a SQL File?

Hi ya, no I can’t explain this one… the commands will be sequential, the SQL query processor will never run two commands from the same batch at the same time. and your code should work regardless of whether the inbound table is empty or not. If you do a declare @OldestInboundID int
select * from roads where inboundid < @OldestInboundID you don’t get any results right? Cheers
Twan

declare @OldestInboundID int
select * from roads where inboundid < @OldestInboundID
Yeah the Above Statement returns nothing. Like I said it has only happended twice in the last 6 months after being run nearly every day. May be something else that is causing it and Not the SQL but it seems to be When the SQL is Run. Would a V Busy SQL Server Cause unexpected results like this? May just have to include the validation and hope it does not happen again… Thanks Again Twan

nah it should make no difference how busy the server is, your code looks solid to me… the only thing you may want to do is to set a variable to getdate() and then use the variable, since it is theoretically possible that your delete statements will delete slight different rows since getdate() is different for each statement… the validation definitely can’t hurt <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ /> Cheers Twan

I can’t find how unrelated rows could be deleted either. But since you have strange behaviour you can try this version:<pre id="code">declare @DeleteTo dateTime set @DateTo = DateAdd(d, -17, getDate()) delete from r from roads r join inbound i on i.InboundID = r.InboundId where i.dt < @DateTo delete from inbound where dt < @DateTo</pre id="code">It is slower, but more precise. <img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />

Thanks Guys, I will try out your suggestions and see how things pan out..

]]>