Wednesday, March 27, 2013

Move SQL Server transaction log files to a different location via TSQL


To optimize I/O performance of a database, it's always a good idea to keep LOG file & DATA file in separate physical drives. 

The transaction log file records every data change and DML transaction executed in the database.  Writing to the transaction log file is sequential in nature as compared to the database files which are typically random I/O.  As such, placing the log file on separate physical disk from database will allow the disk to work in sequential manner and perform optimally.  

I'm going to show a demonstration of moving LOG files to another drive.


1: Capture database and transaction log file information

USE AdventureWorks
GO
sp_helpfile
GO

2: Set database to single user mode and detach database

Use MASTER
GO
-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO
-- Detach the database
sp_detach_db 'AdventureWorks'
GO

*** Now the database is detached.  Once the detach process is completed, then you can copy and paste the new transaction log file then delete the old transaction log file via Windows Explorer.  Once this is completed, we can attach the database with SQL Server database log file at new location with the following script:

3: Attach database with log file at new location

USE master
GO
-- Now Attach the database
sp_attach_DB 'AdventureWorks',
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\AdventureWorks_Data.mdf',
'E:\Move LogFile here through T-SQL\AdventureWorks_Log.ldf'
GO

4. Validate the LOG moving

After the final attach command  transaction log file has been moved to new location and database is operational with log file on new location.  Verifying the new database transaction log location can be accomplished by re-running 

USE AdventureWorks
GO
sp_helpfile
GO

2 comments:

Nandkishor Wagh said...

brillant piece of information, I had come to know about your web-page from my friend hardkik, chennai,i have read atleast 9 posts of yours by now, and let me tell you, your webpage gives the best and the most interesting information. This is just the kind of information that i had been looking for, i'm already your rss reader now and i would regularly watch out for the new posts, once again hats off to you! Thanx a million once again, Regards, Difference Between sql and tsql




Ferdous Nadvi said...

Awesome. Thanks Wagh for your feedback. I'm glad that it helped ! :)