DBA

● Database Level Audit Step-By-Step

SQL Server Error Logs

Overview  : Error Logs maintains events raised by SQL Server Database Engine or  SQL Server Agent, Error Logs are main source for troubleshooting SQL Server problems.

* SQL Server supports two types of error logs :

                * SQL Server Logs

                * SQL Agent Logs

Below points are recorded default in error logs :

    1.  SQL Server start up events including database recovery.

    2.  Backup and restore details.
    3.  Any failed SQL Server jobs
    4.  User defined error message which has WITH LOG clause.
    5.  Maintenance related DBCC statements, such as DBCC CHECKDB and DBCC CHECKALLOC.
    6.  Turning trace flags on or off.
    7.  SQL Servers usage of a particular session for a long period of time.

    8.  Starting and stopping Profiler traces

* By default SQL Server supports

                1 - Current Log

                6 - Archieve Logs

* Error logs are present in LOG folder of respective instance.

* We can read error logs using

                sp_readerrorlog

                xp_readerrorlog

* By default when the server was restarted the error logs are recycled automatically. We can recycle error logs using

sp_cycle_errorlog

* We can configure up to 99 error logs.

* How to Configure?

            * Go to Object Explorer

            * Management

            * R.C on SQL Server Logs

            * Configure       

            * Select checkbox " Limit the no of error logs..................."

                                Max no of logs= 20

            * OK

* To filter the events from error log

                sp_readerrorlog 0,1,'Error',null

Important : Even though sp_readerrorlog accepts only 4 parameters , the extended store procedure xp_readerrorlog accepts at least 7 parameters.

1-Value of error log file you want to read: 0 = current, 1 = Archive #1, 2 = Archive #2, etc...

2-Log file type : 1 or NULL = error log, 2 = SQL Agent log

3-Search string 1: String one you want to search for

4-Search string 2: String two you want to search for to further refine the results

5-Search from start time :   Start Date

6-Search to end time :      End Date

7-Sort order for results : N'asc' = Ascending and N'desc' = Descending.

Note : the example here shows the values in single quotes, but for later versions of SQL Server you may need to use double quotes or you might get this error.

Try this instead :

Or try this, putting N before each parameter.