InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:55:34 GMT20http://www.sqlservercentral.com/Forums/Topic1409381-391-1.aspxHi Team,Thanks a lot. The above script is working fine... Thanks a lot..Mon, 21 Jan 2013 01:48:27 GMTrajkiran.panchagirihttp://www.sqlservercentral.com/Forums/Topic1409381-391-1.aspx[quote][b]rajkiran.panchagiri (1/21/2013)[/b][hr]I need from which sql login that particular statement is running.[/quote]TRy this [code="sql"]SELECT‘SESSION_ID: ‘ + CAST(es.[session_id] AS VARCHAR(4)) + ‘ ‘ +‘ HOST_NAME: ‘ + es.[host_name] + ‘ ‘ +‘ PROGRAM_NAME: ‘ + es.[program_name], ‘ ‘ + CHAR(13) + CHAR(10),‘ LOGIN_NAME: ‘ + es.[login_name] + ‘ ‘ +‘ PROCESS_OWNER: ‘ + CASE es.[is_user_process]WHEN 1 THEN ‘User’ELSE ‘System’ END, ‘ ‘ + CHAR(13) + CHAR(10),‘ TRANSACTION_START_TIME: ‘ + CAST(tat.[transaction_begin_time] AS VARCHAR) + ‘ ‘ +‘ LAST_READ_TIME: ‘ + CAST(ec.[last_read] AS VARCHAR) + ‘ ‘ +‘ LAST_WRITE_TIME: ‘ + CAST(ec.[last_write] AS VARCHAR) + ‘ ‘ +‘ SESSION_STATUS: ‘ + es.[status], ‘ ‘ + CHAR(13) + CHAR(10),‘ TRANSACTION_STATE: ‘ + CASE tat.[transaction_state]WHEN 0 THEN ‘The transaction has not been completely initialized yet.’WHEN 1 THEN ‘The transaction has been initialized but has not started.’WHEN 2 THEN ‘The transaction is active.’WHEN 3 THEN ‘The transaction has ended. This is used for read-only transactions.’WHEN 4 THEN ‘The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.’WHEN 5 THEN ‘The transaction is in a prepared state and waiting resolution.’WHEN 6 THEN ‘The transaction has been committed.’WHEN 7 THEN ‘The transaction is being rolled back.’WHEN 8 THEN ‘The transaction has been rolled back.’ END + ‘ ‘ +‘ TRANSACTION_TYPE: ‘ + CASE CAST(tat.[transaction_type] AS VARCHAR)WHEN ’1′ THEN ‘Read/Write’WHEN ’2′ THEN ‘Read-only’WHEN ’3′ THEN ‘System’ END , ‘ ‘ + CHAR(13) + CHAR(10),‘ SQL_TEXT: ‘ + est.text, ‘ ‘ + CHAR(13) + CHAR(10) + ‘ ‘ + CHAR(13) + CHAR(10)FROM sys.dm_tran_active_transactions tatINNER JOIN sys.dm_tran_session_transactions tstON tst.transaction_id = tat.transaction_idINNER JOIN sys.dm_exec_sessions esON es.session_id = tst.session_idINNER JOIN sys.dm_exec_connections ecON ec.session_id = es.session_idOUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) estORDER BY tat.[transaction_begin_time] ASC[/code]here you need to replace inverted comma in proper way and see attachment too . Mon, 21 Jan 2013 01:28:29 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409381-391-1.aspxHI Team,From the above query am getting the queries hitting in that DB. I need from which sql login that particular statement is running.Thanks in advance.Mon, 21 Jan 2013 01:05:27 GMTrajkiran.panchagirihttp://www.sqlservercentral.com/Forums/Topic1409381-391-1.aspx[code="sql"]-- Execute the query inside target database SELECT TOP 10 qs.total_elapsed_time / qs.execution_count / 1000000.0 AS average_seconds, qs.total_elapsed_time / 1000000.0 AS total_seconds, qs.execution_count, SUBSTRING (qt.text,qs.statement_start_offset/2, (CASE WHEN qs.statement_end_offset = -1 THEN LEN(CONVERT(NVARCHAR(MAX), qt.text)) * 2 ELSE qs.statement_end_offset END - qs.statement_start_offset)/2) AS individual_query, o.name AS object_name, DB_NAME(qt.dbid) AS database_nameFROM sys.dm_exec_query_stats qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as qt LEFT OUTER JOIN sys.objects o ON qt.objectid = o.object_idWHERE qt.dbid = DB_ID()ORDER BY average_seconds DESC;[/code]AND[code="sql"]SELECT DISTINCT TOP 10t.TEXT QueryName,s.execution_count AS ExecutionCount,s.max_elapsed_time AS MaxElapsedTime,ISNULL(s.total_elapsed_time / 1000 / NULLIF(s.execution_count, 0), 0) AS AvgElapsedTime,s.creation_time AS LogCreatedOn,ISNULL(s.execution_count / 1000 / NULLIF(DATEDIFF(s, s.creation_time, GETDATE()), 0), 0) AS FrequencyPerSecFROM sys.dm_exec_query_stats sCROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) tORDER BY s.max_elapsed_time DESC, ExecutionCount DESCGO[/code]Mon, 21 Jan 2013 00:56:30 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic1409381-391-1.aspxHi Friends,Is there a query to get long running queries from dynamic views in sql server 2008 R2. The O/P should contain the following:Login name, Statement executed, CPU, READS, WritesThanks in advanceMon, 21 Jan 2013 00:52:00 GMTrajkiran.panchagiri