Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2007-04-02
3
Medium Priority
?
247 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 1000 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 1000 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

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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.

636 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