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 |
|
lazerath
Constraint Violating Yak Guru
343 Posts |
 Posted - 2005-05-11 : 14:04:13
|
| Alright, I have a pretty big database (70GB) that has a loading/staging table which accounts for the majority of the size. My intentions are to move that table to a separate database at some time in the future, however I needed to free some space so I decided to archive the lion's share of the data in that table.To that effect, I created a DTS package to export all of the data older than one month to CSV and then delete it from the table. We have a pretty beefy server and I have a separate 273 GB RAID 5 array for logs only. Since it has the most free space (200 GB), I chose to export my data to a folder on that drive.I scheduled the job for 9 PM CST last night, and low and behold the job is still running.Well, the file is 83 GB uncompressed and finished exporting at 2 AM. The big problem is the log; it's chewed up the remaining space on the drive. When I came in this morning, I quickly started compressing the folder the big CSV file is in and its freeing space in a steady stream (about .5 GB a minute). For now, that's outpacing the log growth, but soon that file will be as compressed as its gonna get.I changed the Recovery Model to Simple in hopes it would stop the wild kudzu from overcoming my poor server, however that seems to have done nothing.In retrospect, I should have split this task into much smaller steps, but what do I do now?!?Any help is appreciated! |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2005-05-11 : 14:58:19
|
| I would have.1.bcp out the data using Queryout2. Copy the data to a new table without the bcped data using minimally logged operation like SELECT * INTO newTable FROM OldTable3. Audit the New table4. Drop the old table5. Rename the newtable to the oldtable nameHow do you do backups?When's the last full backup?Brett8-) |
 |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-05-11 : 15:13:51
|
| Full backups nightly, transaction log backups every 3 hours during the day.I cancelled my job, but I still have big problems. My compression stopped and my log filled the drive. Right now, the log is freeing space at a snails pace... its like 30 MB a minute. I'm doing a backup of the 215 GB log using Litespeed, but its going on 3 hours.Meanwhile, despite the fact that its reporting 200 MB free, DML commands are failing with "Log is full".This is a production database. Bad things... |
|
|
|
|
|
|
|
|
70GB Database, 215 GB Transaction Log!