Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a large database 200GB+ to which we added a second log file to cater for growth during an upgrade. This is now no longer needed so we want to remove it. Normally this would be a simple ALTER DATABASE <blah> REMOVE FILE <filename> statement.

However our database is log shipped to a secondary for reporting, so the question is - will removing this file break log shipping?

Server is SQL Server 2005 SP3 Standard Edition if it has any bearing.

share|improve this question

2 Answers 2

Removing the extra file won't give you any problems.

share|improve this answer

You are shipping backups of logs, not the actual ldf files. Since the backup mechanism would punt if the restore targets were different, I think you have a risk and would probably need to reinit log shipping. Found these notes. You'd have to halt shipping and manually apply the tran log WITH MOVE to avoid a likely reinit otherwise.

As for a possible work-around, you could empty log contents into other files, and then shrink the log to the smallest possible size. That should work but you'd have a tiny lingering file. Make sure to checkpoint/backup before you try.

share|improve this answer
    
Hi Eric, Thanks for the response, thought there might be a risk, so given it's a Friday, I'll go for the work-around for the moment until I can do some proper testing. –  Smellypom Mar 29 '12 at 20:25
    
Good plan :) Have a good weekend –  Eric Higgins Mar 29 '12 at 20:47

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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