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
GO
sp_helpfile
GO
GO
2: Set database to single user
mode and detach database
Use MASTER
GO
GO
-- Set database to single user mode
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO
ALTER DATABASE adventureWorks
SET SINGLE_USER
GO
-- Detach the database
sp_detach_db 'AdventureWorks'
GO
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
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
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
GO
sp_helpfile
GO
GO
