Removing one of multiple log files

Question

Removing one of multiple log files

SQLbee

Ten Centuries

Points: 1009
More actions
August 13, 2009 at 1:12 am

#213458

I have a question on removing log files from an online database.
I currently have a clustered server instance with SQL server 2005 Standard x64 .
There is a db which is causing excessive log growth issues recently. I have currently two log files on 2 separate volumes, but need to move the second log file to another drive.
I am aware that the server will lock onto the file while writing to it, but under database properties, I get an option to "Remove" the file as well in addition to "Add" option. Can anyone please suggest a method to move the second log file location without taking the DB offline. Will it work if I add another (third) log file at the new location, and then remove the second or old file?
Thanks
Akhil

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

murthykalyani SSCarpal Tunnel Points: 4500 More actions · Answer 1

Shrink the log file with emptyfile option for the one which you want to remove and then you can remove it using alter database statement.

You can carry out this when there is no activity on the database that you are trying to do.

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 2

It sounds like you are not performing any transaction log backups. If you were, then you wouldn't need a second log file, or be looking at creating a third. At least, that is what I am guessing.

How often are you backing up the transaction log?

Also, you should read the article I link to in my signature about Managing Transaction Logs - it will idenfity why you are having these problems and how they can be resolved.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

ChiragNS One Orange Chip Points: 26137 More actions · Answer 3

To add to Jeffery's points

from BOL

However, transaction log files cannot be part of a filegroup; they are separate from one another. As the transaction log grows, the first log file fills, then the second, and so on, by using a fill-and-go strategy instead of a proportional fill strategy.

.

"Keep Trying"

SQLbee Ten Centuries Points: 1009 More actions · Answer 4

Thanks for reply guys.

Kalyani's reply sorted it out. I am somewhat familiar with log management and backup em up 15 min. The problem arose due to heavy log generation on a mirrored database, and there are severe network limitations whcih I am having to werk with:-)