SQL Server Performance Forum – Threads Archive

Automatically kill a process

Env.: SQL2000, Win2000
App.: Access2000 I had a situation some days ago in the morning that all clients’ applications were waiting for a locked process. It seemed a client had turned off his computer without exiting his application last night and therefor the resource left locked. 1. Is my conclusion correct?
2. Do you recommend that I kill a process which is left for a specific time? If yes, is it possible to automate this?

---

I recommend you turn his linked table into a pass-through query so he doesn’t lock your system up everytime he opens his application. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

The application needs to be revied for this. The application should never hold locks on the data and wait for user’s input. There are many recommendations for application design on this site… Killing a process is not a good practice. It may happen some day that the maintainence job has been running for 10 hours and you kill the same. It’ll take almost the same time to roll it back. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

---

Gaurav – I inherited the application and the client doesn’t approve mager changes. I am wondering if I can do something in SQL side. Thank you for the comment about killing processes. I’ll keep it in my mind. Derrick – I like your recommendation. Would you please give me some hints about how I can implement it. Thanks

---

There is an automate process which imports data from text files into some tables. This happens at 3:00AM everyday. At that time there shouldn’t anybody working with the DB. I want to detect users who had left their programs logged on and locked my DB. Then kill those process and let my import works. If I detect and find out who is doing the process, then how can I kill the process. I don’t want to use EM or AQ. I want the application calls an SP and that SP finds the locking process and kills it. Farhad R
[email protected]

---

Here is a handy sp that kill ALL procesess on the the DB. Note use this SP with extreme caution, becuase like the other forum members mentioned if there are jobs running this kill the spids as well. Only use this sp if you are absolutely sure that you want to kill all spids. Here it is. Also like Derrick mentioned I would revise the app not to use link tables as they are resource intensive. Good luck! CREATE PROCEDURE sp_ku @dbname varchar(50) AS
SET NOCOUNT ON DECLARE @strSQL varchar(255)
PRINT ‘Killing Users’
PRINT ‘—————–‘ CREATE table #tmpUsers(
spid int,
eid int,
status varchar(30),
loginname varchar(50),
hostname varchar(50),
blk int,
dbname varchar(50),
cmd varchar(30)) INSERT INTO #tmpUsers EXEC SP_WHO
DECLARE LoginCursor CURSOR
READ_ONLY
FOR SELECT spid, dbname FROM #tmpUsers WHERE dbname = @dbname DECLARE @spid varchar(10)
DECLARE @dbname2 varchar(40)
OPEN LoginCursor FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ‘Killing ‘ + @spid
SET @strSQL = ‘KILL ‘ + @spid
EXEC (@strSQL)
END
FETCH NEXT FROM LoginCursor INTO @spid, @dbname2
END CLOSE LoginCursor
DEALLOCATE LoginCursor DROP table #tmpUsers
go

---

Thank you Lazy_DBA, I am going to read your sp. But as I asked Derrick (and he haven’t answered yet), how can I use query instead of linked table? The app is Access and the development team is using it. Do you mean I use view for each table? I couldn’t get Derrick’s ‘turn his linked table into a pass-through query’ Farhad R
[email protected]

---

I suggest you look into linked tables and Pass-Through query in MS Access help files or go out and grab a book on Access with SQL Server I dont know the exact name but there is a book specific to Access as a front end to SQL Server also Access unleashed is pretty good. It’s been ages since I programmed with Access, but I dont think this will be an over night mission for you, it will require change in your application code.

---

I was at a Microsoft Security Conference yesterday. Sorry about that. To create a pass-through query, you just go into the Query pane and design a new query. You will select Pass-through as your type on the top menu. You can then put in EXEC stored_procedure and it will execute the stored procedure. You then right-click on the title bar to set your connection string. Basically, this will just execute a stored procedure in SQL Server, which you can have pull the same recordset the linked table pulls. You can set that as your recordset in the form. If the form needs to be updateable, you will have more work as you need to create insert and updates statements. If you want to see examples of this, there are a lot of good books such as Access Bible. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

---

Thank you Derrick. I didn’t know about pass-Through query. I thought it’s a technique. "Lazy_DBA"’s reply taught me it is an Access feature and I read Access help. Your reply confirmed what I learnt yesterday. So, I have to do (or ask our developers do) some changes in the application, which I think the client would not accept. I am asking those who have already implemented both "Linked tables" and "Pass-Trough query" methods, do you think there should be too many changes to convert from the first one to the second one? For example, there is a link to tblAccount and there are some SELECT, DELETE and UPDATEs in the application. So, I have to create a query for each of these. If application has two SELECTs with different columns, I have to create two pass-trough query to support them and in the application call the appropriate sp. Am I right? Sorry, if it seems silly but I want to confirm my understanding then prepare a report estimating the size of changes in the application. Thank you and appreciate your patients
Farhad R
[email protected]

---

Since I it has been a while since I have used ACCESS I got a little mixed up. I do remember that they gave a better performance boost than using direct connection with DAO. Linked tables do not pull all the SQL Server Data into the local Jet Engine (Access) the data remains in the SQL Server tables. Also Link tables store the database schema information locally in the linked tables reducing startup delays. Im sure you wont be using DAO since it is primitive now and has been replaced with ADO which is much faster. I recommend you do more research on pass though queries and SQL Server before you make any recomendations to your developers. If the locking were the only performance hits, you can circumvent the problem with other solutions. Let me look in my archives to see what I can dig up. Check out this site from http://www.candace-tripp.com/_pages/index_fl.asp it even looks like she gave her website a new look since I last visited. Good luck!

---

I think He is a she…. Mensa member! What IQ does that have to be? Brett

---

