February 27, 2010 at 10:23 pm
While archiving a large table in preparation for trimming its oldest records, I inadvertently ran out of disk space. I wrote a query to fully complete the archive operation before the record deletions began. This problem occurred in spite of the fact that the hard drive was about 45% free space before I began this operation.
I stopped the job. In SSMS, the status is saying that it is cancelling the query (rolling back) but I'm not clear that SQL Server can function in this condition.
I am wondering what can be done, other than just wait and hope the rollback is successful. I tried the following:
1. Truncating the Archive table. I'm still waiting for that command to complete, which is not a good sign. Normally that command is very quick to execute.
2. Set the Recovery model to simple. SS responds by saying there is no more space in the log file with which to execute this command.
I am open to suggestions.
LC
February 28, 2010 at 1:51 am
The rollback completed and left me with almost no disk free space, about 3.5 MB on a 136 GB drive.
I performed a SHRINK DATABASE on the database which now had a HUGE log file and waited. This reclaimed all my lost disk space, about 54.2 GB now free.
I've set the Recovery model for the database to Simple for the archiving and deleting operations. I've also decided to execute it in 30 day segments starting with the oldest date, 01/01/2009. This will prevent a huge growth of the log file.
When finished, I'll set the Recover model back to Full.
LC
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply