Wednesday, March 7, 2012

Delete Without Writing To Transaction Log

I am using a simple
DELETE
FROM tblMyTable
I was told the reason it takes 4.5 hours is because of writing to the
transaction log.
Can I add something to this statement to bypass the transaction log writing?
Thanks so much in advance.Truncate table tbl_MyTable.
Perayu

>I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
> writing?
> Thanks so much in advance.|||Thanks for your help.
It won't let me do that.
Server: Msg 4712, Level 16, State 1, Line 1
Cannot truncate table 'tblMyTable' because it is being referenced
by a FOREIGN KEY constraint.
"Perayu" wrote:

> Truncate table tbl_MyTable.
> Perayu
>
>
>|||How is your foreign key constaint set up? Do you want to delete from child
table as well? If so, drop the contraint first, truncate parent table. Delet
e
from child table by joining to parent table using Right OUTER JOIN clause.
Recreate the constraint.
Otherwise, spend 5 hours in wait until the transaction is fully committed.
Nitin
"alison" wrote:
> Thanks for your help.
> It won't let me do that.
> Server: Msg 4712, Level 16, State 1, Line 1
> Cannot truncate table 'tblMyTable' because it is being referenced
> by a FOREIGN KEY constraint.
> "Perayu" wrote:
>|||No, you have to write to the log? Who told you it takes 4.5 hours because
of this? Do you have evidence of this, or is someone just pulling a guess
out of their...back pocket :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"alison" <alison@.discussions.microsoft.com> wrote in message
news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
>I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
> writing?
> Thanks so much in advance.|||no, but you can use truncate table, which minimizes logging.
"alison" <alison@.discussions.microsoft.com> wrote in message
news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
> I am using a simple
> DELETE
> FROM tblMyTable
> I was told the reason it takes 4.5 hours is because of writing to the
> transaction log.
> Can I add something to this statement to bypass the transaction log
writing?
> Thanks so much in advance.|||I'm sorry if I'm being a pain. I'm new at this.
Yesterday I did a simple DELETE statement before I left the office.
When I came in this morning I saw that it ran for 4.5 hours. The conversion
on this table takes even longer.
I don't need this table to develop the reports, but it's in the conversion
.cmd script and I was told not to DROP it.
I do really appreciate everyone who's helped me so far. We're still throwing
ideas around here too.
"Louis Davidson" wrote:

> No, you have to write to the log? Who told you it takes 4.5 hours because
> of this? Do you have evidence of this, or is someone just pulling a guess
> out of their...back pocket :)
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "alison" <alison@.discussions.microsoft.com> wrote in message
> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
>
>|||Hi
Truncate can only minimize logging, contrary to popular myth that It avoids.
Still if you face problem with delete you can probably do one of the followi
ng
1)PIN TABLE TO MEMORY, If you have enough memory
2)SPREAD TABLE TO different file groups to achive maximum cpu optimality.
Regards
R.D
"Brian Selzer" wrote:

> no, but you can use truncate table, which minimizes logging.
> "alison" <alison@.discussions.microsoft.com> wrote in message
> news:5A0D2986-E67D-4C09-AE9B-90BA5D77EE4B@.microsoft.com...
> writing?
>
>|||I imagine if it took four hours that pinning the table to memory is probably
not the best idea unless they have all the data, logs, os, mp3s, wmvs, etc
on 1 500 GB drive :)
The second one is a good possibility, but not the first place to go.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"R.D" <RD@.discussions.microsoft.com> wrote in message
news:408CFD0E-7B28-456D-B6CF-0140F80495AD@.microsoft.com...
> Hi
> Truncate can only minimize logging, contrary to popular myth that It
> avoids.
> Still if you face problem with delete you can probably do one of the
> following
> 1)PIN TABLE TO MEMORY, If you have enough memory
> 2)SPREAD TABLE TO different file groups to achive maximum cpu optimality.
> Regards
> R.D
> "Brian Selzer" wrote:
>|||Is this a standalone table? No indexes, no keys, no triggers, etc? If so,
then use TRUNCATE TABLE for sure.
Either way, open up perfmon and watch the Disk Queue items (amongst others),
and see if you have a lot of queuing, and check CPU, etc. You may have
really slow disk performance. It is hard to tell without doing more
research.
But if TRUNCATE works, then this is the best idea. For loading the data,
consider dropping any indexes if no one is using the table during thr
conversion, building the indexes once is cheaper than maintaining them
during a large load.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"alison" <alison@.discussions.microsoft.com> wrote in message
news:77DD37EE-C6BC-4E0E-A28F-21524ED8429E@.microsoft.com...
> I'm sorry if I'm being a pain. I'm new at this.
> Yesterday I did a simple DELETE statement before I left the office.
> When I came in this morning I saw that it ran for 4.5 hours. The
> conversion
> on this table takes even longer.
> I don't need this table to develop the reports, but it's in the conversion
> .cmd script and I was told not to DROP it.
> I do really appreciate everyone who's helped me so far. We're still
> throwing
> ideas around here too.
> "Louis Davidson" wrote:
>

No comments:

Post a Comment