Hey all,
Our Log Shipping Primary is running short of space. We intend to allocate another LUN slice to accomodate growth.
Now... our options appear to be:
The question is... what will happen on the Log Shipped Secondary after we do either of these? Will it try to move the dB to a new location (which won't exist), will it try to add an additional datafile (in a location that won't exist) or will it just continue on its merry way with the existing files as they are today?
Or even worse... will the Secondary have to be re-initialized? That's 100s of GBs over a small pipe - not good.
Thx!
Answer by KenJ ·
David Wimbush is right-on with moving the primary database. It will have no impact on the log-shipping secondary and is the route you would probably want to take.
If, however, you ever do need to add a file to the primary database, you will get errors on the log-shipping secondary restore job, but there is a workaround. Here is a sample error:
Message
2010-03-05 20:53:32.96 *** Error: Could not apply log backup file 'd:\logshipping\source\databasename_20100306025253.trn' to secondary database 'databasename'.(Microsoft.SqlServer.Management.LogShipping) ***
2010-03-05 20:53:32.96 *** Error: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\databasename2.ndf'.
File 'databasename2' cannot be restored to 'C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\databasename2.ndf'. Use WITH MOVE to identify a valid location for the file.
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
RESTORE LOG is terminating abnormally.(.Net SqlClient Data Provider) ***
This sample shows that the destination data file on the log-shipping secondary server is in use. If the path didn't exist, as on your server, you would see an invalid path error message.
The workaround is to manually apply the transaction log from the error message to the secondary server and specify WITH MOVE to get the new data file file into a location that does exist on the secondary server.
restore database databasename from disk = 'd:\logshipping\source\databasename_20100306025253.trn'
with move 'databasename2' to 'D:\logshipping\target\databasename2.ndf'
, norecovery
This will add the new data file to the secondary server, and the log-shipping secondary restore job will continue to operate normally.
Answer by David Wimbush ·
I strongly suspect you could move the database because that should just be an update to a system table in master. It won't get shipped over to the secondary server.
I'm not so sure about adding a secondary data file, though! So far, I've been lucky enough to have kept all my databases to one file and one file group, so I'm not really qualified to comment.