DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS  SQL 2012 PROGRAMMING  DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to cycle (reset, clear) the error log?

Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to cycle (reset, clear) the server error log and locate the server startup parameters.

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

*/

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

Related articles:

SQL SERVER Recycle Error Log Create New Log file without Server Restart

SQL Server 2005 Error Log Management

Using the SQL Server Agent Error Log

Monitor the SQL Server Error Log

 

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE