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.
What can I do to resolve this?
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."
