SQL Error Log location

By viewing the SQL error log, you can ensure that the operation is completed successfully (such as backup, restoration, batch command, or other scripts and processes ). especially if an SQL server instance is stopped or restarted, viewing the SQL error log is very useful for detecting any current or potential problem areas, fields include automatic recovery messages, kernel messages, or other server-level error messages.

You can use SQL Server Management studio or any text editor to view the SQL error log.

By default, the SQL error log is located:

% ProgramFiles % \ Microsoft SQL Server \ MSSQL. n \ MSSQL \ log \ errorlog and errorlog. n

Although the system stored procedure sp_cycle_errorlog can be used to specify to record log files cyclically without restarting the SQL server instance, each time when an SQL server instance is started, A new error log is created. typically, SQL Server retains the previous six logs and returns the latest backup logs with a suffix. 1. The second latest suffix is given. 2. And so on.

 

The current error log has no suffix.

 

If you only want to collect the latest SQL Error Log and do not know the path, you can use the following stored procedure.

Sp_readerrorlog

Xp_readerrorlog

The two stored procedure have four parameters:

1. Value of error log file you want to read:0 = Current, 1 = archive #1, 2 = archive #2, Etc...
2. Log File Type:1 or null = Error Log, 2 = SQL Agent log
3. Search string 1:String one you want to search
4. Search string 2:String two you want to search for to further refine the results

Examples:

Returns all rows of the sixth archive error log.

Exec sp_readerrorlog 6

Returns all rows with 2005 and exec in the sixth error log.

Exec sp_readerrorlog 6, 1, '20140901', 'exec'

 

The disadvantage is that the display result of management studio is 256 characters by default, but this can be modified in the options.

Tools-> options-> query results-> SQL Server-> results to text-> maximum nunber of characters displayed in each column

 

From:

Viewing the SQL Server Error Log

Http://msdn.microsoft.com/en-us/library/ms187885.aspx

Reading the SQL server log files using tsql

Http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/