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:
- Set the databases offline
- Move the file or files to the new location
- Alter the database for each file that has been moved
- 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