SQL Transaction Logs

Today I was getting the following error while deleting records from a very large table (over 7.6 million rows):

The log file for database 'xxxxx' is full. Back up the transaction log for the database to free up some log space. [SQLSTATE 42000] (Error 9002). The step failed.

Deleting large amounts of data will cause this type of error to occur since deleted rows are entered into the transaction log. Truncating the table would overcome this problem, however truncating would delete all the records which I didn't want as I had to keep the last 2 years worth of data.

So I tried to do what the error message said, and that was to backup the transaction log:

BACKUP LOG XXXXX with truncate_only

and I also shrunk the log file for good measure:

dbcc shrinkfile ('XXXXX_Log', 'truncateonly')

After running these 2 lines and confirming that the size of the transaction log was reduced (20MB down to less than 1MB), I tried a second time to delete the table, and again it gave me the same error!

After some time of investigating and searching the net for more answers, I realised that the problem was a lack of hard drive space. The available space was only less than 100MB which wasn't enough to carry out the delete command on my large table.

The solution was to move the database to another hard drive with plenty of free space on it. This was easily done using the Detach and Attach commands in SQL Enterprise manager. This can only be done after hours when there are no connections to the database.