DELETE without transaction?

Hi,
I have a database with about 50 GB - most of the data in one single table
with only three fields. I need to clean up this table frequently (executing
DELETE stantements). My problem is that this locks the table so that BULK
INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
and more) while deleting the data.
Is there a way to delete without using the transaction log and while doing a
bulk insert?
CU,
SvenHi,
I think TRUNCATE TABLE is what you are looking for.
I'm not sure you can do anything else while truncating, but
truncate is very fast.
Check BOL for further information.
Ciao,
Cosmin.
"Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I have a database with about 50 GB - most of the data in one single table
> with only three fields. I need to clean up this table frequently
(executing
> DELETE stantements). My problem is that this locks the table so that BULK
> INSERT jobs cannot execute. Also the transaction lok fills up a lot (15 GB
> and more) while deleting the data.
> Is there a way to delete without using the transaction log and while doing
a
> bulk insert?
> CU,
> Sven
>|||truncate table is a good way of cleaning out the entire
table, assuming there are no foreign key constraints
for deleting large number of rows, try doing it in small
batches, say 1000-10,000 rows at a time, adjust the row
count to keep the batch run time sufficiently short
>--Original Message--
>Hi,
>I have a database with about 50 GB - most of the data in
one single table
>with only three fields. I need to clean up this table
frequently (executing
>DELETE stantements). My problem is that this locks the
table so that BULK
>INSERT jobs cannot execute. Also the transaction lok
fills up a lot (15 GB
>and more) while deleting the data.
>Is there a way to delete without using the transaction
log and while doing a
>bulk insert?
>CU,
>Sven
>
>.
>|||The problem is that I need a where statement - this is not supported by
TRUNCATE TABLE (that's one reason, why it's so fast).
"Cosmin" <cosmin.onea@.infoworld.ro> wrote in message
news:O1NbTJyYDHA.2476@.tk2msftngp13.phx.gbl...
> Hi,
> I think TRUNCATE TABLE is what you are looking for.
> I'm not sure you can do anything else while truncating, but
> truncate is very fast.
> Check BOL for further information.
> Ciao,
> Cosmin.
> "Sven Erik Matzen" <sven.matzen@.dontspamme.com> wrote in message
> news:editI8xYDHA.1004@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> >
> > I have a database with about 50 GB - most of the data in one single
table
> > with only three fields. I need to clean up this table frequently
> (executing
> > DELETE stantements). My problem is that this locks the table so that
BULK
> > INSERT jobs cannot execute. Also the transaction lok fills up a lot (15
GB
> > and more) while deleting the data.
> >
> > Is there a way to delete without using the transaction log and while
doing
> a
> > bulk insert?
> >
> > CU,
> > Sven
> >
> >
>

Delete syntax to delete a record from one table if a matching value isn't found in another

I'm trying to clean up a database design and I'm in a situation to where two tables need a FK but since it didn't exist before there are orphaned records.

Tables are:

Brokers and it's PK is BID

The 2nd table is Broker_Rates which also has a BID table.

I'm trying to figure out a t-sql statement that will parse through all the recrods in the Broker_Rates table and delete the record if there isn't a match for the BID record in the brokers table.

I know this isn't correct syntax but should hopefully clear up what I'm asking

DELETE FROM Broker_Rates

WHERE (Broker_Rates.BID <> Broker.BID)

Thanks

kfrost:

Maybe something like:

DELETE FROM Broker_Rates
from Broker_Rates a
WHERE not exists
( select 0 from Broker b
where a.BID = B.id
)

Dave

|||

That appeared to do the trick. Curious, what does using the 0 in Select 0 person of your string above accomplish. I was using Select *.

Works, just curious for future reference.

Thanks.

|||

I use the zero because the select list is not relevant. Your syntax will work fine. I need to do something like "0 as dummy" so that it is more obvious that the item is a dummy item.

Dave

|||Cool. Thanks!!

Delete Lock

Hi,
I'm trying to clean up some old mess. We've got an sql server that keeps
reporting deadlocks. There is a loging application that keeps inserting
and deleting records from a table. The delete statement will always delete
only 1 row. The table is without primary keys and indexes. My question is
this: How will the delete statement lock the rows ? Will it apply a
rowlock, pagelock or table lock ?
Using Opera's revolutionary e-mail client: http://www.opera.com/m2/If the table does not have primary key or indexes, then sql server have to d
o
a table scan to find the row and may be it is scalating the lock. See "Lock
Escalation" in BOL. If possible, create a pk.
AMB
"kfu" wrote:

> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/
>|||Who is blocking who and under what circumstances. Even a query will acquire
shared locks, meaning that a long running query can block an
insert/update/delete for the duration of it's select or an i/u/d can block a
query for the duration of it's transaction. Read up on options for "set
transaction isolation level". Perhaps allowing queries to the log table to
read uncommitted will help resolve you issue. Also, if you are logging
things like user activity (for example logging the last time a record was
viewed by the user), then do not do this in a fact table, instead use a
seperate log table just for that purpose.
"kfu" <kfurnes@.netcom.no> wrote in message
news:op.sraov9ia0gg7rt@.kjafur2.sense.sensetech.no...
> Hi,
> I'm trying to clean up some old mess. We've got an sql server that keeps
> reporting deadlocks. There is a loging application that keeps inserting
> and deleting records from a table. The delete statement will always delete
> only 1 row. The table is without primary keys and indexes. My question is
> this: How will the delete statement lock the rows ? Will it apply a
> rowlock, pagelock or table lock ?
> --
> Using Opera's revolutionary e-mail client: http://www.opera.com/m2/