SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

Adding Data Files To A Mirrored Database

This is my first post!  I don't really expect anyone to find this blog.

So I have a pretty big database (around 700 GB) and it's asynchronously mirrored to another location.  I thought it would be best to make the [PRIMARY] FileGroup as small as possible so I started with the unused data.  Since I really wasn't thinking, I just added a separate FileGroup and a data file so I can move the data to that file which is on RAID 50 (main data is on RAID 10).  All of a sudden, the mirror becomes SUSPENDED!  

Since the directory structure is not the same on either server, the database mirror doesn't know what to do with the extra data file which then results in a suspended mode.  You cannot just delete the data file/FileGroup to re-establish the mirroring so don't bother trying it.  The only thing you can do is redo the entire mirror.  Be sure to change the RESTORE DATABASE command to include the new data file(s) and use MOVE to their new directory.

That's the resolution to my issue.

If you can think of any other process to resolve this issue, please let me know!  Thanks!


Posted by Shawn Melton on 20 April 2011

HEY!!!! I found your blog!

Posted by Roy Ernest on 20 April 2011

If the two servers have the same directory structure and RAID configuration, will it work if we just add the secondary filegroup?

I tried when I was testing it and mirroring went into Suspend mode. Ours is a 500Gb DB. Just want to confirm if I did something wrong or if that is how it is designed.

Posted by BobbyTables on 20 April 2011

Hi. It is possible to add the file without doing a complete full restore to the mirror.

Check out this article: www.sqlservercentral.com/.../72294

That article outlines a controlled process to add a file to a database that has a different layout on the mirror. You can adapt that process to recover from your situation, however.

Posted by dajonx on 20 April 2011

Thank you Kozloski!  That's an excellent process which I will have to use next time!  

Roy, I believe adding a secondary FileGroup should not suspend the mirror so long as you don't add a data file.  I am in the early process of testing it out.  

Posted by Roy Ernest on 20 April 2011

dajonx, Thx. That explains it. I added a data file as well. :-)

Posted by SQLKnight on 27 April 2011

nice post.

Leave a Comment

Please register or log in to leave a comment.