Nibbling Deletes

Nibbling Deletes
6

- Details

Have large tables full of millions of rows where you need to delete data on a periodic basis without impacting write operations? Then check out this video which will show you how to tackle the problem using nibbling deletes.

This Video Covers

The SQL Server Storage Engine, and T-SQL tips and tricks.

Details

Video Length: 09:19
Skill Level: 300 - Advanced
Series: Tips and Tricks
Author/Presenter: Michael K. Campbell
Applies to SQL Server: 2000, 2005, and 2008

Tags: Best Practices, Maintenance, T-SQL, and Storage Engine

    Applicability

Analyst 0
DBA 60
Developer 20
Manager 20

 

- Downloads

SQL Server Consulting

+ Related Videos

+ Related Resources

+ Transcript

- Comments

Nibbling Deletes. I did not see where you address space requirements when doing large deletes during a archive process. The Transaction Log can grow considerably if not backed up often enough so a addon to your code could include within the loop or another outside loop a step that takes a tlog backup every so often to keep the tlog in check. Can you give an example of this in your code example and video? Putting the db into SIMPLE recovery mode during this operation is not normally a good idea.

- September 16, 2009.

Great point - if you're deleting large amounts of data, the log file WILL grow quite a bit.

But rather than manually backing up your log file as part of this process, I'd recommend just ensuring that you're regularly backing up your log file, making sure you've got enough space to handle a larger operation, and then just keeping an eye on the operation as you go. (Start by making a small number of deletes until you've got a good feel for what kinds of log space this will require.)

And yes, switching your DB into SIMPLE Recovery would typically be a bad idea.

Michael K. Campbell - September 16, 2009.

instead of using

DELETE FROM LorryLocations
WHERE locationId IN (
SELECT TOP (@count) locationId
FROM LorryLocations WITH(NOLOCK)
WHERE
[timestamp] < @cutoff
)

why not use

DELETE TOP(@count) FROM LorryLocations
WHERE [timestamp] < @cutoff
)


And instead taking log backup why not shrink the log file

Please correct me if i m wrong

Thanks

- February 09, 2010.

Hi Iam Prabhu from chennai,joined today in this forum... :)

accurapertcox - September 10, 2010.

Ya learn something new everaydy. It's true I guess!

Rybon - August 17, 2012.

Great video. I got it!. Useful and informative. Thank you very much this helped me solve an issue.

Andyf - October 26, 2012.

Add A Comment

 

NOTE: Comments are moderated.

The following pseudo-markup is permitted:
      bold : text inside *stars* will be bold
      italic : text inside _underscores_ will be emphasized
      hyperlinks : [linktext|http://link.url.here]