十分紧急：sys.processes系统表有一条恐怖信息

山寨DBA 2014-03-31 05:43:33

诸位大神，小弟又遇到麻烦了，最近一台数据库服务器的CPU一直比较高，我执行如下SQL语句，



SELECT 

	spid,waittype,waittime,lastwaittype,waitresource,cpu,

	physical_io,memusage,login_time,last_batch,open_tran,cmd 

FROM 

	sys.[sysprocesses] 

WHERE 

	[spid]>50 AND [dbid]='5' and status='sleeping' 

order by cpu desc



dbcc inputbuffer(135)

查询sys.processes 系统表信息，发现如下图信息：

有一个进程打开了一个transaction，但是却处于sleeping状态，更烦人的事情是，它的CPU时间竟然还在持续不断的增长，图中信息能反应出来。
请问我该如何是好？

...全文

274 31 打赏收藏转发到动态举报

写回复

用AI写文章

31 条回复

切换为时间正序

请发表友善的回复…

发表回复

山寨DBA 2014-04-01

打赏
举报

引用 28 楼 DBA_Huangzj 的回复:

[quote=引用 25 楼 hwhmh2010 的回复:] [quote=引用 21 楼 hwhmh2010 的回复:] [quote=引用 17 楼 DBA_Huangzj 的回复:] select * from sys.dm_exec_connections where session_id =135

这句话执行没有返回值。[/quote] 这个我看错了，这个是由返回值的：

[/quote]查查这个IP地址[/quote] 查出来了，程序的一个应用程序的测试服务器，会连接到我们的数据库。。。感谢二位了，问题应该可以确定是程序了，谢谢谢谢。

山寨DBA 2014-04-01

打赏
举报

引用 26 楼 DBA_Huangzj 的回复:

[quote=引用 24 楼 hwhmh2010 的回复:] [quote=引用 19 楼 DBA_Huangzj 的回复:] 换一个：这里脚本你加个where条件筛选135那个sessionid

SELECT  DTST.[session_id] ,
        DES.[login_name] AS [Login Name] ,
        DB_NAME(DTDT.database_id) AS [Database] ,
        DTDT.[database_transaction_begin_time] AS [Begin Time] ,  
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],  
        CASE DTAT.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type] ,
        CASE DTAT.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended'
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State] ,
        DTDT.[database_transaction_log_record_count] AS [Log Records] ,
        DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] ,
        DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] ,
        DEST.[text] AS [Last Transaction Text] ,
        DEQP.[query_plan] AS [Last Query Plan]
FROM    sys.dm_tran_database_transactions DTDT
        INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id]
        INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id]
        INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id]
        LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
        OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 
-- ORDER BY [Duration ms] DESC;

我在order by之前加：where DTST.session_id=135 没有结果。[/quote]那不加where条件，全部返回[/quote] 不加任何条件，也没有结果呢。。。

發糞塗牆 2014-04-01

打赏
举报

引用 25 楼 hwhmh2010 的回复:

[quote=引用 21 楼 hwhmh2010 的回复:] [quote=引用 17 楼 DBA_Huangzj 的回复:] select * from sys.dm_exec_connections where session_id =135

这句话执行没有返回值。[/quote] 这个我看错了，这个是由返回值的：

[/quote]查查这个IP地址

山寨DBA 2014-04-01

打赏
举报

引用 23 楼 wufeng4552 的回复:

[quote=引用 21 楼 hwhmh2010 的回复:] [quote=引用 17 楼 DBA_Huangzj 的回复:] select * from sys.dm_exec_connections where session_id =135

这句话执行没有返回值。[/quote] select * from sys.dm_tran_session_transactions where session_id=135 得到 transaction_id 然后用DBA_Huangzj 加上条件查查看 sys.dm_tran_active_transactions where transaction_ID=[/quote] sys.dm_tran_active_transactions where transaction_ID= 这个里头也没有找到结果。。。

發糞塗牆 2014-04-01

打赏
举报

引用 24 楼 hwhmh2010 的回复:

[quote=引用 19 楼 DBA_Huangzj 的回复:] 换一个：这里脚本你加个where条件筛选135那个sessionid

