move the log file

We have a tradition, no, a best practice of putting logs in one location and data files in another.  But if the data or logs get too large, or in one case, they are colocated, the physical location of the files can be moved.

You can find where the files are, or more correctly, where SQL thinks they are (the configuration of the database) in SSMS or using sp_help.

Typically, you would detach the database, move the ldf or mdf file(s), and reattach the database.  This can be done in SSMS or via t-sql scripts.  You can also take the database off-line, edit the database properties and change the path to the new location, then put the database back on-line.

Locate the files

USE Database
GO
sp_helpfile
GO
Use MASTER
GO
ALTER DATABASE Database
SET SINGLE_USER
GO

sp_detach_db 'Database'
GO

At this point, the database is detached, and we can move the file to the new location.  We can do this is SSMS by selecting the Database and other tasks.

sp_attach_DB 'DataBase', 
'D:\Program Files\Microsoft SQL Server\MSSQL\Data\Database_Data.mdf',
'E:\Logs\Database_Log.ldf'
GO

Reattach the database referencing the file locations. (This can be done in SSMS at the Databases level, with Attach… and [ADD] to locate the files.  Check that the database is back on-line and working properly.

Advertisements