SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to find the start time of query in the database


How to find the start time of query in the database

Author
Message
anusharani_c
anusharani_c
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 13
I am creating a stored procedure to identify, and kill a long running Business Objects query in the SQL Server database. I am able to identify the BO SQL iin the database with the BO user id, and also able to check the status "RUNNABLE" in sys_processes table, but last_batch column keeps updating timestamp for EACH REQUEST sent to the DB for the same BO query. Hence, I am unable to identify the start time, and to find out how long the BO SQL has been running. Does anyone know, how can I accomplish this ?
vstitte
vstitte
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 407
What version of SQL Server are running?



vstitte
vstitte
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 407
Check this article. See if this helps.
http://www.sqlservercentral.com/scripts/T-SQL/66830/



Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97255 Visits: 33013
For 2005/2008, take a look at sys.dm_exec_syessions. It will give you a basic breakdown of information for the connections in the system, including start times on the last batch. For more detailed information, take a look at sys.dm_exec_requests. That will give you very specific information about what a process is waiting for, what it's doing, what statement it's on within the batch, etc. In some cases, not all, not even most, it will also show a percent complete, but don't rely on that value in any way. It may or may not be accurate and useful.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
anusharani_c
anusharani_c
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 13
SQL 2005
anusharani_c
anusharani_c
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 13
This SP gets the start time of the running SQL from system table dm_exec_* and system function dm_exec_sql.

My understanding is ->One SQL sends multiple requests to the db, the start time in dm_* tables all point to the start time of EACH REQUEST, thus at a given point of time, I am unable to determine when the SQL started.
anusharani_c
anusharani_c
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 13
My understanding is ->One SQL sends multiple requests to the db, the start time in dm_exec_* tables all point to the start time of EACH REQUEST, thus at a given point of time, I am unable to determine when the SQL started.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97255 Visits: 33013
When you say SQL, do you mean an individual SQL statement? One that is currently running? I'm not entirely sure, but you may not be able to drill down to that level without capturing statements in a trace. That's one way of doing it, but you would already have to have the trace in place. You couldn't turn it on in a moment's notice.

I'll have to test the DMV to see if it records a statement or a batch start time.

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
anusharani_c
anusharani_c
SSC-Enthusiastic
SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)SSC-Enthusiastic (132 reputation)

Group: General Forum Members
Points: 132 Visits: 13
I mean the Report SQL which is running in the db. I am trying to identify the start time of the report SQL.
Grant Fritchey
Grant Fritchey
SSC Guru
SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)SSC Guru (97K reputation)

Group: General Forum Members
Points: 97255 Visits: 33013
I'm not trying to be obtuse, I just don't understand what you are referring to. Do you mean queries coming from Reporting Services?

----------------------------------------------------
The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood...
Theodore Roosevelt

The Scary DBA
Author of: SQL Server Query Performance Tuning and SQL Server Execution Plans
Product Evangelist for Red Gate Software
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search