Oops, so your saying that it’s a He She, is she your mother or something? Menso member! What IQ does that have to be? 8-( By the way mistakes can be corrected.

---

Seems the site has good information for me. I am going to check it. As far as I know, the application was built with DAO but when they converted it to use SQL-Server, they changed some part of the application to ADO and in addition to the luck problem, we are having experience of ODBC error, which I may write it under a new title. Thanks Farhad R
[email protected]

---

What type of odbc errors?

---

Lazy_DBA, The error code is [Microsoft][ODBC SQL Server Driver]Timeout expired (#0) 3146 This happens (only sometimes) when the following code is running: Private Sub UpdateMainAndComments()
‘updates table main and comments with ABC table information
Dim strSQL As String
Dim ABRS As Recordset strSQL = "Select BNumber from qryactivebranch order by BNumber"
Set ABRS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges) If ABRS.EOF And ABRS.BOF Then
ABRS.Close
Exit Sub
End If ABRS.MoveFirst
Debug.Print "Start: " & Now
Do While Not ABRS.EOF
strSQL = "UPDATE (ABC INNER JOIN tblMain ON (ABC.[Account #] = tblMain.Contract) " & _
"AND (ABC.[4Dig] = tblMain.Emp) AND (ABC.FSO = tblMain.Branch)) " & _
"INNER JOIN tblComments ON (tblMain.Contract = tblComments.Contract) " & _
"AND (tblMain.Emp = tblComments.Emp) " & _
"AND (tblMain.Branch = tblComments.Branch) " & _
"SET tblMain.[ABC Account] = True, " & _
"tblMain.[SPB Number] = [ABC]![SPB], " & _
"tblMain.[Ret Reason] = [ABC]![Ret_Reason], " & _
"tblMain.[Ret Type] = [ABC]![Ret_Type], " & _
"tblMain.[Ret Date] = [ABC]![Ret_Date], " & _
"tblMain.[Date Available for Sale] = [ABC]![Avail_Sale_Date], " & _
"tblMain.[Ready For Sale Date] = [ABC]![Ready_Sale_Date], " & _
"tblMain.[Ready For Sale Indicator] = IIf([ABC]![Ready_Sale_Date] Is Not Null,’X’), " & _
"tblMain.[Sold Date] = IIf(tblMain![Sold Date] Is Null,[ABC]![sold_date],tblMain![sold date]), " & _
"tblComments.[ITS Title Received Date] = IIf(tblComments![ITS Title Received Date] Is Null,[ABC]![Paper_Recvd_Date],tblComments![ITS Title Received Date]), " & _
"tblMain.[ABC Extract Date] = [ABC]![Processing_Date],tblMain.[ABC Initials] = [ABC]![Initials], " & _
"tblMain.[Ret Branch] = [ABC]![Ret_FSO],tblMain.[Ret Emp] = [ABC]![Ret_EMP4], " & _
"tblMain.[Ret Location] = [ABC]![Ret_Location],tblMain.Date_XX_Not = [ABC]![Date_XX_Not], " & _
"tblMain.Glad_R_Date = [ABC]![Glad_R_Date],tblMain.[Disposition Code] = [ABC]![Disposition Code], " & _
"tblMain.[Emp account] = [ABC]![Emp account number], tblMain.[RPEmpNum] = [ABC]![RPEmpNum]," & _
"tblMain.[RDAN] = [ABC]![RDAN], tblMain.[BusinessType] = [ABC]![BusinessType]," & _
"tblComments.[HSH Date] = IIF(IsNull(tblComments.[HSH Date]), [ABC]![Current_Recvd_Date], tblComments.[HSH Dropoff Date]) " & _
"WHERE (((tblMain.[Branch])= " & ABRS.Fields(0) & "))" DoCmd.RunSQL strSQL, False
Debug.Print "End: " & Now
ABRS.MoveNext Loop
End Sub The code joins 3 tables and updates 2 of them (tblMain, tblComments)with the information of 3rd one (HSH). This is in a WHILE until all accounts are updated. Do you think something is wrong with the code? It runs very well and sometimes generates the error. Is it because of Access / VBA connection to SQL Server. I mean some exceptions or bugs for this type of connection. Thanks, Farhad R
[email protected]

---

How many concurrent connection are you making?

---

Timeout issues can be caused by a number of problems. Typically they are caused by a deadlock condition where one process is holding a resource while attempting another operation. If the process blocking the operation is doing the same thing you’re locked out and pooched. The system can detect this in some cases. Consider that the query plan is created the FIRST time a SP is executed and reused indefinitely until it’s pushed out of cache by more important data.
Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS” with no rights for the sake of knowledge sharing.

---

I noticed that there are at most 6 concurrent connections. Also, there are 115 users who run the application everyday.

Farhad R
[email protected]

---

When Access starts reaching 10 performance starts to suffer. If you have 115 users running the app on a daily bases you should start to look for another front end alternative. I believe msde versione is a lot more powerful it uses the same engine as SQL Server not the jet engine.

---

Why aren’t you feeding a proper UPDATE statement to SQL Server by way of a pass-through query or an ADO connection? The default time-out that Jet assumes is 60 seconds, IIRC, and Jet UPDATEs on SQL Server tables can be time-consuming. You’ll have to check the correct syntax in T-SQL, because Jet-SQL is a bit different from that. Also, there is an incomplete IIf statement in your query:
IIf([ABC]![Ready_Sale_Date] Is Not Null,’X’)
It’s missing the False part. Finally, use tablename.fieldname instead of tablename!fieldname – the exclamation mark suggests it’s a field on a form or report, and Jet first has to check what kind of object you’re referring to (one of the nice things about Jet-SQL is that you can include calls to VBA functions and references to open Access objects, but there are a lot of performance pitfalls). Use the dot and Jet will immediately assume it’s a table referenced in the FROM clause.

---

]]>