Solved

sql server 2005 - eliminate blocks of 500 by 500.

Posted on 2013-06-20
17
383 Views
Last Modified: 2013-07-03
Please experts,

I need this requirement:
- Delete records leaving records of the last 60 days.
- The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.

Since this table contains a lot of records, it is necessary to clean the table for periods, considering the date

I am using sql server 2005, i can not use cursor.

You need to clear the transaction records 500 records without locking the entire table completely and releasing locks on each transaction
0
Comment
Question by:enrique_aeo
  • 7
  • 3
  • 3
  • +2
17 Comments
 
LVL 11

Expert Comment

by:Louis01
ID: 39261929
I do not understand what you mean by the "3 months" part, but hopefully this helps:

To delete the oldest 500 records older than 60 days (identified by DateTime column myTable.recordDateTime) in a table called myTable:

delete top 500
from myTable
where myTable.recordDateTime >= dateadd(day, -60, getdate())
order by myTable.recordDateTime desc

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 39261937
THE table is very large, that is why you want to delete in groups of three months and within those three months of 500 in 500
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39261987
this previously answered question (PAQ) may be of assistance - it discusse using batches.
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 11

Assisted Solution

by:Louis01
Louis01 earned 167 total points
ID: 39261988
If you have an index on myTable.recordDateTime (in my sample above), limiting to to 3 month blocks is not going to make any difference.

By default a TABLOCK (Full table lock) is used. For specifying different locking, see this link for a locking operation that would suit your needs.

For instance:
delete top 500
from myTable
WITH (ROWLOCK) 
where myTable.recordDateTime >= dateadd(day, -60, getdate())
order by myTable.recordDateTime desc 

Open in new window


A note about ROWLOCK though:
The with (rowlock) is a hint that instructs the database that it should keep locks on a row scope. That means that the database will avoid escalating locks to block or table scope.

You use the hint when only a single or only a few rows will be affected by the query, to keep the lock from locking rows that will not be deleted by the query. That will let another query read unrelated rows at the same time instead of having to wait for the delete to complete.

If you use it on a query that will delete a lot of rows, it may degrade the performance as the database will try to avoid escalating the locks to a larger scope, even if it would have been more efficient.

You need to test this though, the ROWLOCK is just a hint and not a guarantee
0
 
LVL 11

Expert Comment

by:Louis01
ID: 39261989
0
 

Author Comment

by:enrique_aeo
ID: 39262025
Please,

Anyway I need your script support

The range of debugging for the deletion of records is 3 months. Within these three months should eliminate blocks of 500 by 500.
0
 
LVL 9

Expert Comment

by:sarabhai
ID: 39262027
Please show the table structure,

use  WITH (ROWLOCK) for delete statement.
And using temp table add all ids that going to delete from the table and then delete it from table one by one using loop.
0
 

Author Comment

by:enrique_aeo
ID: 39272740
This is the table
--Create table and its columns
CREATE TABLE [dbo].[pruebas_rln] (
      [id] [int] NOT NULL IDENTITY (1, 1),
      [sesion] [varchar](34) NULL,
      [pais] [varchar](2) NULL,
      [tienda] [varchar](10) NULL,
      [kiosco] [varchar](10) NULL,
      [tipoConsulta] [varchar](1) NULL,
      [tipoProducto] [varchar](20) NULL,
      [tipoTarjeta] [varchar](20) NULL,
      [numTarjeta] [varchar](20) NULL,
      [tipoDocumento] [varchar](1) NULL,
      [numDocumento] [varchar](20) NULL,
      [codigoPantalla] [varchar](20) NULL,
      [tiempoVisita] [int] NULL,
      [tiempoRespuesta] [int] NULL,
      [impresionRealizada] [varchar](1) NULL,
      [fechaRegistro] [datetime] NULL);
0
 

Author Comment

by:enrique_aeo
ID: 39272753
I attached some data
script-depuracion---Copy.txt
0
 

Author Comment

by:enrique_aeo
ID: 39279609
Please experts, you support is very important for me
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 333 total points
ID: 39286763
Try this code:

declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),dateadd(days, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight

while (select count(1) from [dbo].[pruebas_rln] where fechaRegistro <  @DeleteDate) >0
begin 
     delete top (500)
     from   [dbo].[pruebas_rln]
     fechaRegistro >  @DeleteDate
end 

Open in new window


It will delete the records 500 at a time that are older than 60 days.

NOTE: This is aircode, but I have done similar in the past.
0
 

Author Comment

by:enrique_aeo
ID: 39288421
Hi experts

I test but too long

declare @contador int = 0
declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),dateadd(day, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight
--print @DeleteDate

while (select count(1) from [dbo].[pruebas_rln] where fechaRegistro <  @DeleteDate) >0
begin
     delete top (50)
     from   [dbo].[pruebas_rln]
     where fechaRegistro >  @DeleteDate;

       --@contador = @contador +1;
       print @contador
end

Hello experts, I tried it but the query takes more than five minutes, I'm not sure but I think it has entered an infinite loop, please support
0
 
LVL 38

Expert Comment

by:Jim P.
ID: 39288673
How many record are there if you

declare @DeleteDate as datetime
select @DeleteDate = cast( convert(varchar(10),dateadd(day, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight
--print @DeleteDate

select count(1) from [dbo].[pruebas_rln] where fechaRegistro <  @DeleteDate

Open in new window

0
 

Author Comment

by:enrique_aeo
ID: 39295326
for my test I have 304 rows
0
 
LVL 38

Accepted Solution

by:
Jim P. earned 333 total points
ID: 39295354
Is your test system significantly different from your production system?

Is the table doing cascade deletes to sub-tables?

This is a case where we can't see your systems, databases, and your setups. In addition, my mind reading license expired about ten years ago and I haven't been able to get it renewed.

So if you run the code below:

declare @DeleteDate as datetime
declare @StopDate as datetime
select @StopDate = cast( convert(varchar(10),dateadd(days, -60, getdate()),101) as datetime) -- this gets you 60 days ago at midnight

select @DeleteDate = cast( convert(varchar(10),dateadd(days, -120, getdate()),101) as datetime) -- this gets you 120 days ago at midnight

while   @DeleteDate< @StopDate 
begin 
     delete top (500)
     from   [dbo].[pruebas_rln]
     fechaRegistro  = @DeleteDate

-- increment @DeleteDate by 1 day
    select @DeleteDate = dateadd(day,1,@DeleteDate)
     print @DeleteDate
end 

Open in new window


But if you are doing cascade deletes, it will take time. If you are running on a crap test system it will take time. If there is an error, it will eventually respond. If the fechaRegistro  is encoded as UTC and the rest of the system is set to EST, it has to calculate it.
0

Featured Post

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 30
Slow Connectivity over ODBC 8 35
SQL Help 27 45
Present Absent from working date rage 11 16
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question