How to cycle (reset, clear) the error log ?

The sp_cycle_errorlog system stored procedure closes the current error log file and cycles the error log extension numbers just like a server restart without a restart.

-- Clear SQL Server error log -  delete sql error log - recycle error log

USE master;
EXEC sp_cycle_errorlog ;  
GO

-- Closes the current SQL Server Agent error log file & cycles the log extension numbers 

USE master;
EXEC sp_cycle_agent_errorlog ;  
GO

-- The location  of error log files for SQL Server 2008 instance SQL2008

/*

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log

*/

-- List of SQL Server error log files - ERRORLOG is current

/*

C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log>dir errorlog*.*

 Volume in drive C is Primary RAID1

 Volume Serial Number is 9C4A-FE1A

 Directory of

 C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log

07/02/2012  11:17 AM            17,892 ERRORLOG

07/02/2012  08:43 AM            26,912 ERRORLOG.1

07/01/2012  08:43 PM            23,684 ERRORLOG.2

07/01/2012  04:35 PM            25,736 ERRORLOG.3

06/30/2012  09:46 PM            21,164 ERRORLOG.4

06/30/2012  06:35 PM            19,496 ERRORLOG.5

06/30/2012  06:20 PM            25,264 ERRORLOG.6

               7 File(s)        160,148 bytes

               0 Dir(s)  336,552,349,696 bytes free

*/

------------

-- SQL Server find the location of master.mdf, master.ldf and ERROR LOG

------------

-- SQL Server error log location - errorlog location

DECLARE @SQLServerErrorLog TABLE

(     ID INT IDENTITY(1,1),

      logdate DATETIME,

      processinfo SYSNAME,

      logtext VARCHAR(MAX))

DECLARE @StartUpInfo int

INSERT INTO @SQLServerErrorLog (logdate, processinfo, logtext)

EXEC xp_readerrorlog

-- SELECT * FROM @SQLServerErrorLog

-- Find SQL Server errorlog location

SELECT ErrorPathInfo = logtext FROM @SQLServerErrorLog

WHERE logtext like 'Logging SQL Server messages in file%'

/*

Logging SQL Server messages in file

'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG'.

*/

/*****  THE FOLLOWING WILL NOT WORK AFTER RECYCLE *****/

SELECT @StartUpInfo=ID FROM @SQLServerErrorLog

WHERE logtext like 'Registry startup parameters:%'

SELECT logtext FROM @SQLServerErrorLog

WHERE ID =  @StartUpInfo

/* SQL Sever 2005

SELECT logtext FROM @SQLServerErrorLog

WHERE ID BETWEEN @StartUpInfo+1 AND @StartUpInfo+3

*/

/* Results

Registry startup parameters:    

-d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\master.mdf   

-e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG   

-l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mastlog.ldf

*/

------------

Source : sqlusa