Activity Log

What 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

View Replies


ADVERTISEMENT

How To Log Any Activity

Dear 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 "n°1500" is in cage "AAA"
Mouse "n°1501" is in cage "BBB"

One operator can take one mouse and put it into another cage, for
example the first line becomes

Mouse n°1500 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 "n°1500" 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.

View Replies View Related

SQL Activity

I have just set up the following

http://support.microsoft.com/kb/283696/EN-US/

and http://support.microsoft.com/kb/283886/EN-US/ for the performance

So i run my results and get

select * from v_sysperfinfo

and get ...
Workfiles Created/sec                    Oct 16 2006 11:54AM 632531
Index Searches/sec                       Oct 16 2006 11:49AM 106329443


Values seem high....for the COUNTER
Is there something that needs to be reset when using this tool

 

View Replies View Related

Old && New Values For My Activity Log ??

I 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

View Replies View Related

Current Activity Not Available? 6.5

When 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...

View Replies View Related

Current Activity

I 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 Replies View Related

Current Activity

If I get pageiolatch_sh wait type periodically, what does it mean ? Is
tuning required ?

Thanks,
Jim

View Replies View Related

User Activity

Hello 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,

View Replies View Related

SQL 2005 ACTIVITY

I 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

View Replies View Related

Activity Monitor

Hi 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

View Replies View Related

Activity Monitor

Is there a way to allow a user to run activity monitor without making that user system admin

View Replies View Related

Last Activity On A Table

How to find a last activity happened on a table?

View Replies View Related

Activity Monitor

Is there a way to give access to non-admins on ACTIVITY MONITOR?


------------------------
I think, therefore I am - Rene Descartes

View Replies View Related

Activity Monitor

We have a problem with our website. It uses SQL express.

After restarting the website etc., SQL Express starts out ok, but after a while it seems to react slower and slower.

SQL server takes a lot of the cpu resources it jumps up and down to 100%.

It might be some stored procedure behaving bad....

How can I find out which one is causing the problem.
I've checked the activity monitor, and see some processes with high cpu loads, but how can I find out what this process is....

View Replies View Related

Job Activity Monitor

Hi,
 
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.
 
 

View Replies View Related

Logging Table Activity

Hi 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

View Replies View Related

Audit User Activity

Hi 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

View Replies View Related

User Activity Query

Hey!

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

View Replies View Related

Activity In One DB Causes Time-outs In Another

We 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.

View Replies View Related

Current Activity Window

View Replies View Related

The Current Activity Window

Is it possible to open the current activity window from EM
for NON SA users? Any tricks about it?

View Replies View Related

Current Activity Tool

Hi 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

View Replies View Related

Current Activity Window

Hi,

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

View Replies View Related

Current Activity Blank?

THere is not current activity showing in EM.

sp_who works fine,
sp_who2 returns.

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.


Something has gone wrong. Any ideas?
SELECT INTO can't be set in Master. What database does Current activity information come out of, what system tables.

Thanks for your assistance.

Dano

View Replies View Related

Trace User Activity

Hello,

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!

View Replies View Related

Lase Database Activity

Is ther any way we can know the last database activity.I mean when was the last time the data base used/accessed

View Replies View Related

Cannot View Current Activity In EM

Trying 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

View Replies View Related

Can&#39;t Open Current Activity

On one of my SQL 7 servers, I am suddenly unable to open current activity. I get the error message 1222 "Lock request time out period exceeded". Does anyone know what caused this and how I correct it. Monday I was able to open current activity, Tuesday morning I started getting this message. Nothing was changed between Monday and Tuesday.

Thanks so much for any help on this,
Terry

View Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved