Sql Server Mirroring and DB/Log File Growth

This post is going to cover a nasty surprise that hit me yesterday while doing some load testing/fail-over testing with Sql Server 2008R2.  This particular database had been set-up a long time ago with some surprisingly mad defaults.  The usual problem of a % based database/log file growth as opposed to a fixed size growth.

Anyway this particular database had for reasons lost to the sands of time been configured with an almost ridiculous growth.  This had been corrected on the principal server and I had assumed that this change was replicated to the mirror.

I was proved completely wrong in this assumption

The mirror continues to maintain its own separate database and log file growth settings, which while it is a mirror cannot be changed!

Therefore, I flipped the principal and mirror round let the load testing continue and went home.  The next day I found that the system and all tests had failed completely after a couple of hours.  Obviously now that the mirror was the principal and was using its own settings for database file growth, it had tried to extend the size of the database file to a size bigger than the free space on the drive.  Thereby causing mirroring to fail and obviously the application to fail completely.

In fact mirroring had to be re-initialised by copying a backup from the old-mirror to the old-principal and re-configuring mirroring.

The following code taken from http://www.handsonsqlserver.com/how-to-view-the-database-auto-growth-settings-and-correct-them/ will show, even on a mirror, the database growth settings:

-- auto growth settings for data and log files
select DB_NAME(mf.database_id) database_name
, mf.name logical_name
, CONVERT (DECIMAL (20,2) , (CONVERT(DECIMAL, size)/128)) [file_size_MB]
, CASE mf.is_percent_growth
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS [is_percent_growth]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(VARCHAR, mf.growth) + '%'
WHEN 0 THEN CONVERT(VARCHAR, mf.growth/128) + ' MB'
END AS [growth_in_increment_of]
, CASE mf.is_percent_growth
WHEN 1 THEN CONVERT(DECIMAL(20,2), (((CONVERT(DECIMAL, size)*growth)/100)*8)/1024)
WHEN 0 THEN CONVERT(DECIMAL(20,2), (CONVERT(DECIMAL, growth)/128))
END AS [next_auto_growth_size_MB]
, CASE mf.max_size
WHEN 0 THEN 'No growth is allowed'
WHEN -1 THEN 'File will grow until the disk is full'
ELSE CONVERT(VARCHAR, mf.max_size)
END AS [max_size]
, physical_name
from sys.master_files mf

The site mentioned above also contains the command on how to modify through scripts the growth settings for a particular database.

From my testing this seemed impossible when the database was being used as a mirror.

Conclusion

In conclusion, be sure that you set both the principal up correctly before you take the backup to restore on the mirror.

If you find out that the growth settings need changed, then you’ll need to replicate this on the mirror.  Most likely during some downtime you will flip who is principal, apply the changes to what was the mirror, and then flip the principal back to the original server.

Advertisements
This entry was posted in Sql, Sql Server. Bookmark the permalink.

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