The Error Log, also known as the SQL Server Error Log, is a valuable tool for
troubleshooting SQL Server issues.
By default, the Error Log is located at
<Installation directory>\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log.
The size and number of error is configurable. To change these settings, go to the “Startup
Parameters” tab in SQL Server Configuration Manager, as shown below:
Then expand the “Advanced” option and select “True” for the “@log_status” parameter to
enable ging to both the Windows Application Event and the text file, as shown below:
You can also choose to have the Error process write to a table in your database by
selecting “True” for the “@_dbtable” parameter:
The Error will be truncated each time SQL Server starts, so if you want to keep the contents
of the, you’ll need to periodically copy it to another location.
The Error can be copied to another location by using the “sp_cycle_errorlog” stored
procedure, as shown below:
EXEC sp_cycle_error
This will close the current Error file and open a new one. The old file will be backed up as
<installation directory>\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\Log\ERRORLOG.1.
You can also choose to have the Error process write to multiple files by selecting “True”
for the “@log_truncate_on_checkpoint” parameter:
In this case, when the checkpoint process occurs, the current log file will be backed up as
<installation directory>\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\\ERROR.1
and a new file will be created.
You can also use the “sys.sp_cycle_error stored procedure to cycle the error files
without shutting down and restarting SQL Server:
EXEC sys.sp_cycle_error
This will close the current Error file and create a new one without losing any information in
the process. However, if you have multiple files, only the active one will be copied to
the backup location. The other files will be truncated.
If you want to keep all of the files, you can use the “sys.sp_cycle_agent_error” stored
procedure:
EXEC sys.sp_cycle_agent_error
This will cycle the Error for all SQL Server Agent jobs. The current file will be backed up
as
<installation directory>\Microsoft SQL
Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAGENT.1
and a new file will be created.
You can also schedule this stored procedure to run automatically using SQL Server Agent.
The Error is a valuable tool for troubleshooting SQL Server issues. By default, it is located at
<installation directory>\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL.
The size and number of error is configurable. To change these settings, go to the “Startup
Parameters” tab in SQL Server Configuration Manager.
You can also choose to have the Error Logging process write to a table in your database by
selecting “True” for the “@_dbtable” parameter.
The Error will be truncated each time SQL Server starts, so if you want to keep the contents
of the, you’ll need to periodically copy it to another location.
The Error can be copied to another location by using the “sp_cycle_error” stored
procedure.
You can also use the “sys.sp_cycle_error” stored procedure to cycle the error log files
without shutting down and restarting SQL Server.
If you want to keep all of the files, you can use the “sys.sp_cycle_agent_error stored
procedure.
You can also schedule this stored procedure to run automatically using SQL Server Agent.
The Error is a valuable tool for troubleshooting SQL Server issues. By default, it is located at
<installation directory>\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log. The size
and number of error is configurable. To change these settings, go to the “Startup
Parameters” tab in SQL Server Configuration Manager. You can also choose to have the Error
Logging process write to a table in your database by selecting “True” for the “@_dbtable”
parameter. The Error will be truncated each time SQL Server starts, so if you want to keep
the contents of
Conclusion:
The Error is a valuable tool for troubleshooting SQL Server issues. By default, it is located at
<installation directory>\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\. The size and
number of error are configurable. To change these settings, go to the “Startup Parameters” tab in SQL
Server Configuration Manager. You can also choose to have the Error ging process write to a table in
your database by selecting “True” for the “@log_dbtable” parameter.
Read more: playfh
playfh com login