Deleteing rows

I have to delete 30K rows that were inserted into the orders table by
mistake.
The orderid field is used as a FK on many other tables so the delete takes
forever.
Is there any way to increase the performance of this delete?
In this case I know there will not be any associated FKs that reference
these orderids in other tables because the insert I am seeking to undo was
made only to the orders table. Therefore no orphans will be produced (i.e.,
no RI violation) upon delete.
Is it possible to perform the delete without constraint checking? If not,
is there anything I can do to speed up the process?
ThanksDo you have indexes on the FK columns in the referencing tables? That can
speed up such an operation significantly.
You can disable the FK constraint (see ALTER TABLE), but that disabling
applies for all connections, so make sure you are alone on the database
while doing so, if you want to take that route.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"David F" <davef@.nksj.ru> wrote in message
news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> I have to delete 30K rows that were inserted into the orders table by
> mistake.
> The orderid field is used as a FK on many other tables so the delete
takes
> forever.
> Is there any way to increase the performance of this delete?
> In this case I know there will not be any associated FKs that reference
> these orderids in other tables because the insert I am seeking to undo was
> made only to the orders table. Therefore no orphans will be produced
(i.e.,
> no RI violation) upon delete.
> Is it possible to perform the delete without constraint checking? If not,
> is there anything I can do to speed up the process?
> Thanks
>|||Thanks Tibor.
So it looks like I will not have to DROP the FK, just disable and then
reenable like:
--disable FK
ALTER TABLE child NOCHECK CONSTRAINT fk_id
--re-enable FK
ALTER TABLE child CHECK CONSTRAINT fk_id
Thanks
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> Do you have indexes on the FK columns in the referencing tables? That can
> speed up such an operation significantly.
> You can disable the FK constraint (see ALTER TABLE), but that disabling
> applies for all connections, so make sure you are alone on the database
> while doing so, if you want to take that route.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "David F" <davef@.nksj.ru> wrote in message
> news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > I have to delete 30K rows that were inserted into the orders table by
> > mistake.
> >
> > The orderid field is used as a FK on many other tables so the delete
> takes
> > forever.
> >
> > Is there any way to increase the performance of this delete?
> >
> > In this case I know there will not be any associated FKs that reference
> > these orderids in other tables because the insert I am seeking to undo
was
> > made only to the orders table. Therefore no orphans will be produced
> (i.e.,
> > no RI violation) upon delete.
> >
> > Is it possible to perform the delete without constraint checking? If
not,
> > is there anything I can do to speed up the process?
> >
> > Thanks
> >
> >
>|||Yes, but did you check the indexes first? Having indexes on a FK column is
often crucial for reasonable performance when doing update and delete in the
referenced table. And not only that, these indexes can help your join
operations significantly (you often join over primary key - foreign key
relationships).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Dave" <dave@.nodomain.tv> wrote in message
news:eZjbGj6AEHA.3256@.TK2MSFTNGP09.phx.gbl...
> Thanks Tibor.
> So it looks like I will not have to DROP the FK, just disable and then
> reenable like:
> --disable FK
> ALTER TABLE child NOCHECK CONSTRAINT fk_id
> --re-enable FK
> ALTER TABLE child CHECK CONSTRAINT fk_id
> Thanks
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
> message news:OTRoxf5AEHA.1028@.TK2MSFTNGP11.phx.gbl...
> > Do you have indexes on the FK columns in the referencing tables? That
can
> > speed up such an operation significantly.
> >
> > You can disable the FK constraint (see ALTER TABLE), but that disabling
> > applies for all connections, so make sure you are alone on the database
> > while doing so, if you want to take that route.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "David F" <davef@.nksj.ru> wrote in message
> > news:uS8KQd5AEHA.3348@.TK2MSFTNGP11.phx.gbl...
> > > I have to delete 30K rows that were inserted into the orders table by
> > > mistake.
> > >
> > > The orderid field is used as a FK on many other tables so the delete
> > takes
> > > forever.
> > >
> > > Is there any way to increase the performance of this delete?
> > >
> > > In this case I know there will not be any associated FKs that
reference
> > > these orderids in other tables because the insert I am seeking to undo
> was
> > > made only to the orders table. Therefore no orphans will be produced
> > (i.e.,
> > > no RI violation) upon delete.
> > >
> > > Is it possible to perform the delete without constraint checking? If
> not,
> > > is there anything I can do to speed up the process?
> > >
> > > Thanks
> > >
> > >
> >
> >
>

Deleted table.

Hi all,

I'm struck, i'm working on a production support by mistake i deleted a table without a transaction, is there anyway i could retrieve the records that got deleted. :(

Thanks in advance.

Arun

restore from backup to another database and copy the records over.

Good luck|||

Is there anyway by which i can retrieve it from transaction log.

|||

Possibly, if you haven't backed-up the transaction log since the deletion and if your database's recovery model is not 'Simple'.

One of these tools should be able to help:

http://www.red-gate.com/products/sql_log_rescue/index.htm

http://www.lumigent.com/products/le_sql.html

For future reference, it's a good idea to execute BEGIN TRANSACTION before making manual corrections to Production data - that way you can always ROLLBACK if your code doesn't behave as expected.

Chris

|||If you can get it from a backup, I would highly recommend that. I know it stinks, but it will be the most straightforward (and the most supported :)

Deleted "old" transaction logs = (no items) in EM. How to recover?

A mistake was made and "old" transaction logs (and old backups) were
deleted. It appears that a long living transaction was living in one of
those logs. Now the EP shows (no items). The DBs themselves are still
functioning OK, just the EP shell that's not working.

What's the proper resolution?

Thank you in advance,

FBCK(optimistck@.gmail.com) writes:
> A mistake was made and "old" transaction logs (and old backups) were
> deleted. It appears that a long living transaction was living in one of
> those logs. Now the EP shows (no items). The DBs themselves are still
> functioning OK, just the EP shell that's not working.
> What's the proper resolution?

EP?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Delete Non System Table

Bu Mistake our DTS has selected the Masters Database while performing a Data Transfer

can you please suggest how do I delete Non-System Tables from the masters database.

Your help is appriciatedin sysobjects table looking for "U" in xtype column (U: user table, S: system table).