To view the MS SQL SERVER error log

View Purpose:

The error log is viewed to ensure that the process has completed successfully (for example, backup and restore operations, batch commands, or other scripts and procedures). This can help detect any current or potential problems, including automatic recovery of information (especially if the SQL Server instance has stopped and restarted), kernel information, or other server-level error messages.

Storage location:

By default, the location of SQL error log (SQL SERVER 2005) is:

%ProgramFiles%\Microsoft SQL Server\MSSQL. N\mssql\log\errorlog and Errorlog. N (where n represents a number)

Supplement (turn from Xiaoxiang hermit, article chain interview at the end of the text):

　　

Version	Path
SQL SERVER 2005	Program Files\Microsoft SQL Server\MSSQL. N\mssql\log
SQL SERVER 2008	Program Files\Microsoft SQL Server\mssql10. Instance Name \mssql\log
SQL SERVER R2	Program Files\Microsoft SQL Server\mssql10_50. Instance Name \mssql\log

In SQL SERVER 2005, the error log is located by default in Program Files\Microsoft SQL Server\MSSQL. n\mssql\log\errorlog and errorlog. n file. Where MSSQL.N is distinguished by:

Mssql.1:sqlserver

Mssql.2:ssas

Mssql.3:sqlexpress

Mssql.4:ssrs

So, in general, you just need to focus on the log files in the MSSSQL.1 directory

Open this folder we will see 7 errorlog files, of which six are divided by,......, 6 as the suffix, the one without the suffix represents the current error log.

Whenever a DB instance restarts, a new Errorlog,sql server is created that retains the previous six logs and gives the most recent backup log prefix. 1, the second most recent given suffix. 2, and so on.

If you want to manually switch the error log can be used: sp_cycle_errorlog command, it can close the current error log, and do a sequential switch, 1 change 2,2 3, delete the last one,

This command can be used instead of rebooting to toggle the error log.

You can also use DBCC ERRORLOG to achieve the same functionality. In practice, it is also possible to execute the stored procedure by establishing a job schedule so that the log file size is controlled within a reasonable range.

How to view:

SQL Error Log can be viewed through SQL Server Management Studio or any text editor

Also, if you want to see only the most recent SQL Error Log, and if you don't know the path, you can use the following stored procedure.

Sp_readerrorlog

xp_readerrorlog

These two stored procedure will take four parameters:

1. Value of error log file 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 want to search for
4. Search String 2: String further refine the results want to search

For example:

Returns all rows of the sixth archive error log.

EXEC sp_readerrorlog 6

　　　　返回第六个Error log中的所有带有2005和exec的所有行.

EXEC sp_readerrorlog 6, 1, ‘2005‘, ‘exec‘

Because the default Management Studio display results are 256 characters long, you may not be able to view all the information, which can be modified by the options:

Tools->options->query results->sql server->results to text-> Maximum nunber of characters displayed in EAC H column

After writing this blog and found a master wrote (very full)

　　

Http://www.cnblogs.com/kerrycode/archive/2013/05/28/3104214.html

To view the MS SQL SERVER error log