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?

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.

  • 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 Apr 1 '15 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 Apr 1 '15 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 Apr 1 '15 at 17:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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