Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Is There A Way To List Current Activity, Such As Primary Key? Expand / Collapse
Author
Message
Posted Wednesday, February 3, 2010 1:12 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
There's a bug in our ERP application and until the vendor fixes the code I need to execute some SQL statements and run a utility to fix the data. I periodically throughout the day check and fix.

I have written a couple SQL statements which let me know the jobs (job_number) to fix. However, I don't want to 'fix a job' that a user is still active in - they may be editing various parts of a job and therefore be actively making changes and when they finish the data will need fixing. No sense fixing it now and it being corrupted the next moment.

I've been looking around Enterprise Manager and can't see anything. The closest I get is looking in the properties of the Lock / Processes. Not time efficient.

What I visualize (wish list) is to see what each user is currently accessing by listing the user's ID, Table, Primary Key of the record(s). I can tell by looking at this information what I need to know.

For example: If I issued a command in Query Analyzer "SELECT ... FROM orders where customerid = 'xyz';" the listing would show all the orders that were returned:
myloginid | ORDERS | 12345
myloginid | ORDERS | 21233
myloginid | ORDERS | 34322
someone else | INVENTORY | 1278312

(Hope this makes sense).


"The Road To Success Is Always Under Construction."
Post #858985
Posted Wednesday, February 3, 2010 2:18 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 5,565, Visits: 24,699
Just a wild idea, which I hope will stimulate your creative juices.

Have you considered running sp_who, at least it will tell if some one is using the database, if no one, can you then do your fix up task with an exclusive lock on the table in question?

My wild idea, schedule a job to run sp_who say every 30 minutes and insert the results into a temp table, query the temp table, if the database name is not found, then do the fixing up.


If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #859044
Posted Thursday, February 4, 2010 8:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, April 14, 2013 12:31 PM
Points: 112, Visits: 394
Well, I now know about sp_who.

The database is our ERP database - "everyone's always in it".
I ran sp_who and there are 87 people in the database. All but one is "sleeping" / "AWAITING COMMAND".

If everyone's status is sleeping I could do my updates.

Nice suggestion, it's a start - "Brute force" is how one of my college professors would describe it.


"The Road To Success Is Always Under Construction."
Post #859715
Posted Thursday, February 4, 2010 11:24 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:36 PM
Points: 5,565, Visits: 24,699
Brute force nah with finesse / elegance.

This has got me intrigued ... been a long time since working with 2000, but here may be something to stir your creative juices even more.. if memory serves me correcting a Spid less than 50 is a system spid not a user spid. That said run the followng as an insert into a temp table.

SELECT spid,Db_Name(dbid) FROM Master..sysprocesses WHERE Spid > 49 AND DB_Name(dbid) 
LIKE '%Northwind%'

Then using the entries in the temp table run the following command
into another temp table.
DBCC INPUTBUFFER (spid value)

Then a select on the 2nd temp table with a LIKE clause for your table name.

Also look at fn_get_sql available from a 2000 update, and you should have same if not seach TECHNET for the hot fix.

fn_get_sql uses the sql_handle from sysprocess to return the SQL statement.. negating the need to use the DBCC command.

Being optimistic, if this all works out how about posting the code on the forum so others may learn ...




If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #859827
Posted Thursday, February 4, 2010 11:39 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 1:19 PM
Points: 3,101, Visits: 7,807

Running "SP_WHO2 Active" may be more appropriate in this case since it only shows the active spids.




Alvin Ramard
Memphis PASS Chapter

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.
Post #859832
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse