Is this possible: move logfile online?

Question

Is this possible: move logfile online?

Wilfred van Dijk

SSCrazy Eights

Points: 8980
More actions
December 8, 2008 at 7:06 am

#129203

hi,
Is it possible to move a logfile without detaching the database? something like:
1) create a new logfile
2) make old logfile inactive / offline
or other possibilities ...
Wilfred
The best things in life are the simple things

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

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

You an do an ALTER DATABASE ... MODIFY FILE ... MOVE, but that still requires the SQL to be stopped or the DB taken offline and the files moved manually.

Why do you need to move without any downtime? It'll only be the time required to move the file from one drive to another.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Wilfred van Dijk SSCrazy Eights Points: 8980 More actions · Answer 2

A lot of application servers are connected to this database and this database is used allday. If I need to stop the database, I have to stop a lot of other (application) servers/services, too.

Wilfred
The best things in life are the simple things

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 3

No maintenance window? Weekends?

You don't have to stop SQL. Once the Alter Database has been run, you can take that DB offline, move the files and then bring it back online

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

HanShi SSC-Dedicated Points: 33506 More actions · Answer 4

Here is what I tried to do once:

I added a new log-file on the new location and then make the original log-file empty with DBCC SHRINKFILE and the EMPTYFILE clause. I was NOT be able to delete the initial file though :(, because you're not allowed to delete the PRIMARY log file.

From technet site http://technet.microsoft.com/en-us/library/ms191433.aspx

Deleting a data or transaction log file removes the file from the database. You cannot remove a file from the database unless the file has no existing data or transaction log information; the file must be completely empty before it can be removed. To empty a data file by moving data from the data file to other files in the same filegroup, use the DBCC SHRINKFILE statement and specify the EMPTYFILE clause. Because the Database Engine no longer allows data to be placed on the file after the shrink operation, the empty file can be removed by using the ALTER DATABASE statement or SQL Server Management Studio.

You cannot move transaction log data from one log file to another to empty a transaction log file. To remove inactive transactions from a transaction log file, the transaction log must be truncated or backed up. When the transaction log file no longer contains any active or inactive transactions, the log file can be removed from the database. For more information, see Managing the Transaction Log.

Important:

After you add or delete files, create a database backup immediately. A transaction log backup should not be created until after a full database backup is created.

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 5

Did you try to add one more log file and empty the existing one then?