Delete nologging

Question

Post reply

Delete nologging

krishna83

Ten Centuries

Points: 1003
More actions
May 18, 2017 at 2:06 am

#323554

Hi,
How to ran a delete statement without entering log into .ldf file.
Thank & Regards
Krishna.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 1

It's not possible and why would you want to? Are you trying to prevent your log file from growing?

Thanks

krishna83 Ten Centuries Points: 1003 More actions · Answer 2

Yes, We are executing a delete command on a table which contains nearly 4 core records.

Thanks & Regards
Krishna.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 3

There is no way to run a delete statement without it being logged. What is your recovery model? How many rows are you deleting? How much space you have on the disk where your log file is located?

You could always run the delete statement in batches, increase the frequency of log backups or temporarily change the recovery model to prevent excessive log growth.

Thanks

krishna83 Ten Centuries Points: 1003 More actions · Answer 4

Here,Database recovery model is full.

Is there any chance to improve performance , if we put recovery model as simple.

Free space on disk is 400GB.

Thanks & Regards
Krishjna

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 5

No there won't be a performance improvement in simple mode as the log file is still is use but will get marked for re-use. How many rows are you deleting and would it be possible to split the delete into batches?
It seems as though you have plenty space available on the disk although I don't know how big your database is so why are you concerned about the size of the log file?

Thanks

Thomas Rushton SSC-Insane Points: 22648 More actions · Answer 6

If you want to improve your performance on this sort of query, then there are a few options:

(1) Are you deleting all the data from the table? If so, then use TRUNCATE rather than DELETE - much quicker.
(2) Are you deleting most of the data from the table? If so, then it might be more sensible to SELECT the data you want to keep into a different table, TRUNCATE the original, and then move the data back in. Or DROP the original and RENAME the new table

Otherwise:

(3) Delete in batches. Say, DELETE TOP 100000 FROM ... and repeat until @@rowcount is zero. This will reduce the impact on the server
(4) Partition the data with a partition scheme that matches your deletion criteria, then delete that partition. This will take time to set up, though.

Thomas Rushton
blog: https://thelonedba.wordpress.com

krishna83 Ten Centuries Points: 1003 More actions · Answer 7

Hi,

2 option suitable to my requirement.
We have auto increment column in the Orginal table it might become issue while moving data to back.

Thanks
Krishna.

NorthernSoul SSCertifiable Points: 6869 More actions · Answer 8

From what I recall (feel free to correct me anyone) but a truncate will reseed any identity column so you will need to bear this in mind.

Thanks

Perry Whittle SSC Guru Points: 233854 More actions · Answer 9

NorthernSoul - Thursday, May 18, 2017 5:08 AM
From what I recall (feel free to correct me anyone) but a truncate will reseed any identity column so you will need to bear this in mind.
Thanks

That is correct, it will reseed any identity, assuming of course the user truncating the table has the permission to do so

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 10

2 option suitable to my requirement.
We have auto increment column in the Orginal table it might become issue while moving data to back.

When you insert the copied rows back into the original table, use SET IDENTITY_INSERT <YourTableName> ON this will allow you to preserve the values.