보미네 :: 보미네

« » 2025.2
일	월	화	수	목	금	토
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28

ssms 에서 테이블 리스트가 안보여요

카테고리 없음 / 2014. 2. 25. 17:31

jdbc 와 sqlserver 2012 를 쓰는 환경에서 테이블 리스트가 보이지 않음

원인은 set implicit_transaction on 때문임

유사한 상황을 재현하기 위해 begin tran 을 넣음

-- 상황 재현을 위한 쿼리

drop table tblx

begin tran

create table tblx (idx int)

waitfor delay '15:55:08.700'

insert into tblx values ( 1)

commit tran

돌고 있는 상황에서 ssms 테이블 리스트가 안열림

sp_lock2 로 추적하니 아래와 같은 결과를 얻음

54번에의해서 55번이 블럭되고 대기유형은 아래와 같음

LCK_M_S KEY: 8:281474978938880 (49a99ae85e12)

-- 그냥 한번 찾아보고

select * from sys.dm_tran_locks 에서 resource_associated_entity_id 정보를 보면 281474978938880 이 값이 같다.

-- 테이블 정보를 찾을려면 object_id 를 object_name 함수로 찾으면 된다.

select * from sys.partitions where hobt_id = 281474978938880

select object_name (34)

sysschobjs -- < 이 테이블에 락이 걸려서 ssms 의 테이블창이 안 열린다.

-- 아래 쿼리는 sysshobjc 테이블이기에 admin:localhost 로 접근해야 쿼리가 가능하다.

select *, %%lockres%% from sys.sysschobjs (nolock) where %%lockres%% = '(49a99ae85e12)' 로 확인할수 있다.

-- 이하 상세로그

create view dbo.v_objlist as select db_id('[AdventureWorks]') dbid

, name collate database_default name

, id

from [AdventureWorks].dbo.sysobjects

union all select db_id('[ssa]') dbid

, name collate database_default name

, id

from [ssa].dbo.sysobjects

union all select db_id('[linedev]') dbid

, name collate database_default name

, id

from [linedev].dbo.sysobjects

union all select db_id('[kimmsOut]') dbid

, name collate database_default name

, id

from [kimmsOut].dbo.sysobjects

######################################################################

세션정보

######################################################################

SESSION_ID session_id login_time host_name program_name host_process_id client_version client_interface_name security_id login_name nt_domain nt_user_name status context_info cpu_time memory_usage total_scheduled_time total_elapsed_time endpoint_id last_request_start_time last_request_end_time reads writes logical_reads is_user_process text_size language date_format date_first quoted_identifier arithabort ansi_null_dflt_on ansi_defaults ansi_warnings ansi_padding ansi_nulls concat_null_yields_null transaction_isolation_level lock_timeout deadlock_priority row_count prev_error original_security_id original_login_name last_successful_logon last_unsuccessful_logon unsuccessful_logons group_id

51 커밋된 읽기 51 2014-02-25 10:04:27.320 AD00033670 Microsoft SQL Server Management Studio 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 60 3 102 10179 2 2014-02-25 16:31:39.337 2014-02-25 16:31:39.337 819 5 1883 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 10000 0 0 1222 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

52 커밋된 읽기 52 2014-02-25 10:06:13.787 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 437 2 1247 3251 2 2014-02-25 11:02:24.377 2014-02-25 11:02:24.380 16 32 3782 1 2147483647 한국어 ymd 7 1 1 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

53 커밋된 읽기 53 2014-02-25 10:52:14.130 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 10:52:14.157 2014-02-25 10:52:14.157 0 0 0 1 2147483647 한국어 ymd 7 1 1 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

54 커밋된 읽기 54 2014-02-25 15:54:35.970 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk running 0x 0 2 9 10213 2 2014-02-25 16:34:20.020 2014-02-25 16:34:20.010 40 12 104 1 2147483647 한국어 ymd 7 1 1 1 0 1 1 1 1 2 -1 0 0 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

55 커밋된 읽기 55 2014-02-25 16:34:27.303 AD00033670 Microsoft SQL Server Management Studio 1072956 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk running 0x 63 3 38 20047 2 2014-02-25 16:37:21.930 2014-02-25 16:37:21.930 86 0 432 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 10000 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

56 커밋된 읽기 56 2014-02-25 16:34:39.387 AD00033670 Microsoft SQL Server Management Studio - 쿼리 1072956 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 16:34:39.450 2014-02-25 16:34:39.450 0 0 0 1 2147483647 한국어 ymd 7 1 1 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

57 커밋된 읽기 57 2014-02-25 16:34:39.470 AD00033670 Microsoft SQL Server Management Studio - 쿼리 1072956 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 16:34:39.470 2014-02-25 16:34:39.470 0 0 0 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

58 커밋된 읽기 58 2014-02-25 16:34:39.473 AD00033670 Microsoft SQL Server Management Studio - 쿼리 1072956 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 16:34:39.473 2014-02-25 16:34:39.473 0 0 0 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

59 커밋되지 않은 읽기 59 2014-02-25 16:35:26.173 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk running 0x 1045 2 1897 2074 2 2014-02-25 16:37:23.620 2014-02-25 16:37:23.620 453 34 3516 1 2147483647 한국어 ymd 7 1 1 1 0 1 1 1 1 1 -1 0 0 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

60 커밋된 읽기 60 2014-02-25 16:35:26.213 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 16:35:26.213 2014-02-25 16:35:26.213 0 0 0 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

61 커밋된 읽기 61 2014-02-25 16:35:26.217 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 6 .Net SqlClient Data Provider 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk AD00033670 minsouk sleeping 0x 0 2 0 0 2 2014-02-25 16:35:26.217 2014-02-25 16:35:26.217 0 0 0 1 -1 한국어 ymd 7 1 0 1 0 1 1 1 1 2 -1 0 1 0 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE8030000 AD00033670\minsouk NULL NULL NULL 2

######################################################################

락인포 어뷰징 확인 200개 ver 0.1

######################################################################

rsc_text cnt req_status dbname objname IndId Type Mode req_ownertype

(49a99ae85e12) 1 대기중 kimmsOut NULL 1 Key S:공유 트랜잭션

14 허가됨 msdb NULL 2 Table Sch-S:스키마 안전성 트랜잭션

(001c10b9621f) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

(029d654e9895) 1 허가됨 kimmsOut NULL 2 Key X:단독 트랜잭션

(059f882170b0) 1 허가됨 kimmsOut NULL 4 Key X:단독 트랜잭션

(1e1f3ae616ed) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

(49a99ae85e12) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

(7bd2f52ae606) 1 허가됨 master NULL 2 Key S:공유 트랜잭션

(84194b097678) 1 허가됨 kimmsOut NULL 2 Key X:단독 트랜잭션

(95cb5906b3ba) 1 허가됨 kimmsOut NULL 3 Key X:단독 트랜잭션

(95f7fd14c1f4) 1 허가됨 kimmsOut NULL 2 Key X:단독 트랜잭션

(aade3f4bd969) 1 허가됨 kimmsOut NULL 2 Key X:단독 트랜잭션

(cd98738377da) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

(d1a25d473b6f) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

(e5e62adbc7d5) 1 허가됨 kimmsOut NULL 1 Key X:단독 트랜잭션

8(1:0:0) 1 허가됨 kimmsOut NULL 0 NULL Sch-S:스키마 안전성 트랜잭션

경고: 집계 또는 다른 SET 작업에 의해 Null 값이 제거되었습니다.

######################################################################

헤드블럭만 보기 by minsouk@hotmail.com ver 0.1

######################################################################

spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end request_id

54 1280 0 0x00BD 183769 WAITFOR 8 1 0 52 2 2014-02-25 15:54:35.970 2014-02-25 16:34:20.010 0 1 suspended 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE803000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 WAITFOR AD00033670 minsouk 55CDD90EE33E LPC AD00033670\minsouk 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010008007DDA921BC039B8850000000000000000 96 156 0

######################################################################

헤드블럭 쿼리보기 by minsouk@hotmail.com ver 0.1

######################################################################

#########################

dbcc inputbuffer for spid 54

#########################

qry_type dbname objname text

-------- -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Proc kimmsOut NULL

begin tran

create table tblx (idx int)

waitfor delay '15:55:08.700'

insert into tblx values ( 1)

commit tran

EventType Parameters EventInfo

-------------- ---------- ------------------------------------------------------------------------------------------------------------------------------

Language Event 0

begin tran

create table tblx (idx int)

waitfor delay '15:55:08.700'

insert into tblx values ( 1)

commit tran

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

######################################################################

락트리 보기 by minsouk@hotmail.com ver 0.2

######################################################################

블럭카운트 : 1

######################################################################

hh:mm:ss locktree spid kpid blocked waittype waittime lastwaittype waitresource dbid uid cpu physical_io memusage login_time last_batch ecid open_tran status sid hostname program_name hostprocess cmd nt_domain nt_username net_address net_library loginame context_info sql_handle stmt_start stmt_end request_id

00:03:02 54 54 1280 0 0x00BD 183808 WAITFOR 8 1 0 52 2 2014-02-25 15:54:35.970 2014-02-25 16:34:20.010 0 1 suspended 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE803000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AD00033670 Microsoft SQL Server Management Studio - 쿼리 717196 WAITFOR AD00033670 minsouk 55CDD90EE33E LPC AD00033670\minsouk 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010008007DDA921BC039B8850000000000000000 96 156 0

00:00:01 54 > 55 55 30236 54 0x0003 1976 LCK_M_S KEY: 8:281474978938880 (49a99ae85e12) 8 1 63 86 3 2014-02-25 16:34:27.303 2014-02-25 16:37:21.930 0 0 suspended 0x010500000000000515000000C64261D7FEDF1E8E80857ABAE803000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 AD00033670 Microsoft SQL Server Management Studio 1072956 SELECT AD00033670 minsouk 0145FA5FEC6C LPC AD00033670\minsouk 0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000 0x010008001FEE280D203000800000000000000000 710 -1 0

######################################################################

락인포 보기 by minsouk@hotmail.com ver 0.5

######################################################################

락인포 허가 200개 exclude rsc_type db by minsouk@hotmail.com ver 0.6

######################################################################

spid dbname objname IndId Type rsc_type rsc_text Mode req_mode req_status req_refcnt req_lifetime req_ecid (isParallel) req_ownertype req_transactionID req_transactionUOW (isDTC)

54 kimmsOut NULL 0 Table 5 IX:내재 단독 8 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 2 NULL 12 IX:내재 단독 8 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (001c10b9621f) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 2 Key 7 (84194b097678) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (1e1f3ae616ed) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 Table 5 IX:내재 단독 8 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 2 Key 7 (95f7fd14c1f4) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (49a99ae85e12) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 3 Key 7 (95cb5906b3ba) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 4 Key 7 (059f882170b0) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 NULL 12 Sch-M:스키마 수정 2 허가됨 2 0 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 NULL 11 8(1:0:0) Sch-S:스키마 안전성 1 허가됨 3 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 Table 5 IX:내재 단독 8 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (cd98738377da) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 2 Key 7 (029d654e9895) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 Table 5 IX:내재 단독 8 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (d1a25d473b6f) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 Table 5 Sch-M:스키마 수정 2 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 0 Table 5 IX:내재 단독 8 허가됨 1 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 1 Key 7 (e5e62adbc7d5) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

54 kimmsOut NULL 2 Key 7 (aade3f4bd969) X:단독 5 허가됨 0 33554432 0 트랜잭션 142742 00000000-0000-0000-0000-000000000000

55 msdb NULL 0 Table 5 IS:내재 공유 6 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

55 kimmsOut NULL 0 Table 5 Sch-S:스키마 안전성 1 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

55 msdb NULL 0 Table 5 IS:내재 공유 6 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

55 master NULL 0 Table 5 Sch-S:스키마 안전성 1 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

55 master NULL 2 Key 7 (7bd2f52ae606) S:공유 3 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

55 kimmsOut NULL 0 Table 5 Sch-S:스키마 안전성 1 허가됨 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

######################################################################

락인포 변환 200개 by minsouk@hotmail.com ver 0.5

######################################################################

spid dbname objname IndId Type rsc_type rsc_text Mode req_mode req_status req_refcnt req_lifetime req_ecid (isParallel) req_ownertype req_transactionID req_transactionUOW (isDTC)

######################################################################

락인포 대기 200개 by minsouk@hotmail.com ver 0.5

######################################################################

spid dbname objname IndId Type rsc_type rsc_text Mode req_mode req_status req_refcnt req_lifetime req_ecid (isParallel) req_ownertype req_transactionID req_transactionUOW (isDTC)

55 kimmsOut NULL 1 Key 7 (49a99ae85e12) S:공유 3 대기중 1 0 0 트랜잭션 151388 00000000-0000-0000-0000-000000000000

######################################################################

블럭되는 쿼리보기 sql_handle 별 50개 by minsouk@hotmail.com ver 0.2

######################################################################

|||||||||||||||||||||||||

dbcc inputbuffer for spid 55

|||||||||||||||||||||||||

qry_type dbname objname text

Proc kimmsOut NULL (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000))

DECLARE @is_policy_automation_enabled bit

SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)

FROM msdb.dbo.syspolicy_configuration

WHERE name = 'Enabled')

SELECT

tbl.name AS [Name],

SCHEMA_NAME(tbl.schema_id) AS [Schema],

'Server[@Name=' + quotename(CAST(

serverproperty(N'Servername')

AS sysname),'''') + ']' + '/Database[@Name=' + quotename(db_name(),'''') + ']' + '/Table[@Name=' + quotename(tbl.name,'''') + ' and @Schema=' + quotename(SCHEMA_NAME(tbl.schema_id),'''') + ']' AS [Urn],

case when 1=@is_policy_automation_enabled and exists (select * from msdb.dbo.syspolicy_system_health_state where target_query_expression_with_id like 'Server' + '/Database\[@ID=' + convert(nvarchar(20),dtb.database_id) + '\]'+ '/Table\[@ID=' + convert(nvarchar(20),tbl.object_id) + '\]%' ESCAPE '\') then 1 else 0 end AS [PolicyHealthState],

tbl.create_date AS [CreateDate],

CAST(

case

when tbl.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = tbl.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit) AS [IsSystemObject]

FROM

master.sys.databases AS dtb,

sys.tables AS tbl

WHERE

(CAST(

case

when tbl.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = tbl.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit)=@_msparam_0)and((db_name()=@_msparam_1)and(dtb.name=db_name()))

ORDER BY

[Schema] ASC,[Name] ASC

EventType Parameters EventInfo

Language Event 0 (@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000))

DECLARE @is_policy_automation_enabled bit

SET @is_policy_automation_enabled = (SELECT CONVERT(bit, current_value)

FROM msdb.dbo.syspolicy_configuration

WHERE name = 'Enabled')

SELECT

tbl.name AS [Name],

SCHEMA_NAME(tbl.schema_id) AS [Schema],

'Server[@Name=' + quotename(CAST(

serverproperty(N'Servername')

tbl.create_date AS [CreateDate],

CAST(

case

when tbl.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = tbl.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit) AS [IsSystemObject]

FROM

master.sys.databases AS dtb,

sys.tables AS tbl

WHERE

(CAST(

case

when tbl.is_ms_shipped = 1 then 1

when (

select

major_id

from

sys.extended_properties

where

major_id = tbl.object_id and

minor_id = 0 and

class = 1 and

name = N'microsoft_database_tools_support')

is not null then 1

else 0

end

AS bit)=@_msparam_0)and((db_name()=@_msparam_1)and(dtb.name=db_name()))

ORDER BY

[Schema] ASC,[Name] ASC

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

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

SQL Server 2012 Extended Events Demo

카테고리 없음 / 2014. 1. 15. 02:09

SQL Server 2012 XEvent Excel 분석

[사용방법 : 단 3개의 Step 으로 분석이 가능합니다.]

1. 정의된 Extended Events를 서버에 자동시작으로 등록
(서버가 시작될 때 항상 시작하고, 24시간 로그를 가지고 있습니다. 24시간 내 언제든지 분석가능 )

2. 파일로딩

3. Excel 리프레시

다른 아무런 작업이 필요없이 아래와 같이 분석 가능합니다.

2014년 3월중에 만나보실 수 있습니다.

email : a.to.z@navercorp.com

소개 동영상

https://www.facebook.com/photo.php?v=644916662237134&l=5083054757582591599

http://cafe.naver.com/sqlmvp/4413

아래 그래프 분석에서는 해당 그리드를 클릭하면 해당 쿼리를 볼 수 있으며, 필터를 이용해 특정 쿼리를 모니터링 가능 합니다.

그러므로 병목리소스, 대기분석, 관련쿼리를 식별해 느린 서버응답의 Root Cause 를 알 수 있습니다.

wait type 을 클릭하면 전체기간의 wait 비율과, 언제 어떤 쿼리와 연관된 대기인지 보입니다.

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

잠금 리소스 해석

카테고리 없음 / 2013. 12. 30. 18:17

http://www.sqlnotes.info/2011/10/24/locking-and-blocking-5-lock-resources-in-extended-events/

right('0X00EE00',2) 1자 초과로 읽을떄 처음 읽는 값이 null이믄 제외하고 읽더라고

SELECT *,%%lockres%%,SYS.FN_PHYSLOCFORMATTER(%%PHYSLOC%%) [FILEID:PAGEID:SLOTID],SYS.FN_GETROWSETIDFROMROWDUMP(%%ROWDUMP%%),%%rowdump%% partionid FROM DBO.TEST;

by 안인옥~

저작자표시 비영리 변경금지

Posted by 보미아빠

, |

«이전 1 ··· 88 89 90 91 92 93 94 ··· 171 다음»

보미네

카테고리

달력

공지사항

태그목록

최근에 올라온 글

ssms 에서 테이블 리스트가 안보여요

SQL Server 2012 Extended Events Demo

잠금 리소스 해석

최근에 달린 댓글

최근에 받은 트랙백

글 보관함

링크

티스토리툴바