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 |
|
rocksteer
Yak Posting Veteran
69 Posts |
 Posted - 2010-07-29 : 06:15:24
|
| On SQL Server 2000, I have a large table that occupies most of my database(simple recovery mode). I cannot delete may rows before my tranaction log fills my remaining space on disk.1. Is there anyway to delete without writing records to the tranaction log?2. My physical disk is partioned 250 GB per partition. Most of my databases about 20 of them have the tranaction files on this partition, but the large database has the tranaction log on the same partition as the database. I in error created a new tranaction file on the same partion. I then created another tranaction file on the other partition. I restricted growth on the first two. I restricted the 1st transaction file to 13 GB (size at time of problem), and the one created in error to 1 MB (size after finding that it was on same partition). The thrid on partition with much space I left unrestricted.I did shrinkfile, and backup. The tranaction file shrunk from 13 GB to only a few MB. Can I restrict the 1st tranaction file to say its current size? Can I eliminate second(how)? Will this in effect stop the tranaction file from again filling my 1st partition? I would like to delete a lot of rows (couple of years) but fear crashing the server by filling up the 1st partion.If I had time, and did not fear problem with restore from backups, I would have our tech support remove the partition, and then I would have about 450 GB for my databases.I also heard that I could move the large database by moving its clustered index to a filegroup. Could I create a file group on the mostly empty partition, and do that. Would that effectively remove that table from the 1st partition, and releave my space problems? |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-07-29 : 07:30:41
|
| There are no unlogged operations in SQL. Do you want to delete all the rows in the table? If so, use truncate table instead. It's minimally logged, so will have much less impact.Otherwise do the deleted in chunks. DELETE TOP (5000) ... and run it in a loop until there are no more rows to delete.--Gail ShawSQL Server MVP |
 |
|
|
|
|
|
Any way to Delete With No Log?