May 15, 2005 at 11:51 pm
Hi
Need to delete old records from a table without involving transaction log which is full and the SQL administrator is reluctant to allocate more space. Any suggestions to this situation would be appreciated.
Cheers
Danny
May 16, 2005 at 12:35 am
Ehm, your SQL administrator should be pretty concerned if the log is full. But no, there is no way to delete rows without involving the transaction log. You can however try and design your delete operation to execute in such a way that the log is able to reuse space efficiently, if it is being updated as often as needed. Like I said, if it is truly full, or nearly so, then your administrator should be pretty busy right now.
If you are just unable to perform the entire delete in one transaction because of little empty space in the log you should try batching the operation and committing the transactions between every batch.
If you are clearing an entire table you can use TRUNCATE TABLE to use a minimum of log space.
May 16, 2005 at 3:02 am
Thank for the pointers.
May 18, 2005 at 5:23 pm
If the log file is full, you should do these 3 steps.
1. Backup the log file.
2. Backup the log file WITH TRUCATE_ONLY (this is supposed to release empty space back to Windows)
3. DBCC SHRINKFILE ('name_log', TRUNCATEONLY)
It's the final step which actually shrinks the log file so that Windows see's it much smaller. The preceeding two steps need doing to prepare for the third.
May 19, 2005 at 11:43 am
Ask the administrator to back up the transaction log, which should free up space in the file. Then, as Chris suggested, break up your delete into smaller batches. However, breaking into smaller batches won't help if the database is in full recovery model, and some form of a truncate (or preferably, backup) is not performed on the transaction log between batches.
If this is a one time thing, ask your administrator to backup the database, then the log, then change the recovery model to simple. Then when you run your small delete batches, they will be truncated from the log as they are committed. Once you are done, have the administrator change the recovery model back to full, then take another full backup. Optionally, the administrator can add an additional transaction log file to the database for the duration of your delete, then remove it when you are done. Again, backups are very important throughout this process.
Finally, there is the question of why was the transaction log full in the first place? Was it because you had already tried the delete, and filled it? Or because the recovery model is full, and transaction log backups are not being taken? If the latter, your administrator MUST schedule transaction log backups for the database, or change the recovery model to simple. Simple recovery is highly discouraged in an OLTP system!
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply