SQL log file

[kvinson]

How do you get the sql.log file to store on another drive other than C:? My problem is that when there are a lot of proccesses that sql is doing the log file becomes hugh over 2GB which almost shuts the server down. At present I have to reboot the server bring it up in DOS mode and delete it the file because if you try to delete it any other way you will get an error message that says file cannot be deleted, its in use. I am not talking about the sql log files in the Enterprise Manager, this file is generated that shows the following.
Sql.log:
sqlagent 9c4-9c8 ENTER SQLAllocHandle

SQLSMALLINT 1 <SQL_HANDLE_ENV>

SQLHANDLE 00000000

SQLHANDLE * 00C7D6C8



sqlagent 9c4-9c8 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)

SQLSMALLINT 1 <SQL_HANDLE_ENV>

SQLHANDLE 00000000

SQLHANDLE * 0x00C7D6C8 ( 0x00981c18)



sqlagent 9c4-9c8 ENTER SQLSetEnvAttr

SQLHENV 00981C18

SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>

SQLPOINTER 0x00000003

SQLINTEGER -5



sqlagent 9c4-9c8 EXIT SQLSetEnvAttr with return code 0 (SQL_SUCCESS)

SQLHENV 00981C18

SQLINTEGER 200 <SQL_ATTR_ODBC_VERSION>

SQLPOINTER 0x00000003 (BADMEM)

SQLINTEGER -5



sqlagent 9c4-9c8 ENTER SQLAllocHandle

SQLSMALLINT 2 <SQL_HANDLE_DBC>

SQLHANDLE 00981C18

SQLHANDLE * 00C7D6CC



sqlagent 9c4-9c8 EXIT SQLAllocHandle with return code 0 (SQL_SUCCESS)

SQLSMALLINT 2 <SQL_HANDLE_DBC>

SQLHANDLE 00981C18

SQLHANDLE * 0x00C7D6CC ( 0x00981cc0)



sqlagent 9c4-9c8 ENTER SQLSetConnectAttrW

SQLHDBC 00981CC0

SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>

SQLPOINTER 0x0000001E

SQLINTEGER -5



sqlagent 9c4-9c8 EXIT SQLSetConnectAttrW with return code 0 (SQL_SUCCESS)

SQLHDBC 00981CC0

SQLINTEGER 103 <SQL_ATTR_LOGIN_TIMEOUT>

SQLPOINTER 0x0000001E (BADMEM)

SQLINTEGER -5

 

[Databaseguy]

look into sp_detach and sp_attach. Detach the log file, move it, and re-attach it in the new location.

Just moved my data and log from C: (Which is supposed to be just windows stuff) to E: (Logical drive) worked fine.

Backup just in case first.

 

[SonOfEmidec1100]

I may be off the track here - but I thought sql.log is created by an ODBC SQL server trace - the contents also imply this. Not good for performance in a production (or any other) environment. I would check ODBC admin/Trace and turn tracing off.

 

[MaffewW]

sonofemidec is right I think, you have sql server odbc traing turned on. someones probably forgotten to turn it off after testing, and correct you shouldnt have it running due to disk space and performance

go to control panel. admin tool (if you're on win2k), open Data Sources (ODBC), click tracing and then 'stop tracing now', then stop sql server service and delete the sql.log file



Matt

Brighton, UK