SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

时间:2021-02-23 01:09:59

 这一段时间,因为系统升级,新系统产生的数据长度,比原来的数据长度要长,所以说要扩充一下字段长度。

alter table TableName alter column ColumnName varchar(50)    --修改字段长度sql

在执行的时候,有这样一个情况。

例如Student表的Name字段长度是nvarchar(50),

假如想变成nvarchar(100),这种情况,立刻能执行成功。 

假如想变成nvarchar(20),这种情况,会执行很久。

 

有一些表没啥问题,但是有一个表数据量较大,差不多有1亿多,执行时间太长,还把所有数据表给锁住了(如下图)。不过这种情况数据还能用语句查询。

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

①我就查询出这个语句的spid。之后kill掉。

SELECT   session_id, r.status, r.start_time, r.command, s.text, r.wait_time, r.cpu_time,
r.total_elapsed_time, r.reads, r.writes, r.logical_reads, r.transaction_isolation_level
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) s
WHERE R.STATUS='running' kill 75

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

②但是连续kill了好几遍,依然没啥效果,还是提示锁住。所有就继续看下spid为75的状态。

  方法1: 

select spid, blocked,waittime,lastwaittype,dbid,last_batch,open_tran,hostprocess,cmd from sysprocesses where spid = 75

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

   方法2:

USE master;
GO
EXEC sp_who '' --指定process_id;

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

两种方法查询出来的都是KILLED/ROLLBACK,表示,现在数据在回滚。

③查询一下,数据的回滚状态。

KILL 75 WITH STATUSONLY; 

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

查了一下,这个情况要等好久,有的等两三天的都有,最好的解决办法无非是重启。

----------------------------------------------------解决办法------------------------------------------------

网上找了好久,找了找到了一篇结决办法。

1.查询出处于阻塞的sql线程。

SET
TRANSACTION
ISOLATION
LEVEL
READ
UNCOMMITTED SELECT db_id(DB_NAME(er.[database_id])) [DBID] ,er.[session_id] AS [SessionID] ,er.[command] AS [CommandType] ,est.[text] [StatementText] ,er.[status] AS [Status] ,CONVERT(DECIMAL(5, 2), er.[percent_complete]) AS [Complete_Percent] ,CONVERT(DECIMAL(38, 2), er.[total_elapsed_time] / 60000.00) AS [ElapsedTime_m] ,CONVERT(DECIMAL(38, 2), er.[estimated_completion_time] / 60000.00) AS [EstimatedCompletionTime_m] ,er.[last_wait_type] [LastWait] ,er.[wait_resource] [CurrentWait] FROM
sys.dm_exec_requests
AS er INNER
JOIN
sys.dm_exec_sessions
AS es ON er.[session_id] = es.[session_id] CROSS
APPLY sys.dm_exec_sql_text(er.[sql_handle]) est WHERE er.[command] like
'Killed%'

附:查询出正在执行的语句

SELECT   spid,
blocked,
DB_NAME(sp.dbid) AS DBName,
program_name,
waitresource,
lastwaittype,
sp.loginame,
sp.hostname,
a.[Text] AS [TextData],
SUBSTRING(A.text, sp.stmt_start / 2,
(CASE WHEN sp.stmt_end = -1 THEN DATALENGTH(A.text) ELSE sp.stmt_end
END - sp.stmt_start) / 2) AS [current_cmd]
FROM sys.sysprocesses AS sp OUTER APPLY sys.dm_exec_sql_text (sp.sql_handle) AS A
WHERE spid > 50
ORDER BY blocked DESC, DB_NAME(sp.dbid) ASC, a.[text];

2.查到spid是75,根据spid查询出来系统进程hostprocess为20540。

select spid, blocked,waittime,lastwaittype,dbid,last_batch,open_tran,hostprocess,cmd from sysprocesses where spid = 69

SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK

3.在执行这条语句的机器上,用cmd命令查询出来。

tasklist|findstr 进程号

如:tasklist|findstr 75

4.cmd中根据进程名称杀死进程

taskkill /f /t /im 进程名称

如:taskkill /f /t /im /svchost.exe

如果不行,只有等,或者重启了。

参考文章:1.SQL Server会话KILL不掉,一直处于KILLED /ROLLBACK状态情形浅析

2. 数据库某个进程一直处在KILLED/ROLLBACK

        3.百度 谷歌 新浪 执行 SQL 出现 Lock request time out period exceeded.

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

历史消耗

SELECT TOP 20
total_worker_time/1000 AS [总消耗CPU 时间(ms)],execution_count [运行次数],
qs.total_worker_time/qs.execution_count/1000 AS [平均消耗CPU 时间(ms)],
last_execution_time AS [最后一次执行时间],max_worker_time /1000 AS [最大执行时间(ms)],
SUBSTRING(qt.text,qs.statement_start_offset/2+1,
(CASE WHEN qs.statement_end_offset = -1
THEN DATALENGTH(qt.text)
ELSE qs.statement_end_offset END -qs.statement_start_offset)/2 + 1)
AS [使用CPU的语法], qt.text [完整语法],
qt.dbid, dbname=db_name(qt.dbid),
qt.objectid,object_name(qt.objectid,qt.dbid) ObjectName
FROM sys.dm_exec_query_stats qs WITH(nolock)
CROSS apply sys.dm_exec_sql_text(qs.sql_handle) AS qt
WHERE execution_count>1
ORDER BY total_worker_time DESC

  SQL Server扩充表字段长度,引发的意外KILLED/ROLLBACK