I have a database that is log-shipped to two reporting servers and it is out of physical space. I need to create a secondary data file but have a few questions about this…
Question continued…1) When I issue the create file command, will it be log shipped (I think it will)? 2) Will it fail when the drive specification is not replicated on the secondary servers (I think it will)? 3) What will that impact of that be and how can I work around it? 4) How will log-shipping continue when the secondary datafile is not created (or not created the same) as the primary server? And finally 5) What are your recommendations for doing this with minimal interruption.
The situation you’ve encountered is certainly not rare. At 50,000 feet: yes, you can add a file, yes it can be on different paths on different servers, yes the process will be logged, and yes doing so will throw an error. There’s a fifth yes though, and that’s, “Yes, there’s a simple workaround.”
While the process isn’t as “seamless” as Microsoft says it is at the beginning of the article this Microsoft KB article provides the details you’ll need to add a new database file. To sum up what you’re going to see in the article, you’re going to add the new file, SQL Server’s going to throw an error, then you’re going to manually restore the transaction log backup using WITH MOVE. Once you’ve done that, everything really will be seamless.
