January 25, 2013 at 4:14 am
Hello,
Need help in finding username/login name of a particular query executed at particular time.
January 25, 2013 at 4:35 am
I have got the Query to know this but not yet particular time and only current instance
January 25, 2013 at 4:42 am
Can you please share the query?
January 25, 2013 at 4:49 am
surilds (1/25/2013)
Can you please share the query?
select s.text, qs.* from sys.sysprocesses qs
cross apply sys.dm_exec_sql_text(qs.sql_handle) s
where qs.dbid > 1 and qs.dbid <> 10 and qs.stmt_end <>0
order by qs.cpu desc
when you try to run this Query you will get the Queries running and from what server the Query is running etc...
January 25, 2013 at 4:59 am
try this
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 tat
INNER JOIN sys.dm_tran_session_transactions tst
ON tst.transaction_id = tat.transaction_id
INNER JOIN sys.dm_exec_sessions es
ON es.session_id = tst.session_id
INNER JOIN sys.dm_exec_connections ec
ON ec.session_id = es.session_id
OUTER APPLY sys.dm_exec_sql_text(ec.most_recent_sql_handle) est
ORDER BY tat.[transaction_begin_time] ASC
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2013 at 5:03 am
This is good for the current information.
It would be better if you have queries for past run queries? As I am in need of this information ASAP.
If any one has this query please share.
January 25, 2013 at 5:08 am
surilds (1/25/2013)
It would be better if you have queries for past run queries?
i dont think it would be available everytime , it solely depends on plan cache.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
January 25, 2013 at 5:38 am
Bhuvnesh------ I am getting many syntax errors while executing, is there any changes I need to make before execting?
January 25, 2013 at 5:40 am
SQL Server doesn't store the previous executions of queries. You can look at the aggregation of queries currently in cache using sys.dm_exec_query_stats, but it doesn't have execution context (who ran it, with what parameter values, etc.). In order to get this information, you need to set up a process to capture it for yourself. I suggest using extended events. You can capture rpc_complete and sql_batch_complete to see all the execution context for the queries being run against your system.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 25, 2013 at 7:20 am
Any Third party tool provides this information??
January 25, 2013 at 7:37 am
surilds (1/25/2013)
Any Third party tool provides this information??
Based on your Full requirement may be somebody could suggest , but your just asking with small info..based on that , your getting replies try to explain what are the conditions your facing and why you need it badly ..then you may get any helpful info
January 25, 2013 at 8:07 am
if you don't have a customized trace, CDC, SQL Audit, or some other set of tracking in place prior to the query being executed, there's no way to find "whodunnit" information.
even if it was an update /insert/delete, the log (assuming you have the database in FULL, and already took a regular backup so you could read the transaction log)
the log does not keep track of WHO did it, only the the event occurred.
you could set something up now, so that in the future, you could track that kind of information., but after the fact, it is not possible.
Lowell
January 25, 2013 at 8:49 am
surilds (1/25/2013)
Any Third party tool provides this information??
Are there? Sure, but what you want is pretty simple. Extended Events is a snap to set up and will do exactly what you're looking for. Why spend the money on a third party tool that may or may not do precisely what you need.
And note, I'm saying that as a vendor with a tool that I could try to sell you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply