question

Blackhawk-17 avatar image
Blackhawk-17 asked

Adding a Datafile to a Log Shipping Primary

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:

  1. moving the Primary dB to the new LUN
  2. adding another datafile and placing it on the new LUN

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!

sql-server-2005log-shippingddldatafiles
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

3 Answers

· Write an Answer
KenJ avatar image
KenJ answered

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.

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Wimbush avatar image
David Wimbush answered

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.

2 comments
10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Blackhawk-17 avatar image Blackhawk-17 ·
I agree about the move being an entry in Master and not shipped - we restored the initial secondary backup with MOVE to place it in a specific directory. It's that extra file I'm worried about.
0 Likes 0 ·
David Wimbush avatar image David Wimbush ·
Sorry I couldn't be more help, Blackhawk, but please let us know which option you go for and how it turns out. I'm relying on log shipping myself so I have a keen interest in the outcome.
0 Likes 0 ·
user-1187 avatar image
user-1187 answered

If you can efford down time then, just take the sql services offline and move the database running out of space to new LUN...

You can add the additional file but I think log shipping will fail...

10 |1200 characters needed characters left characters exceeded
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.