January 11, 2011 at 4:01 am
hi guys ,
we have databases which are connected directly with sa login using Management Studio and ETL/dts tasks and client server connections with access&sql
i want to find what queries ran on the production db and their time. , who ran it from which program.
the first part is solved by
————-sql 1———-
use master
SELECT
t.TEXT QueryName,
s.execution_count AS ExecutionCount,
s.max_elapsed_time AS MaxElapsedTime,
s.creation_time AS LogCreatedOn
FROM sys.dm_exec_query_stats s
CROSS APPLY sys.dm_exec_sql_text( s.sql_handle ) t
ORDER BY s.max_elapsed_time DESC, ExecutionCount DESC
second part gives database name hostname , program name , nt username and loginname
———–sql 2———-
SELECT DATEDIFF(MINUTE, a.last_batch, GETDATE()) RunningTime,
a.spid, a.blocked, a.waittime, db_name(a.dbid) As dbname,
a.last_batch, a.hostname, a.program_name,
a.nt_username, a.loginame, b.text as sqlstatement
FROM sys.sysprocesses A CROSS APPLY sys.dm_exec_sql_text(a.sql_handle) B
ORDER BY a.last_batch DESC
I need your help to combine query 1 and 2 as i am not familiar with cross apply . please help
January 11, 2011 at 5:35 am
If you need to track all procedures that run against the server, you need a trace. The query stats DMV doesn't necessarily reflect everything that ran on the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 11, 2011 at 6:24 am
Can you help me out with the trace .
eg select * FROM sys.sysprocesses
what i need is
loginname - windows user name
hostname - machine where the user connected to run the query
programname = interface used eg access or sql server
loginname - database username
dbname - database name
table
background query
last_batch - date and time of the query
i just need the trace for dml statements expecially insert delete update and
some ddl statements like create table drop table and truncate table.
January 11, 2011 at 6:46 am
Do some searching for 'Server side trace', there have been several articles written about that. The trace can also get you host name, login name, program name, etc.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply