主页 / dba / 问题 / 336188
Accepted
Marcello Miorelli
Asked: 2024-02-24 00:56:43 +0800 CST

如何查看事务在提交之前已影响了多少条记录？或者如果我必须杀死它，回滚的成本有多大？

  • 772

我正在使用sqlwatch来监视我的一些服务器，它有一个从名为 的表中批量删除的作业dbo.sqlwatch_logger_snapshot_header

我遇到了很多锁定和阻塞问题，因为该表由不同的进程使用，然后我禁用了所有其他作业，但处理该表的作业除外。

我使用以下脚本禁用所有其他 sqlwatch 作业：

use distribution

set nocount on
set transaction isolation level read uncommitted

declare @Job_id uniqueidentifier
declare @job_name varchar(300)
declare @category_name varchar(300)
declare @body varchar(max)
declare @flag tinyint

declare @enabled bit = 0;  --0 - disable 1-enable
declare @subject varchar(300) = case when @enabled = 0 then 'Disabling  sqlwatch jobs on' + @@servername else 'Enabling sqlwatch jobs on' + @@servername end;
declare @job_enabled bit;

set @flag = 0
set @body = 'The following jobs are going to be ' +
          case when @enabled = 0 then 'Disabled' else 'Enabled' end
          + ' : '+char(10)+Char(13)


IF OBJECT_ID('tempdb.[dbo].[#LogReaderAgents]') IS NOT NULL 
DROP TABLE [dbo].[#LogReaderAgents] 

CREATE TABLE [dbo].[#LogReaderAgents] ( 
[job_id]         UNIQUEIDENTIFIER                 NOT NULL,
[job_name]       SYSNAME                          NOT NULL,
[category_name]  SYSNAME                          NOT NULL,
[enabled]        TINYINT                          NOT NULL)

INSERT INTO [dbo].[#LogReaderAgents] ([job_id],[job_name],[category_name],[enabled])
select job_id, job_name = sj.name, category_name=sc.name, sj.enabled
from  msdb.dbo.sysjobs sj
inner join msdb.dbo.syscategories sc
        on sj.category_id = sc.category_id
where 1=1
  and sj.name like 'SQLWATCH%'
 -- and sc.category_id in (10,13)
 and sj.name not in ('SQLWATCH-INTERNAL-RETENTION')

-- exec sp_gettabledef 'dbo.#LogReaderAgents'
-- exec sp_GetInsertList 'TEMPDB','DBO.#LogReaderAgents'


DECLARE c1 CURSOR FOR
       SELECT Job_id,job_name, category_name, [enabled]
       FROM #LogReaderAgents
OPEN c1
FETCH NEXT FROM c1
       INTO @Job_id,@job_name,@category_name,@job_enabled
       WHILE @@FETCH_STATUS = 0
       begin

              if (select top (1) stop_execution_date from msdb.dbo.sysjobactivity ja
                     where Job_ID = @Job_id
                           and
                     ja.start_execution_date IS NOT NULL
                     order by Start_execution_date desc) is not NULL
              begin
                     set @flag = 1
                     Print @job_name +' is ' + case when @job_enabled=1 then 'Enabled' else 'disabled' end 
                     exec msdb..sp_update_job @job_id = @job_id, @enabled = @enabled
                     set @Body = @Body + char(10)+char(13) + @job_name + ' -- ' + @category_name +' is ' + case when @job_enabled=1 then 'Enabled' else 'disabled' end 
              end
              
              FETCH NEXT FROM c1
       INTO @Job_id,@job_name,@category_name,@job_enabled
       end
CLOSE c1
DEALLOCATE c1

但是，此后作业仍在运行，但我没有看到该表中的记录数量有任何变化。

这是执行过程并批量删除的作业：

在此输入图像描述

进展缓慢但稳定，有 3720 万行

现在有3690万

在此输入图像描述

所以基本上它正在工作，所以一切都很好。

现在我的问题是：

假设我因任何原因不得不停止这份工作

这是批量删除，如果我现在必须停止这项工作，有多少行受到影响，回滚尚未提交的内容的成本有多大？

sql-server

1 个回答

  1. Best Answer

    您可以用来sys.dm_tran_database_transactions查明已写入了多少日志，从而确定是否需要回滚。

    select
  dt.database_transaction_log_bytes_used,
  dt.database_transaction_log_record_count,
  r.writes * 8 * 1024 as bytes_written
from sys.dm_tran_database_transactions dt
join sys.dm_tran_session_transactions tst on tst.transaction_id = dt.transaction_id
join sys.dm_exec_requests r on r.session_id = tst.session_id
where tst.session_id = @SomeSpidHere
  and dt.database_transaction_state <> 10;  -- not committed

    请注意，这database_transaction_log_record_count是生成的日志记录数，而不是受影响的行数。

    至于回滚数据需要多长时间：谁知道呢？它非常依赖于系统，并且不要忘记回滚是单线程的。

    • 3

相关问题