Where can I view SQL Server start / stop logs?

Question

I want to know where to see SQL Server start / stop logs for each instance and SQL / job agent start / stop logs? I am developing some tools for monitoring the state of SQL Server. I am using SQL Server 2008 Enterprise.

thanks in advance George

Answer 1

By default, the SQL Server error log is stored in the Program Files \ Microsoft SQL Server \ MSSQL \ Log directory. The most recent error log file is called ERRORLOG. If you stop and restart SQL Server, the old log will be archived and a new one will be created. Alternatively, you can re-run the error log cycle by executing the DBCC ERRORLOG command or the sp_cycle_errorlog procedure procedure.

http://sqlserverpedia.com/wiki/SQL_Server_Error_Logs

Answer 2

There are some undocumented but well-known system procedures for reading the error log from SQL itself:

• exec xp_enumerrorlogs 1 will list SQL Engine error log file numbers
• exec xp_readerrorlog <errorlognumber>, 1 will return the contents of the requested Engine error log file.
• exec xp_enumerrorlogs 2 display the agent error log file numbers
• exec xp_readerrorlog <errorlognumber>, 2 will return the contents of the requested agent error log file.

These are procedures called by Management Studio to display the Engine and Agent logs.

Answer 3

If you use .NET, you can also use the Microsoft.SqlServer namespace to retrieve most of this data programmatically. For example, I used Microsoft.SqlServer.Rmo to get subscriber status for publishing merge replication. Depending on what you use it for, you can avoid accessing (and supposedly parsing?) The logs directly.

See the Microsoft.SqlServer.Management.Smo.Agent namespace for SQL Agent-specific data.

Using the Microsoft.SqlServer.Management.Smo.Agent namespace, you can do the following :

• View and change SQL Server Agent settings.
• Setting up and managing operators.
• Configure and manage alerts based on system messages or performance conditions.
• Set up and manage tasks with multiple steps and schedules.
• Manage proxy accounts for the subsystems on which jobs are running.
• Manage jobs running on multiple servers.