4

I have a log shipped database, and I want to create a new NDF file in another disk for moving historic tables to that NDF file. My goal is to free some space in the MDF file because the disk that the MDF resides is getting full.

I have a doubt and is: if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If yes, how can I avoid this?

Improve this question
0

1 Answer 1

Reset to default
2

if I create the NDF file in a disk in the primary server that the secondary server does not have: does the log shipping process will fail?

If logshipping paths are same on the primary and secondary servers then adding an ndf will be seamless.

If the paths are different on primary and secondary servers then

  • Stop the backup, copy and restore jobs related to the database that you are adding a secondary file.
  • Add the ndf file on primary using ALTER DATABASE db_name ADD FILE..
  • run the backup job manually on the primary server. This will generate a T-log backup. Make sure that all the previous log backups are applied to the secondary.
  • Run the copy job manually so that the T-log with above changes is shipped to secondary server.

  • Manually restore the log file (from above step) using the 

    RESTORE log db_name FROM      Disk='path_T-log_from_Above_Step.trn'
with norecovery,  ---> Very Important .. !!
MOVE 'logical_file_name_of_ndf'
TO 'physical_path\secondary_file_name.ndf'

  • Once above steps are successful, enable all the backup, copy and restore jobs for the database. All the subsequent backup, copy and restore jobs should be successful now.

Improve this answer
3
  • Hi @Kin. Thanks for your response. So the LDF file of the database will grow big? I'm moving a 50GB table object.
    – Luis de Haro
    Commented Apr 1, 2015 at 14:50
  • It will (but not 50GB), you have to test it out in your environment and be comfortable with doing it in PROD. Feel free to upvote / accept as answer if this answers your question.
    – Kin Shah
    Commented Apr 1, 2015 at 14:55
  • Thanks. I will test what you propose in a local environment. I will accept this as correct because I think it is so.
    – Luis de Haro
    Commented Apr 1, 2015 at 17:42

Your Answer

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

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