How do I move SQL Server error log files to a new location?

Question

Hi, my default SQL Server 2005 log directory is full on C drive. In order to prevent this issue happening in future, I plan to move the default log directory to some other place. Could you please tell me how I can move the error log default directory?

I browsed the web, there is solution for SQL Server 7 and 2000 but not 2005. Please kindly help.

Regards Qing

Answer 1

When you use SSMS and click on generate script it creates T-SQL like this

  USE [msdb]
    GO
    EXEC dbo.sp_set_sqlagent_properties @errorlog_file=N'C:MSSQL\Log\SQLAGENT.OUT'
    GO

I just verified that it works, just make sure that the folder exist otherwise agent won't start

you probably also need to run this first, probably the proc above is disabled (by default)

EXEC master.dbo.sp_configure 'show advanced options', 1

RECONFIGURE

EXEC master.dbo.sp_configure 'Agent XPs', 1

RECONFIGURE

Answer 2

Go to server properties and look for start up parameters. Probably right-click in object explorer, properties, advanced (I can't check in SSMS now).

The path is the "e" switch. Change and restart.

It can't be set in documented SQL commands :-)

Basically, it's in the registry as one of the parameters to the sqlservr.exe binary when run as a windows service