dbForge Studio for SQL Server使用教程：在SQL Server中查找和删除不完整的未完成交易9(第1部分下）

dbForge Studio for SQL Server为有效的探索、分析SQL Server数据库中的大型数据集提供全面的解决方案，并设计各种 表以帮助作出合理的决策。

dbForge Studio for SQL Server最新试用版

高级格式化选项

为此，您需要更深入的自定义选项，请导航至“ SQL Complete”的“选项”窗口。从主菜单可以很容易地做到这一点。

• 进行基本格式调整
• 自定义通知设置

回到这一点，我们创建了表srv.SessionTran来记录丢失事务的终止会话。

如何根据删除操作创建表以存档丢失的交易

现在，以类似的方式，我们将创建一个表来存档根据删除操作从第一个表中选择的未清交易。

SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [srv].[KillSession]([ID] [int] IDENTITY(1,1) NOT NULL,[session_id] [smallint] NOT NULL,[transaction_id] [bigint] NOT NULL,[login_time] [datetime] NOT NULL,[host_name] [nvarchar](128) NULL,[program_name] [nvarchar](128) NULL,[host_process_id] [int] NULL,[client_version] [int] NULL,[client_interface_name] [nvarchar](32) NULL,[security_id] [varbinary](85) NOT NULL,[login_name] [nvarchar](128) NOT NULL,[nt_domain] [nvarchar](128) NULL,[nt_user_name] [nvarchar](128) NULL,[status] [nvarchar](30) NOT NULL,[context_info] [varbinary](128) NULL,[cpu_time] [int] NOT NULL,[memory_usage] [int] NOT NULL,[total_scheduled_time] [int] NOT NULL,[total_elapsed_time] [int] NOT NULL,[endpoint_id] [int] NOT NULL,[last_request_start_time] [datetime] NOT NULL,[last_request_end_time] [datetime] NULL,[reads] [bigint] NOT NULL,[writes] [bigint] NOT NULL,[logical_reads] [bigint] NOT NULL,[is_user_process] [bit] NOT NULL,[text_size] [int] NOT NULL,[language] [nvarchar](128) NULL,[date_format] [nvarchar](3) NULL,[date_first] [smallint] NOT NULL,[quoted_identifier] [bit] NOT NULL,[arithabort] [bit] NOT NULL,[ansi_null_dflt_on] [bit] NOT NULL,[ansi_defaults] [bit] NOT NULL,[ansi_warnings] [bit] NOT NULL,[ansi_padding] [bit] NOT NULL,[ansi_nulls] [bit] NOT NULL,[concat_null_yields_null] [bit] NOT NULL,[transaction_isolation_level] [smallint] NOT NULL,[lock_timeout] [int] NOT NULL,[deadlock_priority] [int] NOT NULL,[row_count] [bigint] NOT NULL,[prev_error] [int] NOT NULL,[original_security_id] [varbinary](85) NOT NULL,[original_login_name] [nvarchar](128) NOT NULL,[last_successful_logon] [datetime] NULL,[last_unsuccessful_logon] [datetime] NULL,[unsuccessful_logons] [bigint] NULL,[group_id] [int] NOT NULL,[database_id] [smallint] NOT NULL,[authenticating_database_id] [int] NULL,[open_transaction_count] [int] NOT NULL,[most_recent_session_id] [int] NULL,[connect_time] [datetime] NULL,[net_transport] [nvarchar](40) NULL,[protocol_type] [nvarchar](40) NULL,[protocol_version] [int] NULL,[encrypt_option] [nvarchar](40) NULL,[auth_scheme] [nvarchar](40) NULL,[node_affinity] [smallint] NULL,[num_reads] [int] NULL,[num_writes] [int] NULL,[last_read] [datetime] NULL,[last_write] [datetime] NULL,[net_packet_size] [int] NULL,[client_net_address] [nvarchar](48) NULL,[client_tcp_port] [int] NULL,[local_net_address] [nvarchar](48) NULL,[local_tcp_port] [int] NULL,[connection_id] [uniqueidentifier] NULL,[parent_connection_id] [uniqueidentifier] NULL,[most_recent_sql_handle] [varbinary](64) NULL,[LastTSQL] [nvarchar](max) NULL,[transaction_begin_time] [datetime] NOT NULL,[CountTranNotRequest] [tinyint] NOT NULL,[CountSessionNotRequest] [tinyint] NOT NULL,[InsertUTCDate] [datetime] NOT NULL, CONSTRAINT [PK_KillSession] PRIMARY KEY CLUSTERED([ID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOALTER TABLE [srv].[KillSession] ADD  CONSTRAINT [DF_KillSession_InsertUTCDate]  DEFAULT (getutcdate()) FOR [InsertUTCDate]GO

在此脚本中：

• sys.dm_exec_sessions和sys.dm_exec_connections引用系统视图
• InsertUTCDate 标识创建记录时的UTC日期和时间。

如果你对我们的产品感兴趣或者有任何疑问，欢迎咨询在线客服>>

标签：