Depuracion Optima

Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
El scripts es el sgte:
use navy
go

declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000
while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
begin
delete navy..nv_trama
where w_fecha < @w_fecha
end

dump tran navy with no_log



La estructura de mi tabla es:
Name Owner Type Created_datetime
nv_trama dbo user table 2003-09-06 08:24:59.153



Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
FixedLenNullInSource Collation
w_fecha datetime no 8 no (n/a) (n/a)
w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS


Identity Seed Increment Not For Replication
No identity column defined.


RowGuidCol
No rowguidcol column defined.


Data_located_on_filegroup
PRIMARY


index_name index_description index_keys
IX_nv_trama clustered located on PRIMARY w_fecha


No constraints have been defined for this object.

No foreign keys reference this table.
No views with schema binding reference this table.


Y la tabla tiene 220.000

Hola, no entiendo que quieres depurar


Salu2
-
[MVP] SQL Server
Orador para Culminis Latam
www.sqlgurus.org



"Pao" escribió en el mensaje
news:
> Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
> El scripts es el sgte:
> use navy
> go
>
> declare @w_fecha as datetime
> select @w_fecha = dateadd(m,-3,getdate())
>
> set rowcount 10000
> while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> begin
> delete navy..nv_trama
> where w_fecha < @w_fecha
> end
>
> dump tran navy with no_log
>
>
>
> La estructura de mi tabla es:
> Name Owner Type Created_datetime
> nv_trama dbo user table 2003-09-06 08:24:59.153
>
>
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> w_fecha datetime no 8 no (n/a) (n/a)
> w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
> w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS
>
>
> Identity Seed Increment Not For Replication
> No identity column defined.
>
>
> RowGuidCol
> No rowguidcol column defined.
>
>
> Data_located_on_filegroup
> PRIMARY
>
>
> index_name index_description index_keys
> IX_nv_trama clustered located on PRIMARY w_fecha
>
>
> No constraints have been defined for this object.
>
> No foreign keys reference this table.
> No views with schema binding reference this table.
>
>
> Y la tabla tiene 220.000

Los datos de la table nv_trama que esta muy grande pero no se si es lo
mejor
hacerlo con ese delete mesclado con el set.

"Maxi [MVP]" wrote:

Hola, no entiendo que quieres depurar


Salu2
-
[MVP] SQL Server
Orador para Culminis Latam
www.sqlgurus.org



"Pao" escribió en el mensaje
news:
> Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
> El scripts es el sgte:
> use navy
> go
>
> declare @w_fecha as datetime
> select @w_fecha = dateadd(m,-3,getdate())
>
> set rowcount 10000
> while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> begin
> delete navy..nv_trama
> where w_fecha < @w_fecha
> end
>
> dump tran navy with no_log
>
>
>
> La estructura de mi tabla es:
> Name Owner Type Created_datetime
> nv_trama dbo user table 2003-09-06 08:24:59.153
>
>
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks
> FixedLenNullInSource Collation
> w_fecha datetime no 8 no (n/a) (n/a)
> w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
> w_trama varchar no 2048 yes no no
> SQL_Latin1_General_CP1_CI_AS
>
>
> Identity Seed Increment Not For Replication
> No identity column defined.
>
>
> RowGuidCol
> No rowguidcol column defined.
>
>
> Data_located_on_filegroup
> PRIMARY
>
>
> index_name index_description index_keys
> IX_nv_trama clustered located on PRIMARY w_fecha
>
>
> No constraints have been defined for this object.
>
> No foreign keys reference this table.
> No views with schema binding reference this table.
>
>
> Y la tabla tiene 220.000

Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
El scripts es el sgte:
use navy
go

declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

set rowcount 10000
while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
begin
delete navy..nv_trama
where w_fecha < @w_fecha
end

dump tran navy with no_log



La estructura de mi tabla es:
Name Owner Type Created_datetime
nv_trama dbo user table 2003-09-06 08:24:59.153



Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
w_fecha datetime no 8 no (n/a) (n/a)
w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS


Identity Seed Increment Not For Replication
No identity column defined.


RowGuidCol
No rowguidcol column defined.


Data_located_on_filegroup
PRIMARY


index_name index_description index_keys
IX_nv_trama clustered located on PRIMARY w_fecha


No constraints have been defined for this object.

No foreign keys reference this table.
No views with schema binding reference this table.


Y la tabla tiene 220.000

> index_name index_description index_keys
> IX_nv_trama clustered located on PRIMARY w_fecha
>

Pao,

No se a que te refieres cuando dices "depurar", pero en cuanto al scrip que
posteastes, a pesar de ser una buena idea hacer el delete en batches, no es
bueno que cuentes las filas como expresion logica del lazo.

Pudieras decirnos cual es la clave primaria de esta tabla?

declare @w_fecha as datetime
select @w_fecha = dateadd(m,-3,getdate())

while exists(select * from navy..nv_trama where w_fecha < @w_fecha)
begin
delete a
from
navy.dbo.nv_trama as a
inner join
(select top 10000 * from navy..nv_trama where w_fecha < @w_fecha) as b
on a.pk = b.pk
end
go


AMB

"Pao" wrote:

> Quiero depurar una tabla grande en mi sqlserver 2000 con sp3.
> El scripts es el sgte:
> use navy
> go
>
> declare @w_fecha as datetime
> select @w_fecha = dateadd(m,-3,getdate())
>
> set rowcount 10000
> while (select count(*) from navy..nv_trama where w_fecha < @w_fecha)> 0
> begin
> delete navy..nv_trama
> where w_fecha < @w_fecha
> end
>
> dump tran navy with no_log
>
>
>
> La estructura de mi tabla es:
> Name Owner Type Created_datetime
> nv_trama dbo user table 2003-09-06 08:24:59.153
>
>
>
> Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation
> w_fecha datetime no 8 no (n/a) (n/a)
> w_oper char no 1 no no no SQL_Latin1_General_CP1_CI_AS
> w_trama varchar no 2048 yes no no SQL_Latin1_General_CP1_CI_AS
>
>
> Identity Seed Increment Not For Replication
> No identity column defined.
>
>
> RowGuidCol
> No rowguidcol column defined.
>
>
> Data_located_on_filegroup
> PRIMARY
>
>
> index_name index_description index_keys
> IX_nv_trama clustered located on PRIMARY w_fecha
>
>
> No constraints have been defined for this object.
>
> No foreign keys reference this table.
> No views with schema binding reference this table.
>
>
> Y la tabla tiene 220.000