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 |
|
MichaelP
Jedi Yak
2489 Posts |
 Posted - 2002-04-03 : 14:34:51
|
| I have a 8GB database (one table, all the date is in that table). What I need to do is somehow save that data someplace else, and then delete all of the records in that table. Data is constantly being written to this table, but not I'm too concerned if I lose some data. Suggestions?I'm thinking I can create a new blank DB, do a detach_db on the 8gb db, and then attach the new blank db. Then, I could copy the 8gn db and do whatever I needed with that. If I do the detach / attach, will it be basically seemless? IIS is logging to this table, so if it's unavailable for a second, will it kill IIS? Ok, lots of questions, sory about that all! Any help would be great!Michael |
|
|
Jay99
468 Posts |
Posted - 2002-04-03 : 14:41:58
|
| Is your problem that you don't want the movement of the data logged? Just set the recover model to simple on your destinatino database and theninsert destination.owner.objectselect fieldlist from source.owner.objector maybe bcp out to a file and bcp into the destination . . . I don't understand where you stuck . . .I am pretty sure that you can't re-attach with a new name, so if you go the detach/attach method, you'll have to have a different instance for you destination.Jay<O> |
 |
|
|
yakoo
Constraint Violating Yak Guru
312 Posts |
Posted - 2002-04-03 : 14:55:50
|
| with enterprise manager you could export your 8GB table to a new database [Export Data.. Choose source and destination and then choose 'copy SQL Server Objects and Data']then truncate your 8GB table in your main database.You could also create a DTS package that would do this for you, and then you could schedule that DTS package to run every week if you wanted to. |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-04-03 : 14:59:30
|
| Really where I'm stuck is that I'd love to do it your way jay, I'd haev to create this new destination DB on another box, because we are nearly out of disk space. If I run your solution (which I think I am at this point), the destination DB will need to be located on a different physical disk (probably on a differnt PC). This INSERT INTO...SELECT should generate MUCH network traffic and load on my SQL Server, which I can't really do until after hours. I need a solution that uses less than 1GB of HDD storage, no network traffic, and can be done while everything (SQL, Webservers, etc) is still running. I also need a car that gets 1000 miles to a gallon and runs on water. :)Thx for the suggestion Jay, I didn't know about the Recovery Model.Michael |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-03 : 15:10:32
|
| Assuming you have no non-clustered indexes, the only way to get your 8 gig down to 1 gig is to export it to text and use a 3rd party compression tool to squeeze it . . . you may or may not get under a gig. You'll need 8 gigs for the exported-pre-compression data too. Oh, and if you want to acces the data, you'll have to un-compress it . . .If you have 7.5 gigs worth of non-clustered indexes, on the other hand, you'll be in fine shape :)Jay<O> |
|
|
|
MichaelP
Jedi Yak
2489 Posts |
Posted - 2002-04-03 : 16:02:33
|
| I think I'm going with the INSERT INTO SELECT and Recovery Mode Simple solution.The Next question os how to take the exising DB (8GB) and make it MUCH smaller (more liek 1GB) since it is now basically empty? I'm looking in the BOL for help on this. I'm guessing it's a DBCC command or something. |
|
|
|
Jay99
468 Posts |
Posted - 2002-04-03 : 16:26:19
|
| Look up "Shrinking databases" in BOL . . . DBCC SHRINKDATABASE will prolly do the trick for you.Jay<O> |
|
|
|
|
|
|
|
|
Export data from DB, then clean the table