SQL Server logs, changing level of logging

Posted By Sagar Patil

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

Setting the logging level enables all log events which are less
than or equal to the log level setting
<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″/>

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

Leave a Reply

You must be logged in to post a comment.

Top of Page

Top menu