Query always running in sql activity monitor and 1.6 GB memory usage by sqlserver.exe

>> How can I know which application is running this query which never stops

Kindly run sp_who2 which would show you which application initiated this request for the spid in Activity Monitor..

Kindly confirm whether it is running continuously or is it been called several times..

Use the query below to see what are the current running query on your server:

SELECT rq.session_id, rq.user_id,rq.start_time,rq.status,rq.command,db_name(rq.database_id),rq.total_elapsed_time,rq.granted_query_memory, t.text
      FROM sys.dm_exec_requests rq
      CROSS APPLY sys.dm_exec_sql_text(sql_handle) t
      WHERE session_id > 50

The query below will give you information about the processes (I selected useful columns, but you change to select * to have all columns):

SELECT spid,status,db_name([dbid]),hostname,loginame,[program_name],cmd,nt_domain,nt_username,net_address,net_library,cpu,physical_io,memusage,login_time,last_batch
FROM sys.sysprocesses
WHERE spid>50 --Remove the where clause to include system processes

Now that you have all those information, you can use the KILL command to stop the query pumping up your memory!

Hope this helped...! Cheers!

Above query only displays the same query executed.

But I can see from activity monitor list that query
create table #tmpDBCCinputbuffer ([Event Type] nvarchar(512), [Parameters] int, [Event Info] nvarchar(512))
insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(60)')
select [Event Info] from #tmpDBCCinputbuffer

is running on tempdb database

What does that mean?

How to find the main query? who is executing it

Thanks

Thanks for great explanation.

Did you mean If I kill this process that doesn't harm me.
Is it possible that this query is using lot of memory

Or it is not working, Its just ideal in running mode, as nothing may be inserting?

My sqlserver.exe is using memory 1,693,192. Is it acceptable.

Thanks

As it is a production server, i don't know if you can promptly restart the SQL service.This will depend on the activity on your server and which kind of application is running on it! Restarting the service could fix this,it take some few seconds, but be aware that you are on a production server.

I posted above a query with a "memusage" column;did you run it and check that column???
here it is again:
SELECT spid,status,db_name([dbid]),hostname,loginame,[program_name],cmd,nt_domain,nt_username,net_address,net_library,cpu,physical_io,memusage,login_time,last_batch
FROM sys.sysprocesses
ORDER by memusage DESC

When running this, care about three main columns: status,physical_io,memusage.if you find a big memusage for a process, run DBCC INPUTBUFFER(spid) ,where spid is the one related to the SELECT just above, to check what is that process doing actually...

>> How to find the main query?

Have you tried sp_who2 as I mentioned earlier..
You can also find this in the query below

select * from sys.sysprocesses

>> Did you mean If I kill this process that doesn't harm me.

Depends upon the activity or job running on that server..

>> Is it possible that this query is using lot of memory

Yes... It can..
You are creating a temp table and inserting records into it and later SELECT ing records from it..
This can be resource intensive if the no. of records in INSERT and SELECT are huge..