December 12, 2002 at 8:26 pm
I've put this as an answer to one of the other threads but this one seems different and if it happens to others could be a problem (because it stops the server!).
This appears to have simple explanations but not simple solutions!
We have the same issue, our c:\sql.log grows until it kills the system (takes some time).
The logname can be changed with the odbcad32 utility but CANNOT BE SWITCHED OFF.
Our production server has the problem (was upgraded from 7 to 2000 sp2 and the services are run as domain\administrator) but a test server doesn't (fresh install of 2000 then sp2 run by local 'system').
ProcessExplorer from sysinternals shows very clearly that c:\sql.log is 'owned' or managed by the SQLAgent. When I switch off SQLagent the log stops accumulating (but I need the agent to run maintenance).
I can delete the file - stop the agent AND sqlserver and delete the file. Restarting both then kicks off a new file.
Everything points to the odbc manager (certainly the logfile name is stored in the odbc registry key) but the registry key is set to 0 (off) not 1 (on) and the odbc administrator does not show the logging to be started.
It looks as if the problem was caused by MS Query users (eg XL) doing queries with the base (office2000) odbc install but this does not hold up against the test system.
Does anyone have any ideas as to how to turn it off. The content of the log is of no use and doesn't even have a date/time stamp against each entry so is not of much use.
Would love some feedback.
December 12, 2002 at 9:07 pm
I will take a look at our machine to see if same issue occurrs there.
See related conversation in thread http://qa.sqlservercentral.com/forum/topic.asp?TOPIC_ID=6860&FORUM_ID=9&CAT_ID=1&Topic_Title=SQL%2ELOG&Forum_Title=General
December 13, 2002 at 4:36 am
Just took a look and none of my boxes are doing this. When I turned on on my machine it took forever to do anything.
Now have you checked the properties for SQL Server Agent to make sure somehow the name for the Error Log wasn't changed?
Also, can you grab a few lines of what is in the file so I can compare the output with mine to make sure is coming from actual ODBC trace?
Lastly, do you have the Oracle CLient installed to maybe talk to an Oracle server? I seem to recall the first time I noticed this file doing anything was in someway related to an issue with Oracle drivers.
December 13, 2002 at 4:49 am
ODBC trace is turned on/off via the DSN applet in control panel.
Andy
December 13, 2002 at 8:24 am
You can turn on/off ODBC trace by changing the key in following registry HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.ini\ODBC too. See http://support.microsoft.com/?kbid=274551.
December 13, 2002 at 10:55 am
Win2K & SQL7. No problem switching trace on/off in odbc manager. SQLAgent logged to c:\sql.log. Changed log file name on odbc manager but sqlagent did not always take notice!!!
Far away is close at hand in the images of elsewhere.
Anon.
December 14, 2002 at 3:55 pm
Guys, thanks for the input. In answer:
Allen Chui: interesting - I've been looking at HK_CurrentUser\software but after reading the KB article I exported the key and re-imported it into HKLM. What that did was interesting - it didn't switch it off but it did allow me to redirect the log onto a partition with a lot more space than the C:. It is still showing Trace=0 (in both instances) and is still writing the log.
Andy Warren: I don't see a 'DSN' entry in Control Panel but under CP\Admin tools there is Data Sources (ODBC) which fires up the ODBCad32 applet and is what I've been using.
David Burrows: Yes, this was not an issue with w2k/sql7. If you upgrade I would suggest that you re-install not upgrade!
Antares686: Here is a bit of the log. Its from one that is just fresh out of the oven - only a few lines from the start of the log after restarting both agent and server (must stop both in order for the sql.log to be released). It was from putting one of the entries into google that I discovered that it was and ODBC call. Another point of interest is that as I write this its Sunday morning and the only users shown in Enterprise Mgr are my login to EM, 6 sa's 6 systems and two domain\administrator (remember that server and agent are run under this account)
I've included an entry with a non successful return code, perhaps this is the clue that logging is running because I have a problem.
"
sqlagent 424-438ENTER SQLDriverConnectW
HDBC 009815C8
HWND 00000000
WCHAR * 0x1F7F8B88 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
sqlagent 424-438EXIT SQLDriverConnectW with return code 1 (SQL_SUCCESS_WITH_INFO)
HDBC 009815C8
HWND 00000000
WCHAR * 0x1F7F8B88 [ -3] "******\ 0"
SWORD -3
WCHAR * 0x1F7F8B88
SWORD 8
SWORD * 0x00000000
UWORD 0 <SQL_DRIVER_NOPROMPT>
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed database context to 'msdb'. (5701)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][SQL Server]Changed language setting to us_english. (5703)
sqlagent 424-438ENTER SQLGetDiagFieldW
SQLSMALLINT 2
SQLHANDLE 009815C8
SQLSMALLINT 1
SQLSMALLINT -1150
SQLPOINTER 0x00B738A0
SQLSMALLINT -6
SQLSMALLINT * 0x00B73898
sqlagent 424-438EXIT SQLGetDiagFieldW with return code -1 (SQL_ERROR)
SQLSMALLINT 2
SQLHANDLE 009815C8
SQLSMALLINT 1
SQLSMALLINT -1150
SQLPOINTER 0x00B738A0
SQLSMALLINT -6
SQLSMALLINT * 0x00B73898
"
James
December 14, 2002 at 4:10 pm
Sorry, one more thing. I don't believe that any Oracle components have been installed. I can see from the registry that some Sybase stuff has gone in but doesn't appear to be there any more (I don't get any event log issues for Sybase). We are mainly a Sybase shop (100% until I came along (g)) but on my test machine (w2k/fresh sql2k) I have Sybase 12 components running with no problems (or logs). Sadly, over time people who should know better (Sybase admins) have used this server for all sorts (footy tipping etc). As far as I can see though these were all present before the upgrade.
James
December 15, 2002 at 4:35 pm
I think I've got it.
I laboriously searched through the registry for 'Trace' and found an entry that had it set to 1. It is in the key MC\HK_Users\S-1-5-21 /lots of numbers/ \software\odbc\odbc.ini\odbc. Whilst I can't tell, I presume that this must be the user id of the SQL server account (domain\administrator). The full key was incomplete, ie it did not have the location of the sql log file.
I reset the 1 to 0 and the log file hasn't changed for over 30 mins (even when I log into Enterprise Manager which caused entries yesterday).
Phew!!
Thanks for all your help.
James
December 16, 2002 at 4:07 am
I wonder if this happened to stick this way during the upgrade. I checked mine on serveral machine (none of which were upgrades but clean installs) and found the key and all are set to 0. Post back if anything changes.
December 16, 2002 at 2:59 pm
As of this morning (8:40am Sydney time) its still dormant so I think we've swatted it.
I can think of four reasons:
a) that it was started by the upgrade (I have to admit I wasn't keen, I always prefer a good clean install)
b) that it automatically switched on if/when we received odbc calls caused by using older client components i.e. base win2k Excel/Access or sql7 client components (which was the case in the first few days).
c) that it was something to do with moving from using the system (default) account to run sql7 to the domain\admin in sql2k.
d) that some twit turned it on (under the domain\admin id). I would guess that this would be the MS response(!), although the two KB articles refer to the registry keys in different places.
Thanks for your help.
James
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply