• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 795
  • Last Modified:

Goldmine 5.7 locking in SQL server

Users getting hourglassed and activity monitor shows blocking.

This was opened as another thread a month or so ago,
but that was closed when the problem sort of went away with not conclusive cause found.

http://www.experts-exchange.com/Database/Contact_Management/Goldmine/Q_23736421.html

Now after a month of so of not being a problem,  it's back.

"In activity moniter, everytime there is a blocking situation, one of the threads involved with the blocking have
this query ( recid's vary)  
UPDATE dbo.CAL SET COMPANY='20080919 13:06 20080919 12:57 20080922 09:24 20080922 10:36' WHERE RECID='7BEPPX1&2*>(JI9' AND COMPANY='20080919 13:06 20080919 12:57 20080922 09:24 20080922 10:35'
I have rebuild indexes for this table.
Both of these steps were taken previously:
Alter Database  OurDatabaseName  Set READ_COMMITTED_SNAPSHOT on
"SQL 2005 in compatibility mode for 2000 it should be ok. "

I'm thinking another clue may be found by setting up individual SQL logins to perhaps isolate which user(s) are involved. Then investigate their activity, or machine? But that's a long shot as I'm pretty sure their machines are all set up the same way.

Anyone else seen this before?
0
awalkinthepark
Asked:
awalkinthepark
  • 5
  • 4
1 Solution
 
stevengraffCommented:
The only thing in that update query that is not total gibberish is

WHERE RECID='7BEPPX1&2*>(JI9'

I suggest you try to find a record in your cal table with that recid and see if it provides any additional insight.

select * from cal WHERE RECID='7BEPPX1&2*>(JI9'

It looks so odd I'm tempted to call it something unkind, like "rogue query." But then I wonder if the query is really coming from GoldMine or from some other application, maybe a 3rd party product that wants to store it's licensing or usage or timeout data in an obscure place in GoldMine.

You've been able to "trap" that query as being something that hangs GoldMine. Question: Can you run a trace and see if you ever get queries like that when GoldMine is not hung?

Also you're idea: "I'm thinking another clue may be found by setting up individual SQL logins to perhaps isolate which user(s) are involved. Then investigate their activity, or machine?" is probably a good idea... it may add some additional insight if, in fact, the query does always come from the same machine or user.
0
 
awalkintheparkAuthor Commented:
Nice idea about the rogue query from another application, but I doubt it.
I'll try to trap that query when it's not hung.

This record:
select * from cal WHERE RECID='7BEPPX1&2*>(JI9'
has no userid, ondate, ontime or enddate
There are 31 records in the CAL table with
 ridiculous content in the Company field
COMPANY='20080919 13:06 20080919 12:57 20080922 09:24 20080922 10:36'
I would like to delete these using query analyser.
Any reason not too?

0
 
awalkintheparkAuthor Commented:
the 31 records have comparable data to:
"20080919 13:06 20080919 12:57 20080922 09:24 20080922 10:36"
they are not all exactly this.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
stevengraffCommented:
I would delete them with the general caveat relative to deleting stuff which we don't know what it is. I have friends who decide to "clean out" their windows directory because they think it's bloated. Next thing you know, nothing works.

Having said that, I have no idea what those records are doing in your database, nor why any program should be sending queries in order to update it.

OK, never mind the above. I did a query on my GoldMine: select * from cal where company like '%:%' and boy did I get an earful.

The records in question all have accountnos beginning with PB and then a user's name. I think this is where GoldMine stores the user login data that you would see if you went into File | Config | Users Settings | Properties | Time Clock.

This means that the records are, basically, legitimate. Why your system hangs when GoldMine tries to update these records is still a mystery. Could well be related to the SQL version though. Your version of GoldMine was never tested with SQL 2005, only SQL 2000 (and possibly SQL 7).
0
 
stevengraffCommented:
Correction... PB = PegBoard. If you view the calendar and select the Pegboard tab, the information you see there comes from these records.
0
 
awalkintheparkAuthor Commented:
The processID#1, a system process, command "Resource monitor"  in SQL server activity monitor  is the other side of the blocking.
0
 
awalkintheparkAuthor Commented:
And it all seems to have gone away after  rebuilding indexes, reorganizing, checking integrity.
So, try that if you are in the same boat.
0
 
stevengraffCommented:
Just curious -- hadn't you already tried that before... without (lasting) success?
0
 
awalkintheparkAuthor Commented:
I think the first time this happened, I rebuilt the indexes, only.
So, I did all the maintenance steps reorganize, check integrity, indexes.
Don't know what to say, except that the problem is gone  for now.  Remains to be seen if it returns.
But there was a definite correlation between the maintenance routines completing and the problem going away. I do appreciate your help and time.
Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now