Deleting records from a table that is actively written too..

Question

Post reply

Deleting records from a table that is actively written too..

hp_dba_uk

SSC-Addicted

Points: 421
More actions
August 7, 2012 at 6:36 am

#279336

Hi there,
We have a table where Application is logging. On average, roughly
800 rows are written to the table per minute so around 50,000 per hours.
Current size of the table is roughly around 18GB with over 60 million rows as its grown and now its taking too much of space. The table is actively written to so what's the best way to remove some of the old records without getting it locked down which in turn would break the Application as it wouldnt be able to write logs etc.
Any advise would be much appreciated. What's the best way to approach? I mean we can get developers to turn down loggin level required but then it has some useful info that would be lost when troubleshooting etc..
Thanks in Advance..

Viewing 6 posts - 1 through 5 (of 5 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

A sliding window partitioning strategy could help here.

Dan Guzman has a couple of nice posts on the subject:

http://weblogs.sqlteam.com/DANG/archive/2008/08/30/Sliding-Window-Table-Partitioning.aspx

http://weblogs.sqlteam.com/dang/archive/2008/09.aspx

Setting up the partition scheme and rebuilding your indexes on the partition scheme can be done online if you have Enterprise Edition.

Hope this helps

Gianluca

-- Gianluca Sartori

Lowell SSC Guru Points: 323518 More actions · Answer 2

Thanks for the Articles Gianluca!

good reading.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Gianluca Sartori (8/7/2012)
A sliding window partitioning strategy could help here.
Dan Guzman has a couple of nice posts on the subject:
http://weblogs.sqlteam.com/DANG/archive/2008/08/30/Sliding-Window-Table-Partitioning.aspx
http://weblogs.sqlteam.com/dang/archive/2008/09.aspx
Setting up the partition scheme and rebuilding your indexes on the partition scheme can be done online if you have Enterprise Edition.
Hope this helps
Gianluca

You need Enterprise Edition for table partitioning anyways.

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Lynn Pettis (8/7/2012)
Gianluca Sartori (8/7/2012)
A sliding window partitioning strategy could help here.
Dan Guzman has a couple of nice posts on the subject:
http://weblogs.sqlteam.com/DANG/archive/2008/08/30/Sliding-Window-Table-Partitioning.aspx
http://weblogs.sqlteam.com/dang/archive/2008/09.aspx
Setting up the partition scheme and rebuilding your indexes on the partition scheme can be done online if you have Enterprise Edition.
Hope this helps
Gianluca
You need Enterprise Edition for table partitioning anyways.

Oh, sure. This is something I didn't think about. :blush:

-- Gianluca Sartori

hp_dba_uk SSC-Addicted Points: 421 More actions · Answer 5

Hi all,

Thanks for your support and useful links.

We are using enterprise edition. Just for your info we found some logic issues with Application side. It was using log4j loggin levels in wrong order hence loggin at wrong levels e.g. INFO instead of ERROR.

We have increased space on the drive (vmware) that was running out of space due to this, whiile we clear the table to minimum. This is where we may use the links given.

So thanks all.

Cheers