Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 750
  • Last Modified:

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

Hi,

On our Windows 2003 server standard edition. This is our production server

We have MSSQL 2005 installed

My sqlserver is using memory 1,693,192. Is it ok or not.

I can see in the Activity monitor that in database tempdb

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.

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

As we have many application running on the PC from differnt vendors

Please help me to identify this

Thanks and Regards
0
tia_kamakshi
Asked:
tia_kamakshi
  • 3
  • 2
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
spikellyCommented:
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!

0
 
tia_kamakshiAuthor Commented:
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
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
spikellyCommented:
Do have any spid or session_id for that query?
The DBCC INPUTBUFFER displays the last statement sent from a client to an instance of Microsoft SQL Server.
Now about the running query:it appears that the query Creates the table #tmpDBCCinputbuffer and tries to insert data in it. But the table created is a temporary table and should be explicitely dropped at the end of the query with a DROP statement like this: DROP TABLE #tmpDBCCinputbuffer .
So you can try this:
USE tempdb
GO
DROP TABLE #tmpDBCCinputbuffer

At the insert level [insert into #tmpDBCCinputbuffer exec ('DBCC INPUTBUFFER(60)') ] you can see that there is this: 'DBCC INPUTBUFFER(60)', the number 60 is the session id associated with an active primary connection. So to know who is executing that query, run the query below:

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 = '60'

In the Activity Monitor you should then have a Process ID column and a Process ID number for the running query; just take that number and KILL that process. Again in the Activity Monitor you DO have a User column which tells you who is running the query.

Use this to see all the queries used by any system account(just removed the WHERE clause):
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

Look at the column db_name to see what are the queries related to the tempdb; then KILL the process.

More here :http://www.mssqltips.com/tip.asp?tip=1363

Hope this will help...any issue? just post here...
0
 
tia_kamakshiAuthor Commented:
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
0
 
spikellyCommented:
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...
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now