6

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?

15

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%).

|improve this answer|||||
  • yea, it's kinda slow. I'm not happy about it. It only takes 90 minutes for me to back up these DB's locally. – James Jun 27 '18 at 13:07
  • 5
    Sorry, I missed the part of your question where you were uploading it to the vendor. Definitely would have recommended taking it locally and then send it to the vendor. (plus, three days of possible transmission errors). Also, you could use zip on the .bak file and it would have done a semi-decent job of compression. – Jonathan Fite Jun 27 '18 at 13:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.