By default, SQL Server Set Default Trace Enabled to true. The default trace log is stored by default in the \MSSQL\LOG directory using a rollover trace file. The base file name for the default trace log file is log.trc.
The above screenshot shows there are 5 rollover trace files created.
You can open them in SQL Server Profiler.Or, you can open them by fn_trace_gettable() function.
SELECT *FROM fn_trace_gettable
('D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\log.trc', default)
You need only the base name for the trace log file. The fn_trace_gettable() will retrieve data from all the trace files.
One example record:
Login failed for user 'sa'. The user is not associated with a trusted SQL Server connection. [CLIENT: 24.239.217.223]
Somebody is trying to connect to my SQL server but login failed.
By default, the trace log files are limited to 32 MB and deleted after 14 days. You can disable the Default Trace Enabled option.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Default Trace Enabled', 0;
GO
RECONFIGURE;
GO
As you see from the screenshot below, the Standard(Default) template defines what classes are traced. If you don't check where to save the trace logs, they are saved in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG.
SQL Server Profiler
Server processes trace data:specify that the server running the trace should process the trace data. Using this option reduces the performance overhead incurred by tracing. If selected, no events are skipped even under stress conditions. If this check box is cleared, processing is performed by SQL Server Profiler, and there is a possibility that some events are not traced under stress conditions.
It is better to run SQL Server Profiler from a test computer and configures the profiler to connect the production SQL server.
SQL Server 2005 is a trace provider. The SQL Server Profiler is the consumer. SQL Server 2005 provides a full Event Tracing for Windows (ETW) functionality which can output most of SQL Trace events available for SQL Server Profiler.
Check your server for the trace provider:
C:\>logman query providers
SQL SERVER ERROR LOG
It records:backup and restore operatioons, batch commands, and scripts.
As you see from the screenshot, the SQL Server error log has 6 rotation files. The current error log is ERRRORLOG. The last one is errorlog.6.
A new errorlog is created each time the SQL INSTANCE starts. You can manually rotate the SQL Server error log by using the system stored procedure sys_cycle_errorlog.
What does the cycle mean?
You can run several times: EXEC sys_cycle_errorlog
You will find the following:
ERRORLOG -->errorlog.1
errorlog.1-->errorlog.2
How do you move the location of SQL Server error log?
- Create a folder, e.g. C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\KKK
- Make sure that the KKK folder security is as the screenshot
When the SQL Server instance is running, open SQL Server configuration Manager, select the SQL Server Instance, properties, Advanced Tab, and Start Parameter.
SQL Server Agent Error Log
SQL Server Agent creates an error log that, by default, records warnings and errors. The following types of messages are displayed in the SQL Server Agent error log:
- Warning messages that provide information about potential problems, such as, "Job test was deleted while it was executing."
- Error messages that usually require intervention by a system administrator to resolve, such as, "Unable to start mail session." Error messages can be sent to a specific user or computer by network popup.
SQL Server maintains up to nine SQL Server Agent error logs. Each archived error log has an extension indicating the relative age of the error log. For example, an extension of .1 indicates the newest archived error log and an extension of .9 indicates the oldest archived error log.
The current log for SQL Server agent error log is sqlagent.out. To manually recycle the agent error log, run the following statements.
USE msdb ;
GO
EXEC dbo.sp_cycle_agent_errorlog ;
GO
To move the SQL Server agent error log to a new location:
- Start the SQL Server Agent
- Right-click the Error Logs under the SQL Server Agent of SSMS console and change the path
- Restart the SQL Server Agent to reflect the new path
====
View SQL Server Error Logs and SQL Server Agent Error Logs:
Text Editor or SSMS
====
Because I restart my SQL Server instance all the time, the log files rotate without reaching the maximum file size. What is the maximum size of a SQL Server error log file or a SQL server agent error log file? I don't know. Anyway, it will rotate when the current log file reaches the maximum size, for example, 5MB.