SELECT  DTST.[session_id] ,
        DES.[login_name] AS [Login Name] ,
        DB_NAME(DTDT.database_id) AS [Database] ,
        DTDT.[database_transaction_begin_time] AS [Begin Time] ,  
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],  
        CASE DTAT.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type] ,
        CASE DTAT.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended'
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State] ,
        DTDT.[database_transaction_log_record_count] AS [Log Records] ,
        DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] ,
        DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] ,
        DEST.[text] AS [Last Transaction Text] ,
        DEQP.[query_plan] AS [Last Query Plan]
FROM    sys.dm_tran_database_transactions DTDT
        INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id]
        INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id]
        INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id]
        LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
        OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 
-- ORDER BY [Duration ms] DESC;

我在order by之前加：where DTST.session_id=135 没有结果。[/quote]那不加where条件，全部返回

山寨DBA 2014-04-01

打赏
举报

引用 21 楼 hwhmh2010 的回复:

[quote=引用 17 楼 DBA_Huangzj 的回复:]
select * from sys.dm_exec_connections where session_id =135

这句话执行没有返回值。[/quote]

这个我看错了，这个是由返回值的：

山寨DBA 2014-04-01

打赏
举报

引用 19 楼 DBA_Huangzj 的回复:

换一个：这里脚本你加个where条件筛选135那个sessionid

SELECT  DTST.[session_id] ,
        DES.[login_name] AS [Login Name] ,
        DB_NAME(DTDT.database_id) AS [Database] ,
        DTDT.[database_transaction_begin_time] AS [Begin Time] ,  
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],  
        CASE DTAT.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type] ,
        CASE DTAT.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended'
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State] ,
        DTDT.[database_transaction_log_record_count] AS [Log Records] ,
        DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] ,
        DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] ,
        DEST.[text] AS [Last Transaction Text] ,
        DEQP.[query_plan] AS [Last Query Plan]
FROM    sys.dm_tran_database_transactions DTDT
        INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id]
        INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id]
        INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id]
        LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
        OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 
-- ORDER BY [Duration ms] DESC;

我在order by之前加：where DTST.session_id=135 没有结果。

水族杰纶 2014-04-01

打赏
举报

引用 21 楼 hwhmh2010 的回复:

[quote=引用 17 楼 DBA_Huangzj 的回复:] select * from sys.dm_exec_connections where session_id =135

山寨DBA 2014-04-01

打赏
举报

引用 20 楼 wufeng4552 的回复:

[quote=引用 13 楼 hwhmh2010 的回复:] [quote=引用 10 楼 wufeng4552 的回复:] 1）从等待类型看没有等待任何资源 2）看看hostname 和program_name (从哪里执行，什么客户端引起的) 3）cpu还在持续增长吗

对的呀，我也看到没有等待任何资源。是从程序端那边执行的，我就怀疑是程序的问题，但是我首先要排查数据库问题呀，昨天还有今天我在测试机上做了十几个模拟实验了，都得不到截图中那样的结果。 CPU还在持续飙升，已经有：1339889 这么多了，还在涨[/quote] 首先你要去了解下程序里有没有和用户交互的事务方式比如：打开一个事务然后等待用户输入信息然后才能提交这个时候用户可能迟迟没有输入，可能用户离开了，出差了等等一直挂那里处于未提交的状态如果是这样这样的情况程序设计的禁忌不允许的 [/quote] 程序和数据库的交互是一直都有的，但是具体怎么导致的还不知道，待我排查了数据库问题之后，会找程序的boss谈这个事情

山寨DBA 2014-04-01

打赏
举报

引用 17 楼 DBA_Huangzj 的回复:

select * from sys.dm_exec_connections where session_id =135

这句话执行没有返回值。

水族杰纶 2014-04-01

打赏
举报

引用 13 楼 hwhmh2010 的回复:

[quote=引用 10 楼 wufeng4552 的回复:] 1）从等待类型看没有等待任何资源 2）看看hostname 和program_name (从哪里执行，什么客户端引起的) 3）cpu还在持续增长吗

發糞塗牆 2014-04-01

打赏
举报

换一个：这里脚本你加个where条件筛选135那个sessionid

SELECT  DTST.[session_id] ,
        DES.[login_name] AS [Login Name] ,
        DB_NAME(DTDT.database_id) AS [Database] ,
        DTDT.[database_transaction_begin_time] AS [Begin Time] ,  
 -- DATEDIFF(ms,DTDT.[database_transaction_begin_time], GETDATE()) AS [Durationms],  
        CASE DTAT.transaction_type
          WHEN 1 THEN 'Read/write'
          WHEN 2 THEN 'Read-only'
          WHEN 3 THEN 'System'
          WHEN 4 THEN 'Distributed'
        END AS [Transaction Type] ,
        CASE DTAT.transaction_state
          WHEN 0 THEN 'Not fully initialized'
          WHEN 1 THEN 'Initialized, not started'
          WHEN 2 THEN 'Active'
          WHEN 3 THEN 'Ended'
          WHEN 4 THEN 'Commit initiated'
          WHEN 5 THEN 'Prepared, awaiting resolution'
          WHEN 6 THEN 'Committed'
          WHEN 7 THEN 'Rolling back'
          WHEN 8 THEN 'Rolled back'
        END AS [Transaction State] ,
        DTDT.[database_transaction_log_record_count] AS [Log Records] ,
        DTDT.[database_transaction_log_bytes_used] AS [Log Bytes Used] ,
        DTDT.[database_transaction_log_bytes_reserved] AS [Log Bytes RSVPd] ,
        DEST.[text] AS [Last Transaction Text] ,
        DEQP.[query_plan] AS [Last Query Plan]
FROM    sys.dm_tran_database_transactions DTDT
        INNER JOIN sys.dm_tran_session_transactions DTST ON DTST.[transaction_id] = DTDT.[transaction_id]
        INNER JOIN sys.[dm_tran_active_transactions] DTAT ON DTST.[transaction_id] = DTAT.[transaction_id]
        INNER JOIN sys.[dm_exec_sessions] DES ON DES.[session_id] = DTST.[session_id]
        INNER JOIN sys.dm_exec_connections DEC ON DEC.[session_id] = DTST.[session_id]
        LEFT JOIN sys.dm_exec_requests DER ON DER.[session_id] = DTST.[session_id]
        CROSS APPLY sys.dm_exec_sql_text(DEC.[most_recent_sql_handle]) AS DEST
        OUTER APPLY sys.dm_exec_query_plan(DER.[plan_handle]) AS DEQP
ORDER BY DTDT.[database_transaction_log_bytes_used] DESC; 
-- ORDER BY [Duration ms] DESC;

發糞塗牆 2014-04-01

打赏
举报

引用 16 楼 hwhmh2010 的回复:

[quote=引用 12 楼 DBA_Huangzj 的回复:] 不紧急的话，研究一下，执行这个，然后上图

      SELECT    DTAT.transaction_id ,
                DTAT.[name] ,
                DTAT.transaction_begin_time ,
                CASE DTAT.transaction_type
                  WHEN 1 THEN 'Read/write'
                  WHEN 2 THEN 'Read-only'
                  WHEN 3 THEN 'System'
                  WHEN 4 THEN 'Distributed'
                END AS transaction_type ,
                CASE DTAT.transaction_state
                  WHEN 0 THEN 'Not fully initialized'
                  WHEN 1 THEN 'Initialized, not started'
                  WHEN 2 THEN 'Active'
                  WHEN 3 THEN 'Ended' -- only applies to read-only transactions 
                  WHEN 4 THEN 'Commit initiated'-- distributed transactions only 
                  WHEN 5 THEN 'Prepared, awaiting resolution'
                  WHEN 6 THEN 'Committed'
                  WHEN 7 THEN 'Rolling back'
                  WHEN 8 THEN 'Rolled back'
                END AS transaction_state ,
                CASE DTAT.dtc_state
                  WHEN 1 THEN 'Active'
                  WHEN 2 THEN 'Prepared'
                  WHEN 3 THEN 'Committed'
                  WHEN 4 THEN 'Aborted'
                  WHEN 5 THEN 'Recovered'
                END AS dtc_state
      FROM      sys.dm_tran_active_transactions DTAT
                INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id
      WHERE     [DTST].[is_user_transaction] = 1
      ORDER BY  DTAT.transaction_begin_time

版主的这个语句什么作用啊，我收集下，以后方便使用。已经执行，结果如下图：

[/quote]这个脚本用到什么dmv，你就去查对应的解释

發糞塗牆 2014-04-01

打赏
举报

select * from sys.dm_exec_connections where session_id =135

山寨DBA 2014-04-01

打赏
举报

引用 12 楼 DBA_Huangzj 的回复:

不紧急的话，研究一下，执行这个，然后上图

      SELECT    DTAT.transaction_id ,

                DTAT.[name] ,

                DTAT.transaction_begin_time ,

                CASE DTAT.transaction_type

                  WHEN 1 THEN 'Read/write'

                  WHEN 2 THEN 'Read-only'

                  WHEN 3 THEN 'System'

                  WHEN 4 THEN 'Distributed'

                END AS transaction_type ,

                CASE DTAT.transaction_state

                  WHEN 0 THEN 'Not fully initialized'

                  WHEN 1 THEN 'Initialized, not started'

                  WHEN 2 THEN 'Active'

                  WHEN 3 THEN 'Ended' -- only applies to read-only transactions 

                  WHEN 4 THEN 'Commit initiated'-- distributed transactions only 

                  WHEN 5 THEN 'Prepared, awaiting resolution'

                  WHEN 6 THEN 'Committed'

                  WHEN 7 THEN 'Rolling back'

                  WHEN 8 THEN 'Rolled back'

                END AS transaction_state ,

                CASE DTAT.dtc_state

                  WHEN 1 THEN 'Active'

                  WHEN 2 THEN 'Prepared'

                  WHEN 3 THEN 'Committed'

                  WHEN 4 THEN 'Aborted'

                  WHEN 5 THEN 'Recovered'

                END AS dtc_state

      FROM      sys.dm_tran_active_transactions DTAT

                INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id

      WHERE     [DTST].[is_user_transaction] = 1

      ORDER BY  DTAT.transaction_begin_time

版主的这个语句什么作用啊，我收集下，以后方便使用。
已经执行，结果如下图：

發糞塗牆 2014-04-01

打赏
举报

那你先别kill掉，慢慢查

山寨DBA 2014-04-01

打赏
举报

引用 11 楼 DBA_Huangzj 的回复:

你可以看到截图中的cmd是awaiting command，我怀疑是不是有人打开个begin tran然后搞些什么死循环之类的。如果已经很紧急，建议直接kill 135

不紧急，我就是想弄出原因到底是程序的问题，还是数据库的问题，如果可以排除数据库问题的话，我就直接联系程序的boss了，让他们以后写程序规范点。呵呵

山寨DBA 2014-04-01

打赏
举报

引用 10 楼 wufeng4552 的回复:

1）从等待类型看没有等待任何资源 2）看看hostname 和program_name (从哪里执行，什么客户端引起的) 3）cpu还在持续增长吗

对的呀，我也看到没有等待任何资源。是从程序端那边执行的，我就怀疑是程序的问题，但是我首先要排查数据库问题呀，昨天还有今天我在测试机上做了十几个模拟实验了，都得不到截图中那样的结果。 CPU还在持续飙升，已经有：1339889 这么多了，还在涨

發糞塗牆 2014-04-01

打赏
举报

不紧急的话，研究一下，执行这个，然后上图

      SELECT    DTAT.transaction_id ,
                DTAT.[name] ,
                DTAT.transaction_begin_time ,
                CASE DTAT.transaction_type
                  WHEN 1 THEN 'Read/write'
                  WHEN 2 THEN 'Read-only'
                  WHEN 3 THEN 'System'
                  WHEN 4 THEN 'Distributed'
                END AS transaction_type ,
                CASE DTAT.transaction_state
                  WHEN 0 THEN 'Not fully initialized'
                  WHEN 1 THEN 'Initialized, not started'
                  WHEN 2 THEN 'Active'
                  WHEN 3 THEN 'Ended' -- only applies to read-only transactions 
                  WHEN 4 THEN 'Commit initiated'-- distributed transactions only 
                  WHEN 5 THEN 'Prepared, awaiting resolution'
                  WHEN 6 THEN 'Committed'
                  WHEN 7 THEN 'Rolling back'
                  WHEN 8 THEN 'Rolled back'
                END AS transaction_state ,
                CASE DTAT.dtc_state
                  WHEN 1 THEN 'Active'
                  WHEN 2 THEN 'Prepared'
                  WHEN 3 THEN 'Committed'
                  WHEN 4 THEN 'Aborted'
                  WHEN 5 THEN 'Recovered'
                END AS dtc_state
      FROM      sys.dm_tran_active_transactions DTAT
                INNER JOIN sys.dm_tran_session_transactions DTST ON DTAT.transaction_id = DTST.transaction_id
      WHERE     [DTST].[is_user_transaction] = 1
      ORDER BY  DTAT.transaction_begin_time

發糞塗牆 2014-04-01