I have a large database in Simple recovery model (vendor requirement) and it took 3 days to back it up to the new hosting company's server.
The morning of the third day, the DB went offline because the transaction log had filled up its drive.
My understanding was that SQL Server would just start recycling the space. What's more interesting is that I don't think the database started working again until I gave it a bit more space – it stayed busted after the backup was complete. I didn't take the time to properly debug, though, so I don't know exactly why it was stuck.
Question: Why can't SQL Server in Simple Recovery re-cycle its transaction log while it is backing up?
Best Answer
The transaction log is used during the backup process to store all the changes that were committed while the backup was being taken. These change are applied to the backup file so that the backup is consistent as of the time it finished (only committed transactions).
But three days for a full? I backup a Terabyte database every day and it only takes 90 minutes (compression, multiple files, SQL native). Given your version, you should look into backing up to multiple files at the same time (or speeding up your backup storage).
Answer: the transaction log is in use by the backup process.
Because you were sending this to the vendor, I suspect that you sent it directly from your local SQL Server to the Vendor on a fileshare (especially since you say it only takes 90 minutes to backup locally).
For this scenario, I would recommend that you backup locally first (and if you were in FULL/BULK recovery mode, maybe with the COPY_ONLY flag) and then send it to the vendor. You won't tie up your SQL Server with this backup and you gain the opportunity to compress the backup using 7zip or similar (which should easily cut it down by 50%).