Skip to content

Backup, file manipulation operations and encryption changes on a database must be serialized

September 29, 2011

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.

Best regards,
io

About these ads
One Comment

Trackbacks & Pingbacks

  1. Tool review – SQL Backup and FTP « sqlservermct

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 40 other followers

%d bloggers like this: