error logs not visible

[beechill4]

Hi,

i cannot see my sql server 2000 error logs via enterprise manager.

the files exist on the server.

is there a way of getting around this?

thanks in advance

 

[ptheriault]

ARe you getting an error message? Are they just to large to open from EM?
They are can be found in the logs folder under MSSQL in your SQL install path. You can wordpad to open them.


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

no error message.
would like to be able to see them through em thats all.

 

[ptheriault]

They are most likely to large.
What are the size of the logs.
You should cut a new error log every week. Run this command. (I have it scheduled to run weekly that way my logs don't grow to large.)

sp_cycle_errorlog

It will create a new log. Try to open the new log.






- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

paul,

have run that command and it worked. but i still cannot see the files through EM?

 

[ptheriault]

And there is no error message? What does the it do, just hang at a blank screen? What are the permissions of the account you are loged in with?

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

no error message paul: see section of file:

logged in as sa

2007-05-04 10:57:36.33 spid53 Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)

2007-05-04 10:57:36.33 spid53 Copyright (C) 1988-2002 Microsoft Corporation.
2007-05-04 10:57:36.33 spid53 All rights reserved.
2007-05-04 10:57:36.33 spid53 Server Process ID is 1108.
2007-05-04 10:57:36.33 spid53 Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2007-05-04 10:57:36.33 spid53 Errorlog has been reinitialized. See previous log for older entries.
2007-05-04 10:57:36.33 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:36.98 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:37.39 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:37.73 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:38.06 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:40.06 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
2007-05-04 10:57:40.37 logon Login succeeded for user 'sql'. Connection: Non-Trusted.

 

[ptheriault]

I wasn't able to find any reason for the error log not opening through EM. (besides the file being to large). I can see where you cycled the log. Are you using EM local on the server or on a remote PC? If it is remote have you tried to open the log localy on the server?

Also,
I noticed that your server is only at sp3a. You should patch that server to sp4.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[beechill4]

paul,

the file grows to 75mb immediately after recycle with thousands of 2007-05-04 10:57:36.33 logon Login succeeded for user 'sql'. Connection: Non-Trusted.
messages

 

[mutley1]

It sounds like you don't have the log location set in the start up parameters so everything is cached and then writes when a restart (or log recycle) occurs.

In EM, right click on your server and select properties, then click on startup parameters. look for an entry beginning with -e. this is the path of the error log on the server. If it doesn't exist, reate it where it should be (but it sounds like it is there because you are getting text file logs).

Next, check the registry for the following 2 settings:

1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent then look for ErrorLogFile (should be the location of the logs on whateverdrive then SQLAGENT.OUT, e.g. C:\Prog blah blah\blah\SQLAGENT.OUT

2.HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters and there should be one called SQLArgX (where X is a number) and that should be the same location as the -e in the startup parameters.

Post back if this doesn't solve it and will try to investigate further.

HTH,

M.

 

[ptheriault]

That's it. 75 MB is too large to open from EM. Who is logging in with sql? is it an account from an untrusted domain?


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[mutley1]

Sorry - the other thing I forgot to mention is you are probably filling the log with sucessful login messages. i think you have the audit levet set to all. If that is the case, and you get a lot of connections frequently, then probably not the best idea. Right click on the server in EM, properties, click on the Security tab and change it from all to failure (radio buttons).

HTH,

M.

 

[beechill4]

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent =C:\Program Files\Microsoft SQL Server\MSSQL\LOG\SQLAGENT.OUT

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\MSSQLServer\Parameters (does not exist)

the startup parameter points to the correct location.

i have set the audit level to failure

 

[mutley1]

The first one you mentioned - is that under ERRORLOGFILE in that registry folder?

I would create a parameters one as well. We had this problem recently and that resolved it. Obviously, back up the registtry etc. if this is production. It should be under parameters, then SQLArgX, where X = the number it is in the startup parameters, i.e. if it is top of the list, then SQLArg0, if it is second, then SQLArg1 etc.etc. I cant believe they dont exist as that is where the info in the startup params come from. Worst case, search the registry for SQLArg and see where it lies.

Cheers,

M.

 

[beechill4]

paul, yes the first one is under errorlogfile. am searching now for the second