Solved

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

Posted on 2007-04-02
3
203 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

[Webinar] Disaster Recovery and Cloud Management

Learn from Unigma and CloudBerry industry veterans which providers are best for certain use cases and how to lower cloud costs, how to grow your Managed Services practice in IaaS clouds, and how to utilize public cloud for Disaster Recovery

Question has a verified solution.

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

Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now