Hi Freinds,
SQL 2000
I have to delete 8,000,000 record from a teble which has 45,000,000 records.
There are index files also with that table.
Is there any option to delete with no logging? I already changed my SQL
DATABASE type to simple, but again it take long time to delete 8000000
records, just because it is logging it.
Thanks,
PatDrop all of the foreign key references to the table, use Truncate Table
<tableName> and then re-create the references.
Thomas
"Patrick" <patriarck@.gmail.com> wrote in message
news:ekP8eaoWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have to delete 8,000,000 record from a teble which has 45,000,000 record
s.
> There are index files also with that table.
> Is there any option to delete with no logging? I already changed my SQL
> DATABASE type to simple, but again it take long time to delete 8000000
> records, just because it is logging it.
> Thanks,
> Pat
>|||Patrick
Divide the transaction into small ones
SET ROWCOUNT 10000 --Instead of 8000000
WHILE 1 = 1
BEGIN
--DELETE FROM Table WHERE .......
IF @.@.ROWCOUNT = 0
BEGIN
BREAK
END
END
SET ROWCOUNT 0
"Patrick" <patriarck@.gmail.com> wrote in message
news:ekP8eaoWFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Hi Freinds,
> SQL 2000
> I have to delete 8,000,000 record from a teble which has 45,000,000
records.
> There are index files also with that table.
> Is there any option to delete with no logging? I already changed my SQL
> DATABASE type to simple, but again it take long time to delete 8000000
> records, just because it is logging it.
> Thanks,
> Pat
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment