[스크랩] 오픈 트랜잭션 쿼리가 있는 DB 찾아보기

https://www.overtop.co.kr/139

오픈 트랜잭션 쿼리가 있는 데이터베이스 찾아보기

현재 오픈된 트랜잭션이 어느 데이터베이스에 있는지 찾는 방법은 여러 가지 있다.

이 활성화된 세션을 찾아야 블로킹세션을 찾을 수 있다.

아래와 같이 세션 2개를 열어서 확인해보자

 

-- session #1 tempdb 를 열고 실제 데이터베이스는 TD 에 있는 테이블을 업데이트 하고 있다.

use tempdb

go

 

begin tran

update A set diff = '1' from td.[dbo].[results] a

-- rollback

 

-- session #2

use tempdb

go

DBCC OPENTRAN

세션#1에서 tempdb를 오픈하고 Update를 하지만 실제는 TD 를 하고 있는 것을 찾아야 된다.

 

 

SELECT spid, dbid FROM SYS.SYSPROCESSES WHERE OPEN_TRAN = 1

select db_name(2)

 

현재 세션정보를 보면 tempdb 로 확인이 된다. 혹시나 오픈된 데이터베이스가 tempdb로 착각하면 안된다.

 

SELECT * FROM SYS.DM_TRAN_SESSION_TRANSACTIONS

 

 

위에서는 정말 잘못된 데이터베이스를 찾을수 있다. 실수하기 좋은 예이다.

실제 오픈 트랜잭션이 발생중인 데이터베이스를 찾으려면 sp_lock 으로 확인해보자.

sp_lock

 

select db_name(9) as DBName

 

/*

DBName

TD

*/

 

use TD – 실제 오픈 트랜잭션이 있는 데이터베이스에서 DBCC OPENTRAN을 수행해야 결과를 볼 수 있다

go

DBCC OPENTRAN

/*

SQL Server 실행 시간:

CPU 시간 = 0ms, 경과 시간 = 0ms

SQL Server 구문 분석 및 컴파일 시간:

CPU 시간 = 0ms, 경과 시간 = 0ms.

데이터베이스 'TD'의 트랜잭션 정보입니다.

 

가장 오래 전에 활성화된 트랜잭션:

SPID(서버 프로세스 ID): 54

UID(사용자 ID) : -1

이름 : user_transaction

LSN : (43:56:5)

시작 시간 : 09 14 2015 1:27:39:913PM

SID : 0x010500000000000515000000301322d263e67671bfb9b84833150000

DBCC 실행이 완료되었습니다. DBCC에서 오류 메시지를 출력하면 시스템 관리자에게 문의하십시오.

*/

출처: https://www.overtop.co.kr/139 [AWSin:티스토리]

 

 

https://dallasdbas.com/dbcc-opentran-simplified/

Examples of how to use DBCC Opentran

n my Top 10 SQL Server Functions post awhile back, I listed DBCC OPENTRAN as one of the top 3, and for good reason.

An Open transaction may simply be something that has not finished yet, or someone issued a BEGIN TRAN without a corresponding COMMIT or ROLLBACK.  Or as we will see at the end, replication is having issues.

You can use this against any database with minimal syntax and get back solid information very quickly.

?

1 2 3 4 5 6 7

--connect to sample db use MyDatabase go   --as generic as this command gets and still runs: DBCC OPENTRAN

Result if nothing is open:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I start and execute a DML (insert, update or delete) transaction with BEGIN TRAN and leave out the corresponding COMMIT, I get:

Transaction information for database ‘SmallData_BigLog’.Oldest active transaction:
SPID (server process ID): 64 <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:4620:1)
Start time : Apr 1 2017 4:59:02:307PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now, if I open a second transaction (in a new query window) and execute any DML statement without the COMMIT, and then run DBCC OPENTRAN again, I get:

Transaction information for database ‘SmallData_BigLog’.Oldest active transaction:
SPID (server process ID): 64 <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:4620:1)
Start time : Apr 1 2017 4:59:02:307PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Yes…the same output, as this is just showing the ONE oldest transaction.

I can run a query to show that there are two SPIDs with open transactions:

?

1 2 3 4 5

-- SELECT spid, blocked,[dbid],last_batch,open_tran FROM master.sys.sysprocesses WHERE open_tran <> 0

If I COMMIT spid 64 and re-run DBCC OPENTRAN, the SPID changes to the second transaction I started:

Transaction information for database ‘SmallData_BigLog’.Oldest active transaction:
SPID (server process ID): 52  <———–
UID (user ID) : -1
Name : user_transaction
LSN : (637:9603:1)
Start time : Apr 1 2017 5:11:20:830PM
SID : 0x0105000000000005150000004893a0845595b6fef515cd5de9030000
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If I COMMIT spid 52 and re-run DBCC OPENTRAN along with checking sysprocesses for open_tran <> 0 I get:

No active open transactions.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.spid blocked dbid last_batch open_tran
—— ——- —— ———————– ———(0 row(s) affected)

 

Now, all of that was just running DBCC OPENTRAN by itself.  There are additional options:

?

1 2	--specify dbname, dbid or 0 for the current database DBCC OPENTRAN (SmallData_BigLog)

You will get results in the same format as the previous examples.

You can suppress all messages, regardless of if a transaction is open or not (but I have no idea why this would help you…)

?

1	DBCC OPENTRAN (0) with no_infomsgs

Result:

Command(s) completed successfully.

 

If you needed to periodically capture the oldest transaction, in order to review later, use WITH TABLERESULTS:

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

-- TableResults only shows the oldest open tran -- useful running in a loop to load the oldest -- tran over time.   --create a temp table CREATE TABLE #OpenTranStatus ( ActiveTransaction varchar(25), Details sql_variant );   -- Execute the command, putting the results in the table. INSERT INTO #OpenTranStatus EXEC ('DBCC OPENTRAN (SmallData_BigLog) with tableresults') SELECT * FROM #OpenTranStatus DROP TABLE #OpenTranStatus

In the above, you could create a user table instead of a temp table of course…it depends on your needs.

One more particularly useful item you may see when running DBCC OPENTRAN by itself:

Transaction information for database ‘Music’.Replicated Transaction Information:
Oldest distributed LSN : (37:143:3)
Oldest non-distributed LSN : (37:144:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

 

If your database is participating in Replication as a Publisher, this may show up when running OPENTRAN, but it doesn’t necessarily mean that the transaction is actually open.  I set this up and stopped the Replication Log Reader and Distribution agent jobs.   I then added some data to a published table (article) and ran DBCC OPENTRAN to get the above result.  Note that there are two lines with LSN information in them (no SPIDs)

I then ran the Log Reader Agent job and got back:

Transaction information for database ‘Music’.Replicated Transaction Information:
Oldest distributed LSN : (37:157:3)
Oldest non-distributed LSN : (0:0:0)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I have verified that new records I inserted have been read by the log reader, AND distributed to the subscriber(s).  This means that while you are seeing

Oldest distributed LSN : (37:157:3)

There is not an error…just info.

If you have non-distributed LSNs, there is something to troubleshoot in the replication process which is way outside the scope of this post.  A non-distributed replicated transaction/LSN CAN cause some huge Log file growth, and need to be investigated.  If this happens frequently, use the TABLERESULTS option to log to a regular table and alert on it.

Hopefully this gives you some insight into various ways to use DBCC OPENTRAN as well as use cases for the various options.  90% of the time I run this, it is due to application transactions timing out, or log file growth issues.