Solved

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

Posted on 2007-04-02
3
238 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how the fundamental information of how to create a table.

707 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