Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ruchirj07
Starting Member
37 Posts |
 Posted - 2007-10-11 : 08:29:31
|
| Hi,I have a procedure that creates a SQL Agent Job which runs the actual procedure. When the same procedure is executed twice (meaning running same procedure having select and insert statement) in two different jobs, i get an error :The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. [SQLSTATE HY000] (Error 1204). The step failed.In procedure, i'm using either "with (nolock) or with (rowlock)".Can anyone please help me out on this. I need its solution uregntly.Thanks,Ruchir |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-11 : 23:01:50
|
| Jobs are created by sp? Did you check if the process holds any lock? |
 |
|
|
ruchirj07
Starting Member
37 Posts |
Posted - 2007-10-12 : 01:56:42
|
| Yeah there are locks but these locks are row specific and hence should not effect the whole table.Also i'm checking the query that is running while the job(s) are running, some jobs gets into "suspended" state for long..is there any way i can set any preference so that the queries are given equal importance rather than taking them in random? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-12 : 23:56:58
|
| Don't think so. By the way, did you see blocking? Should wait for something on the server. |
|
|
|
ruchirj07
Starting Member
37 Posts |
Posted - 2007-10-15 : 01:51:19
|
| What i'm seeing is that when suddenly more than 2 jobs accessing same stored procedure starts running only 2 of then runs and others goes into "suspended state" and finally rollback saying deadlock error.My only concern is : if i'm manually giving hint of rowlock and nolock than why the whole table is getting locked?Is there any answer for the same? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-10-15 : 22:01:01
|
| You can set trace in profiler to get details of deadlock. |
|
|
|
|
|
|
Lock Error