5

Recently I changed the Autogrow settings for some of the databases on our SQL Server 2008 R2 server. These are involved in a database mirroring configuration, with the principal on SERVER1 and the mirror on SERVER2.

This week I failed over three databases- now SERVER1 is the mirror and SERVER2 is the principal for these databases. It appears that the autogrow settings did not move over to SERVER2 properly, as the databases now show that they grow by a percentage (two are set to 32768%, the other to 131072%).

This is different than the settings that used to be there (I believe it was the default- 10%), and also different that the 256MB I set on SERVER1.

To make things more confusing, this is only happening on the primary file- the secondary files and log file has retained the settings I set on SERVER1.

My suspicion is that this is a bug- I did patch SQL after changing the autogrow settings. My question is- has anyone seen this scenario before? Is there a method to make sure all of the settings are correct on both servers without failing all the databases over?

UPDATE: Using a 4th database that I'll call DB1, I set the autogrow on SERVER1 to 512MB (after failing the database over, witnessing the same issue, and failing it back). When I failed it over to SERVER2 after that, it shows growth of 65536%. The takeaway is that the is_autogrow_percent value in sys.master_files is not moving to SERVER2, but the growth value is.

UPDATE2: With DB1 on SERVER1, I changed the autogrow to 10% and failed it to SERVER2. The 10% value remained. I then repeated this, setting autogrow back to 256MB this time. Again, the growth value changed but the is_autogrow_percent did not.

Improve this question
5
  • 2
    Potentially related: connect.microsoft.com/SQLServer/feedback/details/127177/… and connect.microsoft.com/SQLServer/feedback/details/126336/…
    – Aaron Bertrand
    Jul 31, 2013 at 21:06
  • Interesting- those two issues are the same failure mode, just triggered by different events (attaching a database, rebooting a server, failing over a mirrored database).
    – msgisme
    Jul 31, 2013 at 21:13
  • Yeah, same symptom, different cause, no idea about a fix - other than manually setting the autogrow rate after any such event. I know some people even have background jobs that check the settings multiple times daily...
    – Aaron Bertrand
    Jul 31, 2013 at 21:14
  • I guess that is the route I'll be going down. I'm just glad I discovered this before we actually grew a file by 30,000% :)
    – msgisme
    Jul 31, 2013 at 21:17
  • I have seen a similar bogus autogrow issue. The funny thing is that you may get an error before you run out of disk space, possibly drawing attention to the issue. In your case (30,000%) for example: If the file was 1 GB and the time to autogrow came, first it would grow to 300 GB, and then next time, it would need 90 TB of space. Not that I am saying this is a good thing.
    – StanleyJohns
    Aug 1, 2013 at 7:04

1 Answer 1

Reset to default
0

I'm addressing the issue using Policy Based Management. I created a policy on the Data File facet that states all files must have @GrowthType = KB.

I also filed a connect item: https://connect.microsoft.com/SQLServer/feedback/details/796354/autogrow-is-percent-flag-set-on-principal-does-not-persist-to-mirror

UPDATE: This is fixed in SQL Server 2008 R2 SP2 CU8 and SP1 CU13.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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