Solved

How to delete records from a table without logging (but with a where clause)

Posted on 2007-04-02
3
231 Views
Last Modified: 2010-05-18
I have a table with 40 million rows in it. For a test and development server, i want to delete most of them, so the server gets faster and the backupfiles are smaller. But issuing something like 'delete from mytable where date < '2007.01.01' takes AGES (45 mins or more). I think, one reason is because of the logging that SQL server does.
Is there a way to delete records without logging the deletes? Truncate table will not work as i do want to keep a portion of the records.

0
Comment
Question by:dready
3 Comments
 
LVL 10

Accepted Solution

by:
lahousden earned 250 total points
ID: 18835985
<<Is there a way to delete records without logging the deletes?>> - not a selective way.  Your best bet is probably to copy off the rows you want to keep to a temporary table, truncate the original table and then copy the preserved rows back.
0
 
LVL 18

Assisted Solution

by:Sham Haque
Sham Haque earned 250 total points
ID: 18835990
do a SELECT INTO for the data you want to keep (populates a new table) and do a TRUNCATE on your current table
0
 
LVL 23

Expert Comment

by:Racim BOUDJAKDJI
ID: 18836190
<<Your best bet is probably to copy off the rows you want to keep to a temporary table, truncate the original table and then copy the preserved rows back.>>
On the same idea:

> get the records you want to keep in a table2. (select into)
> drop oldtable
> rename table2 to oldtable name

That will simply the time to select into...Hope this helps...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Select - Help finding duplicate records 5 21
Need help with a query 14 39
Converting Stored Procedure to SQL Statement 5 44
Related to SQL Query 5 21
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question