Monitoring User Activity
Aug 2, 2001Hello,
Can anyone guide me besides any third party tool is there a way to find out all the activities of the connection till it got connected.
Thanks
Sejal
ADVERTISEMENT
Setup And Upgrade :: Activity Monitor Shuts Down If Connect To Instance Its Monitoring With Performance Monitor
Aug 19, 2015If I'm on a remote machine, meaning a computer not in the WSFC cluster, and I open SSMS 2014, point it to a SQL Instance, and open activity monitor:
1. I get all the panes and charts except % Processor Time.
2. Then, if I authenticate to the cluster's domain by mapping a drive with valid domain credentials, I'm free to put performance counters in the Perfmon - - - but SQL Activity Monitor shuts down with“The Activity Monitor is unable to execute queries against server SQL-V01INSTANCE1..Activity monitor for this instance will be placed into a paused state.Use the context menu in the overview pane to resume the activity monitor.
Additional information: Access is denied. (Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))(Mscorlib)”
3. Of course, the Activity monitor can't be resumed via the context menu. Removing counters and closing the perfmon do not work. I dropped the mapped drive and rebooted the machine. That brought back 95% of the information in the Activity monitor.
4. Further experimentation showed that any mapping of drive shares present on the SQL Server to the computer running SSMS cut off functionality of the 'overview' pane in the remote machine's SQL Activity monitor -- the monitor that had been trying to watch the server offering the shares.
User Activity
Oct 6, 2005Hello All,
I am new at this site and new at using SQL also.... I was wondering if anyone could help me out with my prob...
I have set up an SQL 2000 server and made some accounts which my friends are using to log onto my server.. Is there a way I could keep a check on who is doin what or maintain a user log....
Many Thanks,
Trace User Activity
Dec 22, 1999Hello,
Does anybody know how to trace user activity after they log off from the SQL Server 6.5? I know SQL Trace is able to trace the user activity in real time.
Many thanks in advance!
Audit User Activity
Mar 21, 2006Hi All,
I'm an Oracle DBA that has inherited some SQL Server 2000 databases.
Can you audit a particular user in Sql Server 2000. We need to know exactly what a particular user is doing, (i.e. creating/dropping objects, and what data he is accessing)
what is the best way to do this??
Thanks,
Dave
User Activity Query
Jan 7, 2007Hey!
I'm trying to produce a user activity table out of a table that contains rows on when users log in or out. However, I can't seem to get the query to 'pick' the right date time in the join.
So, I have a table like this:
ChangeIDUserIDLoggedInDateTime
7107/30/06 12:30PM
6117/30/0612:20PM
5107/29/06 11:00AM
4117/29/06 10:30AM
3205/03/06 6:30PM
2215/03/065:30PM
1205/01/06 9:30AM
0215/01/06 9:15AM
And I want to make it into this table:
UserIDLoggedInDateLoggedOutDateDuration
17/29/06 10:30AM7/29/06 11:00AM30
17/30/06 12:20PM7/30/06 12:30PM10
25/01/06 9:15AM5/01/06 9:30AM15
25/03/065:30PM5/03/06 6:30PM60
Here is what I came up with, but it doesn't seem to be working right :eek:
select
distinct (userin.changeid),
userin.userid,
userin.createdate as LoggedInDate,
userout.createdate as LoggedOutDate,
DateDiff(mm,userin.createdate,userout.createdate) as Duration
from userChange userin
inner join (
select * from userChange
where loggedin = 0
) userout on userin.userid = userout.userid
where and userin.loggedin = 1 and userin.createdate < userout.createdate
order by userin.createdate desc
SQL Server 2005 User Activity
Dec 3, 2006im trying to allocate a user appropriate permissions but i would like to see the users activities to make sure i do give that user exactly what they need how do i check user activity on sql server 2005
View 4 Replies View RelatedProcessing User Activity Table
Jul 20, 2005Hello,I have an application that will be logging to a SQL Server 2000database user user activity from several Windows 2003 terminalservers. This information will be retrieved by monitoring theSecurity logs of these servers (this part I know how to accomplishalready).A table in the database, tblLogEntries, will contain the followingfields:- ID = autoincrementing int- LogTime = Date/Time the user activity was recorded in the securitylog- Username = User's login ID that the activity was recorded with- Type = int, referencing a lookup table with the values of Logon,Logoff, and possible other future items- Server = The name of the server the activity was recorded on.The only question I have is, can you offer a way to process the totaluser login time during a given range using T-SQL.For Example...Given the table data:ID LogTime Username Type Server1 10-10-2003 8:30:00 Tom Logon SERVER-A2 10-10-2003 8:45:00 Sarah Logon SERVER-A3 10-10-2003 16:45:00 Tom Logoff SERVER-A4 10-10-2003 17:00:00 Sarah Logoff SERVER-A5 10-11-2003 8:30:00 Tom Logon SERVER-A6 10-11-2003 8:45:00 Sarah Logon SERVER-A7 10-11-2003 16:30:00 Sarah Logoff SERVER-A8 10-11-2003 17:15:00 Tom Logoff SERVER-AHow would you receive the output:User Logon Total Time for SERVER-ATom 17.0 hrsSarah 16.0 hrsI know I can handle this type of processing on my ASP.NET front-end,but I'm curious as to how easily it can be done by the database,itself.Thanks in advance for your assistance.
View 4 Replies View RelatedUser Connection Changes In Activity Monitor When Running Stored Proc
May 16, 2007Please forgive the simplicity of this question - I am not the dba type. When I connect to a server and look at my connection attributes in activity monitor, the user column shows the correct information for my domainusername. When I run a certain stored procedure in that connection, the domainusername changes to another person. We are not using execute as, setuser, or anything special to explicitly change the user. The stored procedure is in a schema that is owned by dbo (principal_id = 1 - I verified by checking sys.database_principals.)
Does anyone have any suggestions?
Thanks in advance,
John Hennesey
Recovery :: How To Find Last Login Date / Time For User DML Activity On Databases On Instance
Jun 22, 2015How to find last login date/time for user DML acitivity on databases on Instance?
Is there any way we can find our the last login date/time for databases?
Note: 1. We can find if the SQL Trace is running and store.This is not good solution
2. Audit logins off/on is also not good solution.
3. Using DMV's also not good option, if reboot sql server instance then historical values can not see.
SQL 2012 :: Monitoring Tool Using Powershell And Performance Monitoring
Sep 15, 2014We are in plan to build a Monitoring tool using PowerShell and Performance Monitor which could monitor 10 to 20 servers. Do you have any reference of any existing tool using Performance Monitor to monitor the SQL Server and available for free? I didn't want to put some effort, if something is available already.
View 2 Replies View RelatedActivity Log
Nov 9, 1999What is the best to produce user activity log ? Using triggers (insert, delete, update) is rather heavy (?) operation. Has anyone used SQL Profiler for that ? In log should be: who, what, when, workstation, table, changed fields, some data key values.
Harri
How To Log Any Activity
Dec 3, 2007Dear friends,
I'm working on an entity relationship diagram about a mice farm.
Let's say we have a very very basic diagram like this:
MOUSE "is in" CAGE
For example, this gives
Mouse "n1500" is in cage "AAA"
Mouse "n1501" is in cage "BBB"
One operator can take one mouse and put it into another cage, for
example the first line becomes
Mouse n1500 is in cage CCC
Now, I was asked to track any movement... I ask myself: do I need to
add
OPERATOR "moves" MOUSE
so that I have
Operator "John" moves Mouse "n1500" on "Monday 3rd, 2007" at "5 PM"
from "AAA" to "CCC"?
or this kind of logging is provided some way by SQL server?
Thanx a lot for any hint.
Failed To Generate A User Instance Of SQL Server Due To Failure In Retrieving The User's Local Application Data Path. Please Make Sure The User Has A Local User Profile On The Computer. The Connection Will Be Closed
Dec 7, 2006This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database)
Help pleaseee
Current Activity
Apr 17, 2000I have registered a server on to my client machine successfully,but I am not finding the current activity item in the management folder on EM.How do I see the current activity on the server.Do I need to be SA for that server?My machine has NT workstation with SQL server 7.0 on it.Any one to help?
View 1 Replies View RelatedCurrent Activity Not Available? 6.5
Aug 12, 1999When viewing Current Activity, nothing shows up.
If i run sp_who, the return is normal.
If I run sp_who2, the following message is returned. (it also is returned randomly while using function in EM such as Backup/Restore.)
Msg 268, Level 16, State 1
You cannot run SELECT INTO in this database. The DBO would have to run sp_dboption to enable this option.
What is it??? It's buggin me......
Dano
p.s. I remember a similar problem in the past, it had to do with turning truncate on checkpoint in one of the system databases msdb/master/tempdb or something like that. I turned off all Trunc. and Select/Bulk options on these...
Old && New Values For My Activity Log ??
Oct 27, 2005I have a requirment to create a activity log to log all changes that
take place in my tables as they happen. All my Inserts,updates & Deletes take place inside stored procs. A windows .NET application calls the stored procs and passes the data set to the stored procs to execute them on a Sql server 2000 database.
When an update statement is run inside my stored proc, the dataset only passes me the new values. but,i will need the old value from the database before the update takes place to report in my activity log. What is the best way to do it ?
My activity log shold report a description like this
"Name Changed from 'Robert Johnson'(old value) to 'Bob Johnson'(New Value)"
Though i can do a select from the tables to get the old values and match the new values to get the differences before running the update statements, it is too much work as i have well over 100 stored procs in my application and i have to check for every single field value in a table. If only one column changed in a table containg 20 columns, i would still have to check for 20 columns before determining which fields changed.
Can any one suggest me a better solution to report the old values and new values for all the updates that take place in a database through stored Procedures.
Thanks
Reo
Activity Monitor
Apr 10, 2008Is there a way to allow a user to run activity monitor without making that user system admin
View 9 Replies View RelatedActivity Monitor
Apr 23, 2008Hi experts,
I just want to know how can i kill all the processes of a database
if the database have more than 100 connections.This is for the purpose of restoring a database.
RKNAIR
SQL 2005 ACTIVITY
Jun 5, 2008I usually run this command in SQL 2000.
Which tells me what is running with all the sql code statements.
Is there an alternative command to run in SQL 2005...rather than using the Reports - i find it easier to execute sp i called this sp_now.
set nocount on
declare @handle binary(20),
@spid smallint,
@rowcnt smallint,
@output varchar(500)
declare ActiveSpids CURSOR FOR
select sql_handle, spid
from sysprocesses
where sql_handle <> 0x0000000000000000000000000000000000000000
--and spid <> @@SPID
order by cpu desc
OPEN ActiveSpids
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
set @rowcnt = @@CURSOR_ROWS
print '===================='
print '= CURRENT ACTIVITY ='
print '===================='
print ' '
set @output = 'ACTIVE SPIDS: ' + convert(varchar(4),@rowcnt)
print @output
WHILE (@@FETCH_STATUS = 0)
BEGIN
print ' '
print ' '
print 'O' + replicate('x',120) + 'O'
print 'O' + replicate('x',120) + 'O'
print ' '
print ' '
print ' '
select 'loginame' = left(loginame, 30),
'hostname' = left(hostname,30),
'datagbase' = left(db_name(dbid),30),
'spid' = str(spid,4,0),
'block' = str(blocked,5,0),
'phys_io' = str(physical_io,8,0),
'cpu(mm:ss)' = str((cpu/1000/60),6) + ':' + case when left((str(((cpu/1000) % 60),2)),1) = ' ' then stuff(str(((cpu/1000) % 60),2),1,1,'0') else str(((cpu/1000) % 60),2) END ,
'mem(MB)' = str((convert(float,memusage) * 8192.0 / 1024.0 / 1024.0),8,2),
'program_name' = left(program_name,50),
'command' = cmd,
'lastwaittype' = left(lastwaittype,15),
'login_time' = convert(char(19),login_time,120),
'last_batch' = convert(char(19),last_batch,120),
'status' = left(status, 10),
'nt_username' = left(nt_username,20)
from master..sysprocesses
where spid = @spid
print ' '
print ' '
-- Dump the inputbuffer to get an idea of what the spid is doing
dbcc inputbuffer(@spid)
print ' '
print ' '
-- Use the built-in function to show the exact SQL that the spid is running
select * from ::fn_get_sql(@handle)
FETCH NEXT FROM ActiveSpids
INTO @handle,
@spid
END
close ActiveSpids
deallocate ActiveSpids
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Activity Monitor
Apr 10, 2007Is there a way to give access to non-admins on ACTIVITY MONITOR?
------------------------
I think, therefore I am - Rene Descartes
Last Activity On A Table
Feb 13, 2008How to find a last activity happened on a table?
View 10 Replies View RelatedJob Activity Monitor
Sep 10, 2007Hi,
I have a problem with Job Activity Monitor.
I have a SSIS which copies data from a table to a flat file. The connection string of this file is variable and the file is reused if not exists and is created if exists.
When I run the SSIS manually from my microsoft visual studio it works properly. However, when I run this SSIS from the Job Activity Monitor, I get the following error:
Message
Executed as user: REDCAMadminsql2k5. Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 8:41:10 AM Error: 2007-09-10 08:48:04.99 Code: 0xC020200E Source: Crear Historico Historico [1] Description: Cannot open the datafile "\srvnfileHISTORICOSCAJEROSOFI3210C01OFI3210C01_2007-7X.txt". End Error Error: 2007-09-10 08:48:04.99 Code: 0xC004701A Source: Crear Historico DTS.Pipeline Description: component "Historico" (1) failed the pre-execute phase and returned error code 0xC020200E. End Error DTExec: The package execution returned DTSER_FAILURE (1). Started: 8:41:10 AM Finished: 8:48:53 AM Elapsed: 462.234 seconds. The package execution failed. The step failed.
Any idea?
Thanks.
Capurting Activity(Very Urgent)
Dec 6, 2001Hello,
A developer is running sql transactions from the application and needs to know how it is executed from database, I know I have to run Profiler Trace, but not exactly which traces , Can you please guide me?
Thanks
Malika
Cannot View Current Activity In EM
Nov 20, 2002Trying to view current activity in EM however get error 1222 Lock request time out period exceeded, this also occurs when trying to view tempdb database.
Is this a EM Gui error or locking issue and hwo do you resolve?
Thanks
GaryW
Current Activity Window
Sep 24, 1999Hi,
On sql server 6.5 running on a win/nt platform my Current Activity window comes up blank when I click on it. Everything else seems to be working fine. This is a new problem, it just stopped reporting any info when I click on it.
Any suggestions would be greatly appreciated.
Thanks,
Jerry
Lase Database Activity
Mar 19, 2002Is ther any way we can know the last database activity.I mean when was the last time the data base used/accessed
View 1 Replies View RelatedCurrent Activity Tool
Nov 3, 1998Hi there!
in the ms-enterprise manager , the current activity.. under server menu
does not display anything in one of the server in EM but in other
servers it is okay.
any inputs?
thanks.
joe
Logging Table Activity
Mar 10, 2005Hi peeps,
We have a great big database (90gb) which has been populated (monopolised) by our finance team, and its full of tables that probably aren't being used at all. But know knows whats being used and what isn't or they don't have the time to go through it with me.
So I have decided to implement a procedure that logs table activity on this database, and if for example a table isn't used for a month then it will be archived off and zipped up.
I have a few ideas in my head how I can acheive this, but I am looking for some opinions and ideas from you guys?
Thanks in advance
Activity In One DB Causes Time-outs In Another
Aug 7, 2007We have several SQL 2000 databases on one server.
One of the applications I'm responsible for has batch jobs that run for an hour; all activity is on the database. During this hour, other applications that use other databases on the same server experience time-outs. One of my coworkers did a count(*) on an empty table and it took 11 seconds.
We pay people to keep our servers up and running. Is this something they might solve by reconfiguring the server? It seems strange to me that a single database is allowed to hog all server resources.
We are meeting with them later this week, and I'd like to have some knowledge about this; we don't want to BS'ed into buying a new server.
Lots Of Transactions, But No Activity
Jan 30, 2007Hi all,
I have a strange situation. Performance monitor shows that SQLServer:Transactions Transactions value is 125, but SQL Server Profiler does not show any activity.
I ran sp_who2 and I have a bunch of processes with SUSPENDED status. Would those be counted in Performance monitor?
When does a transaction get suspended?
Thanks.
Alec
Logging Activity/connections
Mar 6, 2007Good morning all... first time poster here.
Very new to SQL server, and the database world in general. We're an old Oracle shop, and our Oracle dba's are pretty set in their ways and aren't too excited about supporting SQL server. Understandable, change is tough sometimes. Being the new kid in town, I have made it my goal to wrangle our SQL 2000/2005 environment and carve out my own little niche.
My first order of business is to do some general cleanup and documentation. So, my question is this... is there a way to determine when the last few user connection to a database were? I have a host of databases that I suspect are no longer in use, but no one can seem to identify them. Before I delete them, I definitely want to make sure they aren't being used by some off-the-beaten-path app that I am unaware of. I did a search here and a general Google search that was fruitless. I'm not the beast Googler though. :)
Any advice here will be greatly appreciated!
-Alan-
edit: My idea of a solution would be a query to show, say, the last 10 connections to a particular database. Username, date, time, etc. would be spectacular.
SQL Activity Monitor Logs
Oct 5, 2007Hi All,
I want to analyze server (SQL 2005) activity at a specific time in the past. Unfortunately, there doesn't seem to be a log file reflecting the information in Activity Monitor. Knowing that the sysprocesses and syslocks tables feed Activity Monitor, I thought about doing a log analysis on the master transaction log, but I'm not sure that's possible.
Is there a (different) way to achieve this?
Or is there a better approach to identifying processes that cause performance bottleneck/deadlocks?
Any input would be appreciated.
Ken