SQL Server logs, changing level of logging

To view SQL Server error log from SQL Server Enterprise Manager
– Expand a server group, and then expand a server.
– Expand Management, and then expand SQL Server Logs.
– Click the SQL Server Log to view it. Error log information appears in the details pane

One can also execute “exec xp_readerrorlog”

SQL server logs are generally located at “C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG”.

The log files are rolled over on each sql server instance restart, or when running DBCC ERRORLOG statement. sql server will only retain max 6 old file. This could be changed byselecting configure

How do Add detailed logging

Edit NSService.exe.config File

The default installation folder is C:\Program Files\Microsoft SQL Server\90\NotificationServices\n.n.nnn\bin.
Open the NSservice.exe.config file.

<?xml version=”1.0″ encoding=”UTF-8″?>
<!–
The default logging levels for all components is Warning

Off = 0 < Error = 1 < Warning = 2 < Info = 3 < Verbose = 4

Change the values of the value attribute to change the logging
level.

Setting the logging level enables all log events which are less
than or equal to the log level setting
–>
<configuration>
<system.diagnostics>
<switches>
<add name=”LogAdministrative” value=”2″/>
<add name=”LogService” value=”2″/>
<add name=”LogEventProvider” value=”2″/>
<add name=”LogEventCollector” value=”2″/>
<add name=”LogGenerator” value=”2″/>
<add name=”LogDistributor” value=”2″/>
<add name=”LogVacuumer” value=”2″/>
<add name=”LogOther” value=”2″/>
<add name=”LogPerformanceMonitor” value=”2″/>
</switches>
</system.diagnostics>
</configuration>

Initially, each logging option is set to 2, which turns on logging for error and warning messages only.
To apply the logging changes, save the file and restart the instance of Notification Services.

Element name	Logging category
LogAdministrative	SQL Server Management Studio and nscontrol utility events
LogService	NS$instanceName Microsoft Windows service events
LogEventProvider	Event provider events
LogEventCollector	EventCollector object events
LogGenerator	Generator events
LogDistributor	Distributor events
LogVacuumer	Vacuumer events
LogOther	Performance monitor events
LogPerformanceMonitor	Events for all other components, such as the standard content formatter and delivery protocols

Logging Level Values : Logging levels determine which types of events are written to the Application log. You can control the logging level by setting the value attribute to one of the following values.

0 Off
1 Error messages only
2 Error and warning messages
3 Error, warning, and informational messages
4 Verbose logging, which writes all messages to the Application log