Clearing SQL Server 2005 event log

Question

I need to delete the SQL Server 2005 logs, but can't figure out how to do it. I'm talking about the logs that are found in Management -> SQL Server Logs in SSMS object explorer. I can't seem to detete them from there, so I assume there is another way or a command to do it.

Also, I noticed that at least some log entries can be found in Windows Event Log (the ones about failed logins, for example). Will those get deleted too if I find a way to clear the SQL Server logs? Or should I clear the event log separately?

I looked for an answer but people seem to ask mostly about clearing transaction logs which is not what I need.

Answer 1

Log files in question are named ERRORLOG, ERRORLOG1 etc. and are usually stored in LOG folder somewhere inside MSSQL installation directory. In the beginning of the log there is a message like this:

Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'

It can help to determine the exact path.

If the need to delete these logs is caused by a security concern, it's good to execute sp_cycle_errorlog proc to make log cycle. This way the ERRORLOG file will be empty and all the events will be in ERRORLOG1, ERRROLOG2 etc which can be deleted at any time.

Answer 2

You can try the query:

EXEC master.dbo.xp_readerrorlog 0, 1, 'Logging SQL Server messages in file', null, NULL, NULL, N'asc'

The text column will provide you with the current location of your SQL error log file namely ERRORLOG.

The currently used error log file cannot be deleted. You can cycle the log file using sp_cycle_errorlog and the delete the former file which will be named ERRORLOG.1.

The windows event logs will have to be cleared out separately.