Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Aandrew
Starting Member
5 Posts |
 Posted - 2007-07-13 : 04:53:51
|
| The company I work for has a number of different PoS applications, one of which produces large amounts of data relating to sales transactions and statistics relating to the transactions.One of our customers is having problems recently as the database size has grown in excess of 100gb and Ive been given the task of clearing it down. Theres probably about 50 tables for which Im going to have to remove all but the last 3 months worth of data. As this is a live server they have concerns about locking etc while this cleardown is taking place.So my question is, are there any special considerations when deleting large amounts of data like this or is it really just a simple case of 'DELETE FROM WHERE' ?? |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-07-13 : 05:13:12
|
| You don't like filling you log.SET ROWCOUNT 10000SELECT 1 AS DummyWHILE @@ROWCOUNT > 0 DELETE FROM Table1 WHERE ...SET ROWCOUNT 0Peter LarssonHelsingborg, Sweden |
 |
|
|
jezemine
Master Smack Fu Yak Hacker
2886 Posts |
Posted - 2007-07-13 : 09:01:07
|
another option (won't work for you in this case though, but good to be aware of for some other time perhaps) is to copy out only the data you want to keep to a staging table, truncate the table, and then copy it back. this works well only if:1. you are keeping only a small fraction of the table, as it sounds like you are. 2. you don't need the data to be available throughout the delete processso you can't use this option because of #2.
elsasoft.org |
|
|
|
Aandrew
Starting Member
5 Posts |
Posted - 2007-07-16 : 12:20:21
|
| In the end I went for the ROWCOUNT method. I did a bit of reading up on it and now have a solution which seems to be working really well, so thanks for that. Ive use this for clearing down the statistical data.My one remaining problem is how to perform a bulk deletion of the transaction data which is stored in two separate tables containing header and position data. I need to clear them both down keeping N days worth of data but only the header table actually stores the transaction date.I need to make sure that both header and position records are deleted and that no spares get left behind, would this involve using a transaction? |
|
|
|
|
|
|
Deleting Large Amounts of Data