MSSQL Server error logs Location

MSSQL Server error logs can be found in the following kind of folder structure:

D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Log

By default, it keeps six ERRORLOGS, older than that are getting deleted.
Everytime, when MSSQL Server instance is restarted, a new ERRORLOG is created. Old ones are archived.

Troubleshooting who killed your session and reading the error log

The following link gives the steps to find the login name that killed your application session:
https://www.toadworld.com/platforms/sql-server/b/weblog/archive/2012/05/03/who-killed-my-sql-server-process


Though reading an ERRORLOG from MSSQL is faster using a TextEditor, the following URL gives the steps to grab the specific errors in MSSQL server instance itself:
http://sqlserverlearner.com/2011/read-sql-server-error-log-using-tsql-query

MSSQL Server comparison with other databases

The following link gives the white paper about comparison of MSSQL server with other commercial databases:

https://assets.microsoft.com/pt-pt/SQL_Server_Mission_Critical_Performance_TDM_White_Paper.pdf

Checking data page corruption in the database

The following command can be used to display corruption related information and repair (with additional option) the corruption.


DBCC CHECKDB([database_name]);

Checking deadlock in the ERRRLOG after enabling the trace

First, to check if a specific trace is enabled on SQLServer, the following command can be used. If this command does not return any result, then there were no traces enabled by DBA.

DBCC TRACESTATUS();

To turn on the trace for detecting the deadlock in the ERRORLOG, the traces (1204, 1222) can be enabled using the commands:

DBCC TRACEON (1204, -1);

DBCC TRACEON (1222, -1);

Then, using the SQLServerManagement Console, you can keep on checking the ERRORLOG, to find if there are any deadlock occurred on not.

If the ERRORLOG is too big, the search process will choke the performance of the database server. To avoid that. The ERRORLOGs can be copied to another server and the following command can be used to find the lines that displays deadlock related information.

findstr “deadlock”  ERRORLOG

Here, deadlock is the search string and the ERRORLOG is filename.

Once you find the deadlock related information, the traces can be turned off using the following commands:

DBCC TRACEOFF (1204, -1);

DBCC TRACEOFF (1222, -1);

How to check which process killed the application process?

In the database server, we need to search for the string Kill in the SQLServer’s Error Log.

In SQLServerManagementWizard, Select the menus Management->SQL Server Logs-> <ERRORLOG> -> Search

Enter the string “Kill”, then click Search. 

If the search affects the server's performance or takes long time (It happens if the Error-Log size is too big), the Error-Log file can be copied to another machine, and then the following command can be used to find the lines that have the string Kill.

Example:

 findstr "Kill" ERRORLOG

(NOTE: K is upper case in Kill.   ERRORLOG is filename)

This usually displays the Process ID of the killed process, and the Host process ID of the killing process.  Using the host_process_id, the login_name that killed the process can found from the the table sys.dm_exec_sessions.

select login_name from sys.dm_exec_sessions where host_process_id = 'host process id got from the log';