Moving transaction log files

One of my customers took my advice and finally wanted to move the transaction log files to a separate disk. Probably, the current disk space issues on that server helped here a little bit as well 😉
The best way to move database and log files to a new location is by specifying the new file location in the FILENAME clause of the ALTER DATABASE statement.
To move a file of a database, follow these steps:

  1. Set the databases offline
  2. Move the file or files to the new location
  3. Alter the database for each file that has been moved
  4. Bring back the database online

This method works really well for a single database but if you have to repeat it twice, you can automate it.
And in my case, the customer wanted to move the transaction log files for all the databases (230) in 1 time.

The first part of the script will collect the current physical paths and file names of the transaction log files and will store it into a table variable.
Don’t forget to specify the parameter @NewPath which should contain the new path for your transaction log files.
Secondly, a loop will go over all the databases that are ONLINE and will execute the 4 actions as described above. With the “ONLINE” option, you avoid bringing OFFLINE databases back online.
If the move was successful, the script will delete the old transaction log file. I’m using xp_cmshell to perform the COPY and the DEL (I know…). If you should know any better solution, just drop me a comment!
Keep also in mind that this script only works within the same SQL Server Instance.

SET NOCOUNT ON
GO
declare @DatabaseFileLocation as table  (
[Databaseid] int,
[DBName] sysname,
[FileType] int,
[name] varchar(256),
[physical_name] varchar(1024)
)

DECLARE @sqlcmd nvarchar(1024), @Newpath nvarchar(1024),@ID int

--Specify the new transaction log file folder
set @Newpath='E:'
set @ID=4

--Collect the current physical name of the transaction log file
SET @sqlcmd = N'use [?]
select DB_ID(), DB_NAME(), type, name, physical_name from sys.database_files where type=1'

Insert into @DatabaseFileLocation
exec sp_MSForEachDB @sqlcmd

while 0=0 
begin
	select @ID=min(DATABASE_ID) from sys.databases where database_id>@ID and state_desc='ONLINE'
	if @@rowcount=0 or @ID is null break
	begin
		--Set database OFFLINE
		select @sqlcmd= N'ALTER DATABASE [' + name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE' from sys.databases
		where database_id=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--COPY the transaction log file to the the new path
		SELECT @sqlcmd= 'COPY ' + physical_name + ' ' + REPLACE([physical_name],SUBSTRING(physical_name,0,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1),@Newpath) from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec master..xp_cmdshell @sqlcmd
		--ALTER the database so that it will point to the new transaction log file location
		select @sqlcmd='ALTER DATABASE [' + DBName + '] MODIFY FILE ( NAME = ['+ name + '], FILENAME = ''' + REPLACE([physical_name],SUBSTRING(physical_name,0,LEN(physical_name)-CHARINDEX('\',REVERSE(physical_name))+1),@Newpath) + ''')' from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--Set database ONLINE
		select @sqlcmd= N'ALTER DATABASE [' + name + '] SET ONLINE' from sys.databases
		where database_id=@ID
		print @sqlcmd
		exec sp_executesql @sqlcmd
		--If all went well, remove the old transaction log files
		SELECT @sqlcmd= 'DEL ' + physical_name  from @DatabaseFileLocation where Databaseid=@ID
		print @sqlcmd
		exec master..xp_cmdshell @sqlcmd
	end
end

Leave a Reply

Your email address will not be published. Required fields are marked *