这一段时间,因为系统升级,新系统产生的数据长度,比原来的数据长度要长,所以说要扩充一下字段长度。
alter table TableName alter column ColumnName varchar(50) --修改字段长度sql
在执行的时候,有这样一个情况。
例如Student表的Name字段长度是nvarchar(50),
假如想变成nvarchar(100),这种情况,立刻能执行成功。
假如想变成nvarchar(20),这种情况,会执行很久。
有一些表没啥问题,但是有一个表数据量较大,差不多有1亿多,执行时间太长,还把所有数据表给锁住了(如下图)。不过这种情况数据还能用语句查询。
①我就查询出这个语句的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
②但是连续kill了好几遍,依然没啥效果,还是提示锁住。所有就继续看下spid为75的状态。
方法1:
select spid, blocked,waittime,lastwaittype,dbid,last_batch,open_tran,hostprocess,cmd from sysprocesses where spid = 75
方法2:
USE master;
GO
EXEC sp_who '' --指定process_id;
两种方法查询出来的都是KILLED/ROLLBACK,表示,现在数据在回滚。
③查询一下,数据的回滚状态。
KILL 75 WITH STATUSONLY;
查了一下,这个情况要等好久,有的等两三天的都有,最好的解决办法无非是重启。
----------------------------------------------------解决办法------------------------------------------------
网上找了好久,找了找到了一篇结决办法。
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
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状态情形浅析
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