Category Archives: Locking

Transaction rollback in progress without an end

This is a quick post, I hope it’s useful to someone.

I was dealing with a rollback/kill process which I couldn’t kill.
An sp_whoisactive (by @adammachanic) showed a killed/rollback for more then 29 days!
A simple kill of spid 75 didn’t work.

 

This was the result of kill 75:

SPID 75: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds.


The result of sp_whoisactive showed me it was an xp_cmdshell command so I knew I had to look at the processes and try to kill it from there. Of course, the Task Manager didn’t give me any details so I fired up Process Explorer from sysinternals and this is what I got:

 

So all I had to do was kill the process from within Process Explorer and voila.

Locking during debugging stored procedure in Visual Studio 2005

I had this weird situation for almost two days whilst debugging a stored procedure in Visual Studio 2005. Just after a few minutes, over and over again at the same line, I got an error popping up:

Failed to retrieve data for this request.
Lock request time out period exceeded.
So obviously it had something to do with locking which is not unusal but it shouldn’t give me an error from within Visual Studio right?
I’m working on a standalone instance here, so no interferance from other applications or people with open connections in SSMS.
When I looked at the Activity Monitor in SSMS, I could see a LCK_M_S key lock caused by the query below which is not part of my stored procedure (It almost looks like Visual Studio is executing this query).
SELECT
db_name() AS [Database_Name],
udf.name AS [Name],
SCHEMA_NAME(udf.schema_id) AS [Schema],
udf.object_id AS [ID],
CAST(
 case
  when udf.is_ms_shipped = 1 then 1
  when (
    select
      major_id
    from
      sys.extended_properties
    where
      major_id = udf.object_id and
      minor_id = 0 and
      class = 1 and
      name = N''microsoft_database_tools_support'')
    is not null then 1
  else 0
end
       AS bit) AS [IsSystemObject],
CAST(CASE WHEN ISNULL(smudf.definition, ssmudf.definition) IS NULL THEN 1 ELSE 0 END AS bit) AS [IsEncrypted],
(case when ''FN'' = udf.type then 1 when ''FS'' = udf.type then 1 when ''IF'' = udf.type then 3 when ''TF'' = udf.type then 2 when ''FT'' = udf.type then 2 else 0 end) AS [FunctionType],usrt.name AS [DataType],sret_param.name AS [DataTypeSchema],
ISNULL(baset.name, N'''') AS [SystemType],
CAST(CASE WHEN baset.name IN (N''nchar'', N''nvarchar'') AND ret_param.max_length <> -1 THEN ret_param.max_length/2 ELSE ret_param.max_length END AS int) AS [Length],
CAST(ret_param.precision AS int) AS [NumericPrecision],
CAST(ret_param.scale AS int) AS [NumericScale],
case when amudf.object_id is null then N'''' else asmbludf.name end AS [AssemblyName],
case when amudf.object_id is null then N'''' else amudf.assembly_class end AS [ClassName],
case when amudf.object_id is null then N'''' else amudf.assembly_method end AS [MethodName],
CASE WHEN udf.type IN (''FN'',''IF'',''TF'') THEN 1 WHEN udf.type IN (''FS'',''FT'') THEN 2 ELSE 1 END AS [ImplementationType]
FROM
sys.all_objects AS udf
LEFT OUTER JOIN sys.sql_modules AS smudf ON smudf.object_id = udf.object_id
LEFT OUTER JOIN sys.system_sql_modules AS ssmudf ON ssmudf.object_id = udf.object_id
LEFT OUTER JOIN sys.all_parameters AS ret_param ON ret_param.object_id = udf.object_id and ret_param.is_output = 1
LEFT OUTER JOIN sys.types AS usrt ON usrt.user_type_id = ret_param.user_type_id
LEFT OUTER JOIN sys.schemas AS sret_param ON sret_param.schema_id = usrt.schema_id
LEFT OUTER JOIN sys.types AS baset ON baset.user_type_id = ret_param.system_type_id and baset.user_type_id = baset.system_type_id
LEFT OUTER JOIN sys.assembly_modules AS amudf ON amudf.object_id = udf.object_id
LEFT OUTER JOIN sys.assemblies AS asmbludf ON asmbludf.assembly_id = amudf.assembly_id
WHERE
(udf.type in (''TF'', ''FN'', ''IF'', ''FS'', ''FT''))
ORDER BY
[Database_Name] ASC,[Schema] ASC,[Name] ASC

The query started to show up in the Activity Monitor as soon as I expanded the stored procedure node from the Server Explorer pane where you connect to a database. I guess Visual Studio 2005 uses the info from the resultset to build the nodes in the Server Explorer/Database connection.

I started experimenting with the Visual Studio settings including the time out setting but I didn’t make any progress until I decided to switch to Visual Studio 2010.
The query responsable for the locking did no longer appear.