[MSSQL] 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다. TRACEON / TRACEOFF / ERRORLOG 사용법

exec sp_readerrorlog 0,1, 'Logging SQL Server'
select serverproperty('errorlogfilename')

[MSSQL] 다른 프로세스와의 교착 상태가 발생하여 실행이 중지되었습니다

테이블 락에 의해 트랜잭션 교착상태가 발생한것으로 예상되는 메시지입니다.

교착상태(테이블 락lock) 발생에 대한 정보확인을 통해 원인 파악이 필요합니다.

아래 방법을 참고하세요

 

1. sp_lock : mode 가 X붙은거 확인

2. sp_who : spid 중복 과 같은 이상로그 검색

3. TRACEON 해서 로그 확인 (DB상에서 발생한 모든 로그 확인가능)

DBCC TRACESTATUS(1204) -- trace 상태 확인
DBCC TRACEON(1204,-1) -- trace 상태 설정
DBCC TRACEOFF(1204,-1) -- trace 상태 해제

 

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-traceon-trace-flags-transact-sql?view=sql-server-ver15#:~:text=To%20turn%20off%20a%20global,by%20using%20a%20startup%20option.

 

SQL 서버 Errorlog 저장 폴더 위치 조회

Errorlog 파일 저장 경로는 아래와 같이 조회 할 수 있습니다.

Trace flag	Description
260	Prints versioning information about extended stored procedure dynamic-link libraries (DLLs). For more information about __GetXpVersion(), see Creating Extended Stored Procedures.
1204	Returns the type of locks participating in the deadlock and the current command affected.
2528	Disables parallel checking of objects by DBCC CHECKDB, DBCC CHECKFILEGROUP, and DBCC CHECKTABLE. By default, the degree of parallelism is determined automatically by the query processor.  The maximum degree of parallelism is configured in the same manner as that of parallel queries. For more information, see max degree of parallelism Option. Parallel DBCC should typically be left enabled. In the case of DBCC CHECKDB, the query processor re-evaluates and automatically adjusts parallelism with each table or batch of tables checked. In some cases, checking may commence while the server is virtually idle. An administrator who knows that the load will increase before checking is complete may want to manually decrease or disable parallelism. However, disabling parallel checking can cause a decrease in overall database performance. Decreasing the degree of parallelism increases the amount of transaction log that must be scanned. This in turn increases the demand for tempdb space and results in a non-linear increase in the time required for dbcc to complete its checks. If DBCC is run with the TABLOCK feature enabled and parallelism turned off, tables may be locked for longer periods of time.
3205	By default, if a tape drive supports hardware compression, either the DUMP or BACKUP statement uses it. With this trace flag, you can disable hardware compression for tape drivers. This is useful when you want to exchange tapes with other sites or tape drives that do not support compression.