Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Deleting records from a table that is actively written too.. Expand / Collapse
Author
Message
Posted Tuesday, August 7, 2012 6:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 3, 2012 8:04 PM
Points: 37, Visits: 123
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..
Post #1341194
Posted Tuesday, August 7, 2012 7:27 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 4,654, Visits: 11,114
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

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1341240
Posted Tuesday, August 7, 2012 1:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:23 AM
Points: 12,953, Visits: 32,478
Thanks for the Articles Gianluca!
good reading.


Lowell

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1341491
Posted Tuesday, August 7, 2012 1:16 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 6:11 AM
Points: 20,857, Visits: 32,873
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1341496
Posted Wednesday, August 8, 2012 1:50 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 6:22 AM
Points: 4,654, Visits: 11,114
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.


--
Gianluca Sartori

How to post T-SQL questions
spaghettidba.com
@spaghettidba
Post #1341711
Posted Wednesday, August 8, 2012 3:02 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Saturday, November 3, 2012 8:04 PM
Points: 37, Visits: 123
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



Post #1341741
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse