正文

sqlsqlserver问题疑难解答(代码片段)

author  author  2023-01-07  401

关键词：

/*********************************
FINDING OPEN TRANSACTIONS AND KILLING THEM
**********************************/

/*
If you fail to commit a transaction, it'll lock the table. To see which transactions are open, you can use DBCC OPENTRAN (Database Console Commands). 
OPENTRAN will show you the oldest open transaction. You can then use the KILL keyword to terminate the transaction (this'll also terminate the connection).
*/
DBCC OPENTRAN;

KILL 52; -- 52 is the server process ID for the transaction as listed on the 'SPID (server process ID):' line of DBCC OPENTRAN

/*********************************
HOW TO SEE WHAT QUERIES ARE BEING SENT TO THE DATABASE
**********************************/

/*
One method is to go to Tools and select SQL Server Profiler. You can then create a trace which will show all code hitting the database, including from other
users and applications.

A second method involves the following query which will show you queries based on session information (doesn't always work?).
*/
SELECT sdest.DatabaseName 
    ,sdes.session_id
    ,sdes.[host_name]
    ,sdes.[program_name]
    ,sdes.client_interface_name
    ,sdes.login_name
    ,sdes.login_time
    ,sdes.nt_domain
    ,sdes.nt_user_name
    ,sdec.client_net_address
    ,sdec.local_net_address
    ,sdest.ObjName
    ,sdest.Query
FROM sys.dm_exec_sessions AS sdes
INNER JOIN sys.dm_exec_connections AS sdec ON sdec.session_id = sdes.session_id
CROSS APPLY (
    SELECT db_name(dbid) AS DatabaseName
        ,object_id(objectid) AS ObjName
        ,ISNULL((
                SELECT TEXT AS [processing-instruction(definition)]
                FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
                FOR XML PATH('')
                    ,TYPE
                ), '') AS Query

    FROM sys.dm_exec_sql_text(sdec.most_recent_sql_handle)
    ) sdest
where sdes.session_id <> @@SPID

正文

sqlsqlserver问题疑难解答(代码片段)

sqlsqlserver的(代码片段)

sqlsqlserver临时表(代码片段)

sqlsqlserver游标示例(代码片段)

sqlsqlserver表信息(代码片段)

sqlsqlserver索引操作。(代码片段)

sqlsqlserver显示查询时间(代码片段)

sqlsqlserver合并表演示(代码片段)

sqlsqlserver中的常量(代码片段)

sqlsqlserver-1秒跟踪(代码片段)

sqlsqlserver中的组连接(代码片段)

sqlsqlserver查看缺少的索引(代码片段)

sqlsqlserver材质视图创建脚本(代码片段)

sqlsqlserver用0填充数字(代码片段)

sqlsqlserver查询表行计数(代码片段)

sqlsqlserver中判断表是否存在(代码片段)

sqlsqlserver数据库cpu用法(代码片段)

sqlsqlserver始终在可用性查询上。(代码片段)

sqlsqlserver-清除循环中的errorlogs表(代码片段)