Where to see SQL Server start/stop logs?

Question

I want to know where to see SQL Server start/stop logs for each instances and SQL Server agent/job start/stop logs? I am developing some tools to monitor SQL Server status. 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 current error log file is called ERRORLOG. If you stop and re-start SQL Server, the old log will be archived and a new one will be created. In addition, you can re-cycle the error log by executing the DBCC ERRORLOG command or the sp_cycle_errorlog system procedure.

http://sqlserverpedia.com/wiki/SQL_Server_Error_Logs

Answer 2

There are some undocumented but well know system procedures to read the errorlog from SQL itself:

• exec xp_enumerrorlogs 1 will list SQL Engine errorlog file numbers
• exec xp_readerrorlog <errorlognumber>, 1 will return the content of the requested Engine errorlog file.
• exec xp_enumerrorlogs 2 will list the Agent error log file numbers
• exec xp_readerrorlog <errorlognumber>, 2 will return the content of the requested Agent error log file.

These are the procedures invoked by Management Studio to show the Engine and Agent logs.

Answer 3

If you are using .NET you can also use the Microsoft.SqlServer namespace to get most of this data programatically. For example, I have used Microsoft.SqlServer.Rmo to get the status of subscribers to a merge replication publication. Depending on what you are using it for, you may be able to avoid accessing (and presumably parsing?) the logs directly.

Check out the Microsoft.SqlServer.Management.Smo.Agent namespace for data specific to the SQL agent.

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

• View and modify SQL Server Agent settings.
• Set up and manage operators.
• Set up and manage alerts based on system messages or performance conditions.
• Set up and manage jobs with multiple steps and schedules.
• Manage proxy accounts for the subsystems on which jobs run.
• Manage jobs that run on multiple servers.