March 25, 2009 at 12:17 am
Dear all,
We are using SQL Server 2005 for production and the database compatability level is kept for 80 not 90. I have a qery for
finding the long running queries which supports on SQL 2005 not 2000 database. Its throwing error when i run the query
to identify the long running queries.
Query :
select top 50
qs.total_worker_time / execution_count as avg_worker_time,
substring(st.text, (qs.statement_start_offset/2)+1,
((case qs.statement_end_offset
when -1 then datalength(st.text)
else qs.statement_end_offset
end - qs.statement_start_offset)/2) + 1) as statement_text,
*
from
sys.dm_exec_query_stats as qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) as st
order by
avg_worker_time desc
ERROR :
Server: Msg 170, Level 15, State 1, Line 11
Line 11: Incorrect syntax near 'apply'.
Can anyone please have a look into this to work fine for 2000 compatibility databases.
Or else anyone please provide me the query for identifying the long running queries.
Thanks,
CH&HU.
March 25, 2009 at 12:45 am
Try ...
SELECT TOP 50 qs.total_worker_time / execution_count as avg_worker_time,
(SELECT SUBSTRING(st.text,
(qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset WHEN -1 THEN
datalength(st.text)
ELSE
qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1)
FROM sys.dm_exec_sql_text(qs.sql_handle) AS ST) AS statement_text,
*
FROM sys.dm_exec_query_stats AS qs
ORDER BY avg_worker_time DESC
Thanks.
EDIT: Forgot to format the query so it was hard to read ... Sorry.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 1:44 am
--------------------------------------------------
Error : Server: Msg 170, Level 15, State 1, Line 17
Line 17: Incorrect syntax near '.'.
Thanks,
CH&HU
March 25, 2009 at 6:29 am
If the database is running in 80 compatibility mode, you won't be able to run 90 type queries, which include using the CROSS APPLY query.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
March 25, 2009 at 10:17 am
Urg sorry; that wouldn't work because the function returns a table which was not supported in SQL 2000.
You can run that query in a database that is 90 and it will return the SQL Statements to you. Please note this is instance wide and not database based.
When I run it on masters database I don't just get master database statements I get statements from all server statements.
Thanks.
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 25, 2009 at 10:26 am
dbcc opentran will tell for oldest open transaction.
look for last_batch and status
then runn dbcc inputbuffer(spid)
to see whats it running.
:crazy: :alien:
Umar Iqbal
August 30, 2011 at 11:58 pm
If your problem is a database with compatibility level 80 on an SQL Server 2005, just run the query from some other database on that server which has compatibility level 90, e.g. tempdb.
May 4, 2012 at 7:32 am
I get the following error:
Msg 170, Level 15, State 1, Line 9
Line 9: Incorrect syntax near '.'.
Have been looking at it a while, but not sure how to fix.
Please advise.
Thank you.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply