Yes and no. There is a lock in the DB but the app is still alive. And even then I believe it is only one thread and/or table that is locked.
We are able to capture he offending SQL but it doesn't translate back to the application layer. Jason On Tue, Sep 18, 2012 at 12:59 PM, Joe Martin D'Souza <jdso...@shyle.net>wrote: > ** > > Which kind of makes sense.. You can’t get a heart beat of a dead man.. But > you might get the last thing that was run before the system died? > > Alternately I think if there was a process completely outside the AR > System that monitored the DB User, it could monitor these things, you would > have it running even after a server restart. Maybe one of the BMC > monitoring tools may be an answer to this? > > Joe > > *From:* Jason Miller <jason.mil...@gmail.com> > *Sent:* Tuesday, September 18, 2012 3:40 PM > *Newsgroups:* public.remedy.arsystem.general > *To:* arslist@ARSLIST.ORG > *Subject:* Re: Querys that kill my system > > ** We have Large Result Logging turned on. While it does show us some > people who could use some education it has never given us information > (form/user) regarding a query that hangs the system. For some reason these > hanging queries do not seem to be logged by this feature. > > We use the below MS SQL query to find the offending process and query but > since it is at the SQL level it doesn't tell us who issued the query. I > would love to be able to correlated this with the thread log. These hangs > happens so rarely now it doesn't warrant keeping SQL logging on at all > times. > > --------------------------------------------------------------------- > DROP TABLE #Processes > > -- Field ID 240000007 = Description and Field ID 240000008 = Worklog > SELECT > s.spid, BlockingSPID = s.blocked, DatabaseName = > DB_NAME(s.dbid), > s.program_name, s.loginame, ObjectName = > OBJECT_NAME(objectid, s.dbid), Definition = CAST(text AS VARCHAR(MAX)) > INTO #Processes > FROM sys.sysprocesses s > CROSS APPLY sys.dm_exec_sql_text (sql_handle) > WHERE > s.spid > 50; > > WITH Blocking(SPID, BlockingSPID, BlockingStatement, RowNo, LevelRow) > AS > ( > SELECT > s.SPID, s.BlockingSPID, s.Definition, > ROW_NUMBER() OVER(ORDER BY s.SPID), > 0 AS LevelRow > FROM > #Processes s > JOIN #Processes s1 ON s.SPID = s1.BlockingSPID > WHERE > s.BlockingSPID = 0 > UNION ALL > SELECT > r.SPID, r.BlockingSPID, r.Definition, > d.RowNo, > d.LevelRow + 1 > FROM > #Processes r > JOIN Blocking d ON r.BlockingSPID = d.SPID > WHERE > r.BlockingSPID > 0 > ) > SELECT * FROM Blocking > ORDER BY RowNo, LevelRow > > -- DROP TABLE #Processes > --------------------------------------------------------------------- > > Jason > > On Tue, Sep 18, 2012 at 12:22 PM, ravi rai <ravira...@hotmail.com> wrote: > >> ** >> Joe, >> This feature is available >> Add following in Ar.cfg >> >> *Large-Result-Logging-Threshold: 70000 >> * >> 70000 is the threshold limit >> >> enable thread log >> >> >> result will be like >> <THRD> <1 > /* Mon Jul 09 2012 16:05:38.2310 */ Thread Id 39696 >> (GLEWF) large result buffer allocation - /Length: *840183*/Entries: >> 3000/Client Ver: >=10/RPC ID: 11393/User: <UserName>/Form: <Form name>/ >> >> Ravi >> >> ------------------------------ >> Date: Tue, 18 Sep 2012 14:05:57 -0400 >> From: jdso...@shyle.net >> >> Subject: Re: Querys that kill my system >> To: arslist@ARSLIST.ORG >> >> >> ** >> >> So this feature really isn't there? You nearly had me there! >> >> It’s a great feature if it was there though.. Maybe a perfect candidate >> for some sort of an enhancement request.. >> >> Joe >> >> *From:* John Sundberg <john.sundb...@kineticdata.com> >> *Sent:* Monday, September 17, 2012 10:04 PM >> *Newsgroups:* public.remedy.arsystem.general >> *To:* arslist@ARSLIST.ORG >> *Subject:* Re: Querys that kill my system >> >> ** It is a little hard to say what "version" it was.... >> >> Because in 2012 -- BMC renamed it all to BMC Atrium ... threw in the >> kitchen sink -- which included the Remedy product and the ITSM Suite + some >> discovery software + a CMDB... >> >> But in 2014 Oracle broke it all up -- and peeled off the ITSM software >> into something they called "Oracle Service Support" .... (And the concept >> of a development admin tool went away) -- it is purely configuration and >> consulting to configure the product... >> (Traditional Remedy customer are unhappy - new customers don't know >> better - think they are eating a juicy steak - turns out it is pink slime) >> >> RemedyForce was taken over by Business.com (aka Salesforce.com). >> >> RemedyOnDemand - still exists -- but is part of "Oracle Scalable >> Enterprise" - which everybody calls "Oh say ... can you see" -- because >> nobody can actually see it working... kind of a weird industry joke... >> (Still not sure about it -- I think 2021 humor goes over my head) >> >> Same timeframe -- Oracle renamed Remedy to "Oracle Enable" ... and it >> became really the "adhoc" business "back-office" apps generator.... >> >> SRM was abandoned by Oracle -- really BMC killed it before Oracle got it >> (circa 2011) - BMC just never told the customers. >> >> Of course Kinetic Data stayed independent and front-ends both Oracle >> Service Support and the Enable framework - keeping customers happy - >> regardless of the shenanigans behind the scenes... >> >> BTW - the MN Vikings still are without a Superbowl in 2021 :( >> >> Obama gets reelected. >> Republican gets elected 2016 -- does a fair job - but chooses not to >> rerun. >> Hillary gets 2020. >> >> And that is all I know... >> >> -John >> >> On Mon, Sep 17, 2012 at 8:15 PM, Shellman, David <dave.shell...@te.com>wrote: >> >> ** >> John, >> >> Do you know what version this introduced? >> >> Dave >> >> On Sep 17, 2012, at 8:43 PM, "John Sundberg" < >> john.sundb...@kineticdata.com> wrote: >> >> ** No - they won't work. :( >> >> Sorry... >> >> -John >> >> >> On Mon, Sep 17, 2012 at 6:05 PM, Chris Kelly <ctopke...@gmail.com> wrote: >> >> ** These are great suggestions But I an in 2012 and I need a solution >> today.... >> As you can tell I know nothing about remedy. So will adding the two >> lines work? >> Any help is great, my future self says thanks also.... >> Chris >> On Sep 17, 2012 4:26 PM, "Joe Martin D'Souza" <jdso...@shyle.net> wrote: >> >> ** >> >> Sweet... don’t think I knew this.. >> >> New feature?? >> >> Joe >> >> *From:* John Sundberg <john.sundb...@kineticdata.com> >> *Sent:* Monday, September 17, 2012 4:56 PM >> *Newsgroups:* public.remedy.arsystem.general >> *To:* arslist@ARSLIST.ORG >> *Subject:* Re: Querys that kill my system >> >> ** >> >> You could go into ar.cfg >> >> add the 2 lines : >> >> ars_track_long_queries: true >> ars_track_long_queries_min_length: 5000 # This is milliseconds - so all >> greater than 5 seconds >> >> >> Then -- restart server >> >> Look for table called: "ARS_TRACK_QUERIES" (I think that is the name)… >> >> Look in there -- it will tell >> who >> when >> what >> how long it ran >> how many entries returned >> what ip address >> what client type and version >> >> >> Pretty sweet feature… (wish I would have thought of it originally) >> >> >> -John >> >> >> >> >> On Mon, Sep 17, 2012 at 10:21 AM, Chris Kelly <ctopke...@gmail.com>wrote: >> >> ** >> Thanks so very much. I am not a Remedy Admin so My admin says the >> Unqualified searches is disabled. >> >> I will work on getting the fields indexed asap. >> >> The thing is we don't know who is running the queries... is there a way >> on the remedy system that we could look at to see who is running this and >> stop it when it is going on? >> >> Chris >> >> On Mon, Sep 17, 2012 at 9:09 AM, Maddala, Venkat >> <venkat.madd...@ncr.com>wrote: >> >> ** >> >> Hi Chris,**** >> >> Here are the few things that you can do **** >> **1) **Disable Unqualified searches on server**** >> **2) **Index the most commonly used fields in search**** >> **3) **Limit the free form searches by adding custom workflow**** >> >> **** >> >> BTW is this for custom applications? Or OOTB?**** >> >> **** >> >> HTH**** >> >> Venkat Maddala **** >> >> http://RemediesForRemedy.com <http://remediesforremedy.com/>**** >> >> **** >> >> **** >> >> **** >> >> *From:* Action Request System discussion list(ARSList) [mailto: >> arslist@ARSLIST.ORG] *On Behalf Of *Chris Kelly >> *Sent:* Monday, September 17, 2012 10:51 AM >> *To:* arslist@ARSLIST.ORG >> *Subject:* Querys that kill my system**** >> >> **** >> >> ** **** >> >> Hello all.**** >> >> **** >> >> I have a question for the group.**** >> >> **** >> >> Our ARsystem 7.1 and what is going on is when someone runs a intensive >> query my system comes to a standstill. what are a few things i would do >> to stop this? or set it so if queries get approved before running????**** >> >> **** >> >> **** >> >> Please help ...**** >> >> **** >> >> Chris **** >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ **** >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> >> >> >> -- >> >> *John Sundberg* >> Kinetic Data, Inc. >> "Your Business. Your Process." >> *WWRUG10 Best Customer Service/Support Award* >> *WWRUG09 Innovator of the Year Award* >> * >> * >> 651-556-0930 I john.sundb...@kineticdata.com >> www.kineticdata.com I community.kineticdata.com >> >> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> >> >> >> -- >> >> *John Sundberg* >> Kinetic Data, Inc. >> "Your Business. Your Process." >> *WWRUG10 Best Customer Service/Support Award* >> *WWRUG09 Innovator of the Year Award* >> * >> * >> 651-556-0930 I john.sundb...@kineticdata.com >> www.kineticdata.com I community.kineticdata.com >> >> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> >> >> >> >> -- >> >> *John Sundberg* >> Kinetic Data, Inc. >> "Your Business. Your Process." >> *WWRUG10 Best Customer Service/Support Award* >> *WWRUG09 Innovator of the Year Award* >> * >> * >> 651-556-0930 I john.sundb...@kineticdata.com >> www.kineticdata.com I community.kineticdata.com >> >> >> >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ >> > > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _attend WWRUG12 www.wwrug.com ARSlist: "Where the Answers Are"_ > _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org attend wwrug12 www.wwrug12.com ARSList: "Where the Answers Are"
