aye its a cracking feature of SQL haha

yeah of course thanks to Jeff and Cebisa too 🙂

Hi Guys,

Thanks for all of your assistance,

I have just arranged for the SQL Server to be rebooted,

Due to some systems we have i cannot restart the services without other servers...

no worries Brian it has been good to get somebody else looking at with me,

yeah the kill with status only returns "SPID 205: transaction rollback in progress. Estimated rollback completion:...

thanks again Brian for your continued assistance,

there is currently nothing processing on the linked server that i can see as being called from this server,

what i think is strange is...

Hi Brian,

I am seeing that as the CMD column in sys.sysprocesses for the SPID,

Also in sp_Who2

Thanks

Shayn

Hi Jeff,

Thanks for your response,

No the job itself was just running a stored proc which called some other procs, some of which being on linked servers,

It didnt seem to even...

Hi Brian,

Thanks for your response,

I ran the query but it didn't return anything,

When i ommited the status = 'Rollback' line i can see that the session_id/SPID shows the bellow

Session_ID -...

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)...

restarting the server agent service has not had the desired effect,

the sql job has now stopped but the SPID remains in a KILLED/ROLLBACK state and the tempdb log file continues...

also, just to confirm, it is the TempDB log file that is growing not the data file,

not sure if that makes any difference?

i may be going at this from the wrong angle but the only issues that are being presented are a job stuck "Executing: 0(Unknown)" and a constantly growing TempDB, no...

Hi Cebisa,

Thanks for your reply,

Unfortunately i don't have a secondary to fail over to,

Yeah i am leaning towards a restart of the Server Agent Services but whilst it isn't, yet,...

..also, the CPU and MemUsage in sysProcesses hasn't changed for quite some time with the LastWaitType being PREEMPTIVE_OS_AUTHORIZATIONOPS

Nice reminder

Thanks Steve

confidently answer question

get it wrong

re-read question

notice the "-"

facepalm

drink more coffee....