Why Killed SPID on xp_LogInfo not rolling back and growing tempdb?
I received a notification that the space on the drive my tempdb sits on was running low, when checking the running process it pointed to a server agent task. I checked the scheduled tasks and we had one that had been running for over a day, usually it runs for less than a minute, and it was showing a status of "Executing: 0(Unknown)" in the job activity monitor.
When I tried to stop the Job in Activity Monitor nothing happened, it just kept on running and filling up tempdb to almost the full size of the disc. Knowing that the job itself could just be stopped and re-run without affecting the business, I, perhaps too hastily, killed the SPID, thinking this would "release" space back to tempdb and I would then restart the services out of hours to shrink the files.
Unfortunately this didn't work and the SPID has been in a Killed/rollback state for nearly a day now whilst the tempdb files grow (I arranged for the disk they sit on to be increased whilst I looked into it). When I check the process for the SPID it appears to be running xp_LogInfo with the last batch relating to insert #nt select distinct domain+N''+name, sid, sidtype from OpenRowset(TABLE NETUSERGETGROUPS, @acctnam.
I believe that the owner of the SQL Job may have reset their password when the job kicked in to cause this (this is just me guessing) which has caused it to get stuck in a loop.
The CPU and MemUsage in sysprocesses hasn't changed for over a day now and the LastWaitType is showing as PREEMPTIVE_OS_AUTHORIZATIONOPS.
When running DBCC INPUTBUFFER for the SPID I previously killed I am receiving this:
EventType - Language Event Parameters - 0 EventInfo - (@P1 nvarchar(128),@P2 uniqueidentifier)EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1, @job_id = @P2
I have now restarted the SQL Server Agent Service which stopped the job running but the tempdb log continues to grow.
How sql server kill spid? And how to resolve it?
When running DBCC OPENTRAN(TEMPDB) I get the below, the spid it relates to is the one I killed:
Transaction information for database 'tempdb'. Oldest active transaction: SPID (server process ID): 205 UID (user ID) : -1 Name : INSERT EXEC LSN : (5293:26478:20) Start time : Oct 19 2020 3:30:02:193PM SID : 0x0105000000000005150000005f9b10c13b99fcfc4ad890fd8e220200 DBCC execution completed. If DBCC printed error messages, contact your system administrator.
kill {spid} with statusonly returned "SPID 205: transaction rollback in progress. Estimated rollback completion: 0%. Estimated time remaining: 0 seconds."
For sql server kill spid, you have to scroll down to the SPID of the process you would like to kill. Right-click on that line and select 'Kill Process'. A popup window will open for you to confirm that you want to kill the process. Once this is done, the process will be terminated and all uncompleted transactions will begin the rollback process. Once we KILL SPID in SQL Server, it becomes an uncompleted transaction. SQL Server must undo all changes performed by the uncompleted transaction and database objects should be returned to the original state before these transactions.