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 |
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
 Posted - 2007-01-10 : 21:17:07
|
| okay the tables we have are becomming way out of hand and we need to figure out how to sort them:problem:9 tables have more than 10 million recordsI propose:we just archive old records from the tableis that the best solution? (which happens to be the easiest) |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-11 : 03:28:40
|
| "we just archive old records from the table"Depends a bit how you are going to do the archiving.INSERT INTO MyArchiveTableSELECT *FROM MyLiveTableWHERE SomeDate < DATEADD(Month, -1, GetDate())DELETEFROM MyLiveTableWHERE SomeDate < DATEADD(Month, -1, GetDate())is probably going to have a hugely disproportionate impact on the Transaction Logs, and may well block all access to the database for some considerable time.We archive about 2,000,000 rows a day from our database. It uses lots of careful programming to reduce the impact and not skew the normal behaviour of the database.Kristen |
 |
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2007-01-11 : 04:23:21
|
| kristen,in our archiving process, we transfer data from one database to another... we actually create a new database which we can archived database... we're currently using the BCP utility to transfer data...SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2007-01-11 : 04:25:48
|
quote:
Originally posted by Kristen
"we just archive old records from the table".......INSERT INTO MyArchiveTableSELECT *FROM MyLiveTableWHERE SomeDate < DATEADD(Month, -1, GetDate())DELETEFROM MyLiveTableWHERE SomeDate < DATEADD(Month, -1, GetDate())....
isn't this going to bloat the LDF file??? what's the best way to archive a database???SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-12 : 00:49:06
|
"isn't this going to bloat the LDF file???"Yeah, that's what I said: "is probably going to have a hugely disproportionate impact on the Transaction Logs""what's the best way to archive a database???"The whole database? Store the backups.Just some data within the database? ... it all depends ...  Kristen |
|
|
|
mightypenny_ph
Yak Posting Veteran
54 Posts |
Posted - 2007-01-12 : 04:10:31
|
| yup... the whole database... :( creating replica and etc stuff... how do you go around your archiving procedure???SlayerS_`BoxeR` + [ReD]NaDa |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-12 : 04:23:00
|
| "how do you go around your archiving procedure"We export the data with BCP and then delete it. The Export has no logging consequence, but the Delete does of course! The delete is in a loop which dynamically adjusts the number of rows deleted according to how long the previous loop iteration took - so if the server gets busy the delete does fewer rows per iteration to lighten the load.We very rarely need the data back again, so we just leave it in BCP files. If we do need it we import it back into a temporary database or somesuch to query.But that isn't a whole database solution ...We store our TLog backups for 7 days, our daily backups for 4 weeks, and our Sunday backups forever. That gives us the ability to go back a long way in time, but only to within a weekKristen |
|
|
|
rnbguy
Constraint Violating Yak Guru
293 Posts |
Posted - 2007-01-15 : 00:53:28
|
| why BCP why not just do a full backup? |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-01-20 : 08:49:57
|
| Its quicker for us to reimport via BCP than have to restore a (massive) database backup (from a tape probably stored off-site by then, but either way with significant delays to retrieve it), and then have the housekeeping of dropping the temporary database.Kristen |
|
|
|
|
|
|
|
|
database size issue