|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Locks and SQLAgent - Generic refresher, Alert Engine
Sql server 2000 sp4
1. I have two programs running on msdb database (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there processes? 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) We have over 100 users on the database using one application but only one user have lot of locked resources. I am trying to find why one user has locked many resources while other users only have few locks on resource. It seems like that user creates deadlock situation for other users. dbdba -- ontario, canada db,
SQL Agent runs as a separate process on the server. If you look at Windows Services, you will see it running there. (Is that what you were asking?) http://msdn.microsoft.com/en-us/library/aa213039(SQL.80).aspx is the topic "Understanding Locking in SQL Server" along with some subtopics to explain particular details. If you are having deadlock problems, you should be running with trace 1205 enabled. This will record details on deadlocks in the SQL Server Logs (the application log for SQL server) that will help you see what code is deadlocking and against what. (There are better tools starting in SQL Server 2005.) DBCC TRACEON (1205) -- Or set the server to startup with 1205 enabled. RLF Show quoteHide quote "db" <d*@discussions.microsoft.com> wrote in message news:7048528E-AAFE-4237-A6F3-8F376A447C9C@microsoft.com... > Sql server 2000 sp4 > > 1. I have two programs running on msdb database > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are > there > processes? > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE > (key,DB, > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > We have over 100 users on the database using one application but only one > user have lot of locked resources. I am trying to find why one user has > locked many resources while other users only have few locks on resource. > It > seems like that user creates deadlock situation for other users. > > dbdba > -- > ontario, canada One user having locks on many resources isn't necessarily a problem. If you
observe locks acquired and released long enough, you'd find that the number of locks is a very dynamic one, and is not a good indicator for any performance related troubleshooting exercise. I'd suggest that you focus on whether there is any real perofrmance problemas a result (e.g. is there any sustained blocking? Is anyone complaining about performance? Has your app actually run into deadlocks?) Linchi Show quoteHide quote "db" wrote: > Sql server 2000 sp4 > > 1. I have two programs running on msdb database > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > processes? > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > We have over 100 users on the database using one application but only one > user have lot of locked resources. I am trying to find why one user has > locked many resources while other users only have few locks on resource. It > seems like that user creates deadlock situation for other users. > > dbdba > -- > ontario, canada Hi
Yes we have real perofrmance problemas a result (there is sustained blocking). People are complaining about performance though all performance counter are good. App is running into deadlocks and usually by processes of same identified user. That user connects to our database server over intranet. -- Show quoteHide quoteontario, canada "Linchi Shea" wrote: > One user having locks on many resources isn't necessarily a problem. If you > observe locks acquired and released long enough, you'd find that the number > of locks is a very dynamic one, and is not a good indicator for any > performance related troubleshooting exercise. I'd suggest that you focus on > whether there is any real perofrmance problemas a result (e.g. is there any > sustained blocking? Is anyone complaining about performance? Has your app > actually run into deadlocks?) > > Linchi > > "db" wrote: > > > Sql server 2000 sp4 > > > > 1. I have two programs running on msdb database > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > > processes? > > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > > > We have over 100 users on the database using one application but only one > > user have lot of locked resources. I am trying to find why one user has > > locked many resources while other users only have few locks on resource. It > > seems like that user creates deadlock situation for other users. > > > > dbdba > > -- > > ontario, canada If you have sustained blocking, you need to identify the spid at the head of
each blocking chain. Google for "SQL Server" blocking, you'll find plenty of info and scripts to use. I just use a really simple query: select * from master..sysprocesses where spid in (select blocked from master..sysprocesses) or blocked != 0 The spid(s) whose column blocked has no value is your head blocker, and you should examine what it is doing. For troubleshooting deadlocks, you can turn on trace flag: DBCC TRACEON (3605,1204,-1) This will cause the deadlock info to be logged in SQL errorlog that you can rview later to find what spids and resources are involved in a deadlock. Linchi Show quoteHide quote "db" wrote: > Hi > > Yes we have real perofrmance problemas a result (there is > sustained blocking). People are complaining about performance though all > performance counter are good. App is running into deadlocks and usually by > processes of same identified user. That user connects to our database server > over intranet. > -- > ontario, canada > > > "Linchi Shea" wrote: > > > One user having locks on many resources isn't necessarily a problem. If you > > observe locks acquired and released long enough, you'd find that the number > > of locks is a very dynamic one, and is not a good indicator for any > > performance related troubleshooting exercise. I'd suggest that you focus on > > whether there is any real perofrmance problemas a result (e.g. is there any > > sustained blocking? Is anyone complaining about performance? Has your app > > actually run into deadlocks?) > > > > Linchi > > > > "db" wrote: > > > > > Sql server 2000 sp4 > > > > > > 1. I have two programs running on msdb database > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > > > processes? > > > > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > > > > > We have over 100 users on the database using one application but only one > > > user have lot of locked resources. I am trying to find why one user has > > > locked many resources while other users only have few locks on resource. It > > > seems like that user creates deadlock situation for other users. > > > > > > dbdba > > > -- > > > ontario, canada Also yesterday we had error in one user screen while running an application
module " Data provider or other service returned an E_FAIL status." -- Show quoteHide quoteontario, canada "Linchi Shea" wrote: > One user having locks on many resources isn't necessarily a problem. If you > observe locks acquired and released long enough, you'd find that the number > of locks is a very dynamic one, and is not a good indicator for any > performance related troubleshooting exercise. I'd suggest that you focus on > whether there is any real perofrmance problemas a result (e.g. is there any > sustained blocking? Is anyone complaining about performance? Has your app > actually run into deadlocks?) > > Linchi > > "db" wrote: > > > Sql server 2000 sp4 > > > > 1. I have two programs running on msdb database > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > > processes? > > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > > > We have over 100 users on the database using one application but only one > > user have lot of locked resources. I am trying to find why one user has > > locked many resources while other users only have few locks on resource. It > > seems like that user creates deadlock situation for other users. > > > > dbdba > > -- > > ontario, canada That seems to be an error thrown by the app instead of SQL Server.
Linchi Show quoteHide quote "db" wrote: > Also yesterday we had error in one user screen while running an application > module > > " Data provider or other service returned an E_FAIL status." > > -- > ontario, canada > > > "Linchi Shea" wrote: > > > One user having locks on many resources isn't necessarily a problem. If you > > observe locks acquired and released long enough, you'd find that the number > > of locks is a very dynamic one, and is not a good indicator for any > > performance related troubleshooting exercise. I'd suggest that you focus on > > whether there is any real perofrmance problemas a result (e.g. is there any > > sustained blocking? Is anyone complaining about performance? Has your app > > actually run into deadlocks?) > > > > Linchi > > > > "db" wrote: > > > > > Sql server 2000 sp4 > > > > > > 1. I have two programs running on msdb database > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > > > processes? > > > > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > > > > > We have over 100 users on the database using one application but only one > > > user have lot of locked resources. I am trying to find why one user has > > > locked many resources while other users only have few locks on resource. It > > > seems like that user creates deadlock situation for other users. > > > > > > dbdba > > > -- > > > ontario, canada Hi,
We are also on SQL 2000 sp4, and ran into deadlocks while processing a large transaction updates. We used the DBCC trace and captued some deadlocks. However, the software vendor said, none of other clients with the same setting have any error and no "slowing down" or have-to-killed the connections problems. Could hardware or windows settings contribute to the creation of deadlocks? Any suggestion, Thanks Peter Show quoteHide quote "Linchi Shea" wrote: > That seems to be an error thrown by the app instead of SQL Server. > > Linchi > > "db" wrote: > > > Also yesterday we had error in one user screen while running an application > > module > > > > " Data provider or other service returned an E_FAIL status." > > > > -- > > ontario, canada > > > > > > "Linchi Shea" wrote: > > > > > One user having locks on many resources isn't necessarily a problem. If you > > > observe locks acquired and released long enough, you'd find that the number > > > of locks is a very dynamic one, and is not a good indicator for any > > > performance related troubleshooting exercise. I'd suggest that you focus on > > > whether there is any real perofrmance problemas a result (e.g. is there any > > > sustained blocking? Is anyone complaining about performance? Has your app > > > actually run into deadlocks?) > > > > > > Linchi > > > > > > "db" wrote: > > > > > > > Sql server 2000 sp4 > > > > > > > > 1. I have two programs running on msdb database > > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What are there > > > > processes? > > > > > > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE (key,DB, > > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > > > > > > > > We have over 100 users on the database using one application but only one > > > > user have lot of locked resources. I am trying to find why one user has > > > > locked many resources while other users only have few locks on resource. It > > > > seems like that user creates deadlock situation for other users. > > > > > > > > dbdba > > > > -- > > > > ontario, canada Peter,
Anything that increases the length of a transaction can increase the possibility of a deadlock. This includes longer I/O times due to slower disks or high disk activity, high CPU contention, contention for memory with other processes on the server, and so forth. Even so, I would consider "nobody else has the problem" to be a pretty unhelpful answer. (I am being tactful.) RLF Show quoteHide quote "Peter" <Pe***@discussions.microsoft.com> wrote in message news:C142018F-1176-48E6-9864-2DAB548D6E90@microsoft.com... > Hi, > > We are also on SQL 2000 sp4, and ran into deadlocks while processing a > large > transaction updates. We used the DBCC trace and captued some deadlocks. > However, the software vendor said, none of other clients with the same > setting have any error and no "slowing down" or have-to-killed the > connections problems. > > Could hardware or windows settings contribute to the creation of > deadlocks? > > Any suggestion, > > Thanks > Peter > > > > "Linchi Shea" wrote: > >> That seems to be an error thrown by the app instead of SQL Server. >> >> Linchi >> >> "db" wrote: >> >> > Also yesterday we had error in one user screen while running an >> > application >> > module >> > >> > " Data provider or other service returned an E_FAIL status." >> > >> > -- >> > ontario, canada >> > >> > >> > "Linchi Shea" wrote: >> > >> > > One user having locks on many resources isn't necessarily a problem. >> > > If you >> > > observe locks acquired and released long enough, you'd find that the >> > > number >> > > of locks is a very dynamic one, and is not a good indicator for any >> > > performance related troubleshooting exercise. I'd suggest that you >> > > focus on >> > > whether there is any real perofrmance problemas a result (e.g. is >> > > there any >> > > sustained blocking? Is anyone complaining about performance? Has your >> > > app >> > > actually run into deadlocks?) >> > > >> > > Linchi >> > > >> > > "db" wrote: >> > > >> > > > Sql server 2000 sp4 >> > > > >> > > > 1. I have two programs running on msdb database >> > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What >> > > > are there >> > > > processes? >> > > > >> > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE >> > > > (key,DB, >> > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) >> > > > >> > > > We have over 100 users on the database using one application but >> > > > only one >> > > > user have lot of locked resources. I am trying to find why one user >> > > > has >> > > > locked many resources while other users only have few locks on >> > > > resource. It >> > > > seems like that user creates deadlock situation for other users. >> > > > >> > > > dbdba >> > > > -- >> > > > ontario, canada I see. Our SQL Server 2000 is on a VM Virtual server, could this cause the
deadlocks to occure? Thanks Peter Show quoteHide quote "Russell Fields" wrote: > Peter, > > Anything that increases the length of a transaction can increase the > possibility of a deadlock. This includes longer I/O times due to slower > disks or high disk activity, high CPU contention, contention for memory with > other processes on the server, and so forth. > > Even so, I would consider "nobody else has the problem" to be a pretty > unhelpful answer. (I am being tactful.) > > RLF > > "Peter" <Pe***@discussions.microsoft.com> wrote in message > news:C142018F-1176-48E6-9864-2DAB548D6E90@microsoft.com... > > Hi, > > > > We are also on SQL 2000 sp4, and ran into deadlocks while processing a > > large > > transaction updates. We used the DBCC trace and captued some deadlocks. > > However, the software vendor said, none of other clients with the same > > setting have any error and no "slowing down" or have-to-killed the > > connections problems. > > > > Could hardware or windows settings contribute to the creation of > > deadlocks? > > > > Any suggestion, > > > > Thanks > > Peter > > > > > > > > "Linchi Shea" wrote: > > > >> That seems to be an error thrown by the app instead of SQL Server. > >> > >> Linchi > >> > >> "db" wrote: > >> > >> > Also yesterday we had error in one user screen while running an > >> > application > >> > module > >> > > >> > " Data provider or other service returned an E_FAIL status." > >> > > >> > -- > >> > ontario, canada > >> > > >> > > >> > "Linchi Shea" wrote: > >> > > >> > > One user having locks on many resources isn't necessarily a problem. > >> > > If you > >> > > observe locks acquired and released long enough, you'd find that the > >> > > number > >> > > of locks is a very dynamic one, and is not a good indicator for any > >> > > performance related troubleshooting exercise. I'd suggest that you > >> > > focus on > >> > > whether there is any real perofrmance problemas a result (e.g. is > >> > > there any > >> > > sustained blocking? Is anyone complaining about performance? Has your > >> > > app > >> > > actually run into deadlocks?) > >> > > > >> > > Linchi > >> > > > >> > > "db" wrote: > >> > > > >> > > > Sql server 2000 sp4 > >> > > > > >> > > > 1. I have two programs running on msdb database > >> > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). What > >> > > > are there > >> > > > processes? > >> > > > > >> > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK TYPE > >> > > > (key,DB, > >> > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) > >> > > > > >> > > > We have over 100 users on the database using one application but > >> > > > only one > >> > > > user have lot of locked resources. I am trying to find why one user > >> > > > has > >> > > > locked many resources while other users only have few locks on > >> > > > resource. It > >> > > > seems like that user creates deadlock situation for other users. > >> > > > > >> > > > dbdba > >> > > > -- > >> > > > ontario, canada > > Peter,
It depends on the server. If the hardware is overcommitted with too many VMs or the I/O system is not designed/allocated with SQL Server in mind, there may be performance problems with all of the issues that I mentioned previously. On the other hand a VM that provides plenty of resources to the SQL Server may run just fine. I think you will need to have someone use the VM monitoring software to determine the resources available and being used by your VM. FWIW, RLF Show quoteHide quote "Peter" <Pe***@discussions.microsoft.com> wrote in message news:D2E6E82A-4452-4C40-98CF-4EB829162D0D@microsoft.com... >I see. Our SQL Server 2000 is on a VM Virtual server, could this cause the > deadlocks to occure? > > Thanks > Peter > > "Russell Fields" wrote: > >> Peter, >> >> Anything that increases the length of a transaction can increase the >> possibility of a deadlock. This includes longer I/O times due to slower >> disks or high disk activity, high CPU contention, contention for memory >> with >> other processes on the server, and so forth. >> >> Even so, I would consider "nobody else has the problem" to be a pretty >> unhelpful answer. (I am being tactful.) >> >> RLF >> >> "Peter" <Pe***@discussions.microsoft.com> wrote in message >> news:C142018F-1176-48E6-9864-2DAB548D6E90@microsoft.com... >> > Hi, >> > >> > We are also on SQL 2000 sp4, and ran into deadlocks while processing a >> > large >> > transaction updates. We used the DBCC trace and captued some >> > deadlocks. >> > However, the software vendor said, none of other clients with the same >> > setting have any error and no "slowing down" or have-to-killed the >> > connections problems. >> > >> > Could hardware or windows settings contribute to the creation of >> > deadlocks? >> > >> > Any suggestion, >> > >> > Thanks >> > Peter >> > >> > >> > >> > "Linchi Shea" wrote: >> > >> >> That seems to be an error thrown by the app instead of SQL Server. >> >> >> >> Linchi >> >> >> >> "db" wrote: >> >> >> >> > Also yesterday we had error in one user screen while running an >> >> > application >> >> > module >> >> > >> >> > " Data provider or other service returned an E_FAIL status." >> >> > >> >> > -- >> >> > ontario, canada >> >> > >> >> > >> >> > "Linchi Shea" wrote: >> >> > >> >> > > One user having locks on many resources isn't necessarily a >> >> > > problem. >> >> > > If you >> >> > > observe locks acquired and released long enough, you'd find that >> >> > > the >> >> > > number >> >> > > of locks is a very dynamic one, and is not a good indicator for >> >> > > any >> >> > > performance related troubleshooting exercise. I'd suggest that you >> >> > > focus on >> >> > > whether there is any real perofrmance problemas a result (e.g. is >> >> > > there any >> >> > > sustained blocking? Is anyone complaining about performance? Has >> >> > > your >> >> > > app >> >> > > actually run into deadlocks?) >> >> > > >> >> > > Linchi >> >> > > >> >> > > "db" wrote: >> >> > > >> >> > > > Sql server 2000 sp4 >> >> > > > >> >> > > > 1. I have two programs running on msdb database >> >> > > > (SQLAgent - Generic refresher) abd (SQLAgent - Alert Engine). >> >> > > > What >> >> > > > are there >> >> > > > processes? >> >> > > > >> >> > > > 2. I need to understand lock modes (s,X,IX,X, sch-M) AND LOCK >> >> > > > TYPE >> >> > > > (key,DB, >> >> > > > RID, IDX,PAG,tab) and corresponging resource like (a90112e94ea7) >> >> > > > >> >> > > > We have over 100 users on the database using one application but >> >> > > > only one >> >> > > > user have lot of locked resources. I am trying to find why one >> >> > > > user >> >> > > > has >> >> > > > locked many resources while other users only have few locks on >> >> > > > resource. It >> >> > > > seems like that user creates deadlock situation for other users. >> >> > > > >> >> > > > dbdba >> >> > > > -- >> >> > > > ontario, canada >> >>  
Other interesting topics
Connection server utilizing trusted connection to other SQL Server
RESTORING drop table not showing up in the transaction log deleting DB strange BLOB beahaviour AWE on SQL Server2005 ID large queries while they execute and kill 'Generate scripts' functionality in SQL 2005 not being consistent Problem with SUM - Help please Backups & Transaction Files |
|||||||||||||||||||||||
