an intersting sql problem
-
You hava a table containing about 1000000 records.Now you want to delete 950000 records and only left 50000 records in the table. As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)? many thanks
richardye wrote:
As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)?
What is going to happen when you delete all of those records is that the transaction log is going to grow. Regarding disk space(I take it you mean disk space rather than memory?): Backup the transaction log to clear it out of pending transactions first. You will then need to shrink the transaction log. Run your script to delete the records. Then straightaway backup the transaction log and shrink it again. This is the only way I can think of to keep disk space usage at a minimum. Regarding speed: All I can suggest is that you do all of this out of hours when nobody else is using the database. If anybody else is accessing the table you are deleting from there is always the chance that you could get a lock, for a period of time, or even a deadlock.
You always pass failure on the way to success.
-
You hava a table containing about 1000000 records.Now you want to delete 950000 records and only left 50000 records in the table. As there isn't much memory can be used, how to deal with it perfectly (both in time and in space)? many thanks
Shrinking a transaction log is a science in itself, so here is a very useful script that I use(you may have to run the script more than once on the same transaction log for it to recover all the free space). Click here for the google document.
You always pass failure on the way to success.