I love scratching my head; that’s why I became a DBA in the first place, I get the chance to scratch my head and swear a lot, without anyone complaining about it.
The most recent head-scratching situation was when we checked our backup history table (we use a custom script, and no, we’re not allowed to change things in production as we please) and saw something similar to this:
Ooops, a NULL entry, this is not good.
After some testing, I arrived at this error:
“Backup, file manipulation operations (such as ALTER DATABASE ADD FILE) and encryption changes on a database must be serialized“. For the ones interested, here’s the link to error messages in SQL Server 2008 R2, 3xxx series.
Now I have something.
It turns out that the autogrowth is set to 1 GB, which is totally not sufficient for our particular DBs. In other words, both data files and log files must grow, and every now and then the growth happens during the backup process.
The solution is very simple: change the autogrowth to something more meaningful to your DB; ah, also, please make sure you don’t shrink the transaction log file because “it looks big”. Yes, I know, we should all have our DBs properly sized, but let’s just say that most of the IT shops are far away from the fairy land.
I hope this helps.