deleting records without writing to transaction log
-
Is there any way to delete records from a table without writing to the sql server transaction log? I don't want to delete all the records in the table, only those created prior to a specified date. I'm deleting a lot of records and the log is filling up.
Thanks for any help.
Elaine
-
Depending on how many records you want to save it might be faster to do a
SELECT *
INTO NewTableName
FROM TableName
WHERE DateField > DateParameter
GO
Truncate Table TableName
GO
INSERT INTO TableName
SELECT *
FROM NewTableName
Just my $0.02.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
You could also set the recovery mode to simple so the log takes less space during this operation.
-
Thank you. The recovery mode is set to simple, but it's still filling up. I will probably try Jim P's suggestion to save off the rows I want to keep, truncate the table and then insert the saved rows back. I will end up saving less rows than I want to delete, so that is probably the best way to do it. I was being lazy and hoping that there was a way to just not write to the transaction log.
Thanks again.
-
Can you imagine the implications of that possibility?
-
It sounds like you're overunning your checkpoints. Maybe try:
SELECT YEAR(Datefield) MONTH(Datefield), MAX(Datefield)
FROM TableName
GROUP BY YEAR(Datefield) MONTH(Datefield)
ORDER BY YEAR(Datefield) MONTH(Datefield)
Then take the results and do search and replace:
DELETE FROM TableName
WHERE Datefield < "Your results MAX(Datefield)"
GO
That should also solve the problem, by deleting smaller chunks.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Remi: You're right, how would systems function if anyone could turn off logging? It would be like driving while talking on the cell phone; I know I can do it safely, but those other folks...
There is no "i" in team, but idiot has two. -
Setting the recovery mode to simple would not make any difference here. That only lets SQL Server delete the log records before the MinLSN after a checkpoint is made. The problem here seems to be that the transaction is so large that the log runs out of space before the transaction is committed (which in turn would let SQL Server truncate the log). Jim P's solution(s) should solve the problem.
-
Elaine, if there are no Foreign Key / Primary Key relationships on the table you can use the
TRUNCATE TABLE myTableName
command. It deletes ALL the data in the table without using the log file, regardless of recovery mode.
It just unlinks the data pages, and takes only a few seconds for tables of millions of rows.
Julian Kuiters
juliankuiters.id.au -
It deletes ALL the data in the table without using the log file, regardless of recovery mode.
Slightly incorrect. TRUNCATE is minimally logged. The deallocation of the datapages actually is recorded in the log. You can also use TRUNCATE within a transaction, and if it is rolled-back, the datapages are reallocated.
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
If the Recovery Mode is set to Simple, as you say it is, turn on the TRUNCATE LOG ON CHECKPOINT option and delete in smaller chunks. You may have to do a Backup Log With Truncate_Only at the end of each sectional delete, as well, but understand that both actions do put your database at risk if the lights go out before your next backup.
...OR...
One previous post made the suggestion of copying only the good stuff to another table. If you set the Recovery Mode to Bulk Logged and do the copy to a new table using SELECT/INTO, then add the indexes and constraints, rename the old table to something else, and rename the new table to what the original table was, the log won't see more than a hiccup. Side benefit... if the table you are doing this to is in use, your users will only see a 30 millisecond disturbance.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thank you all for your advice. Part of the problem is that we ran out of disk space, so I don't have enough room to save to another table the data I need to keep. I'm going to delete in small increments until enough space is free for me to follow Jim's advice to save what I want to keep to another table and then truncate the original table. It's a reporting table that no users hit directly, so nobody will be affected until the reporting job runs. I really appreciate all of your responses. Thanks again.
Elaine
-
Correct me if I'm wrong...
If you delete a record using the query tool in Enterprise Manager (NOT Query Analyzer) I believe its not logged. Thats why its so dangerous to mess with data using this too.
-Isaiah -
>> Correct me if I'm wrong...
If you delete a record using the query tool in Enterprise Manager (NOT Query Analyzer) I believe its not logged. Thats why its so dangerous to mess with data using this too.<<
Yep you are not right. It does not depend on the tool. All "delete" operations are logged and in the end EM uses "delete" too !
* Noel -
Thank god... I was hopping he was wrong cause a few of my backups would have been wrong :-).
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply