"The SQL Server cannot obtain a LOCK resource at this time" error

Hi there,
I am running on my machine the following SQL statements on my (local) SQL
Server:
SET NOCOUNT ON
SET ROWCOUNT 1500
delete_more:
delete table1 where field1 = '1' and field2 = '2'
IF @.@.ROWCOUNT > 0 GOTO delete_more
SET ROWCOUNT 0
The table has arround 5 million records and the DELETE statement would
delete arround 100000 records. I can see by running sp_lock that SQL is
escalating the locks to a table lock.
I am running in paralel the following:
SET ROWCOUNT 100
delete table1 where field1 = '3' and field2 = '2'
SET ROWCOUNT 0
This query is running fine, however the DELETE statement fails with the
error 1204:
"The SQL Server cannot obtain a LOCK resource at this time". Can somebody
shed some light for me here? Am I not able to run two DELETE statements is
paralel or am I doing something wrong?
I am running with the run_value for locks set to 0.
Thank you in advance.You mentioned there is already a table lock on that particular table, as it
is deleting millions of records. You probably need to wait for this
transaction to complete before you execute another delete statement on the
same table.
I suspect changing isolation level would solve your purpose.
"Andrei" wrote:
> Hi there,
> I am running on my machine the following SQL statements on my (local) SQL
> Server:
> SET NOCOUNT ON
> SET ROWCOUNT 1500
> delete_more:
> delete table1 where field1 = '1' and field2 = '2'
> IF @.@.ROWCOUNT > 0 GOTO delete_more
> SET ROWCOUNT 0
> The table has arround 5 million records and the DELETE statement would
> delete arround 100000 records. I can see by running sp_lock that SQL is
> escalating the locks to a table lock.
> I am running in paralel the following:
> SET ROWCOUNT 100
> delete table1 where field1 = '3' and field2 = '2'
> SET ROWCOUNT 0
> This query is running fine, however the DELETE statement fails with the
> error 1204:
> "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> shed some light for me here? Am I not able to run two DELETE statements is
> paralel or am I doing something wrong?
> I am running with the run_value for locks set to 0.
> Thank you in advance.|||What I want is to run several jobs concurrently, I do not want to wait for
one to finish in order to start the second one.
However I do not understand why I get the error, since the first statement
is suppose to delete 1500 records (so maximum 1500 key locks applied, if
there is no lock escalation) and the second statement is deleting 100
records. Am I missing something here?
"GYK" wrote:
> You mentioned there is already a table lock on that particular table, as it
> is deleting millions of records. You probably need to wait for this
> transaction to complete before you execute another delete statement on the
> same table.
> I suspect changing isolation level would solve your purpose.
> "Andrei" wrote:
> > Hi there,
> >
> > I am running on my machine the following SQL statements on my (local) SQL
> > Server:
> >
> > SET NOCOUNT ON
> >
> > SET ROWCOUNT 1500
> > delete_more:
> > delete table1 where field1 = '1' and field2 = '2'
> > IF @.@.ROWCOUNT > 0 GOTO delete_more
> > SET ROWCOUNT 0
> >
> > The table has arround 5 million records and the DELETE statement would
> > delete arround 100000 records. I can see by running sp_lock that SQL is
> > escalating the locks to a table lock.
> >
> > I am running in paralel the following:
> > SET ROWCOUNT 100
> > delete table1 where field1 = '3' and field2 = '2'
> > SET ROWCOUNT 0
> >
> > This query is running fine, however the DELETE statement fails with the
> > error 1204:
> > "The SQL Server cannot obtain a LOCK resource at this time". Can somebody
> > shed some light for me here? Am I not able to run two DELETE statements is
> > paralel or am I doing something wrong?
> >
> > I am running with the run_value for locks set to 0.
> >
> > Thank you in advance.

"Resource is low, some results are dropped" using Timbuktu

You don't need to be familiar with Timbuktu to add value to this. If you
know SQL Server, have you ever had this happen?
I'm using Timbuktu 5.1 to remote into a SQL Server. I sometimes get this
exact message, verbatim, "Resource is low, some results are dropped."
I checked the server and it's got a load of resources. I never thought it
could be my client since I'm only asked Timbuktu to render the server
desktop. Any suggestions or common experience?
Larry wrote:
> You don't need to be familiar with Timbuktu to add value to this. If
> you know SQL Server, have you ever had this happen?
> I'm using Timbuktu 5.1 to remote into a SQL Server. I sometimes get
> this exact message, verbatim, "Resource is low, some results are
> dropped."
> I checked the server and it's got a load of resources. I never
> thought it could be my client since I'm only asked Timbuktu to render
> the server desktop. Any suggestions or common experience?
I'm not familiar with Timbuktu, but I've only ever seen that message in
Query Analyzer on a client when I run a batch that creates thousands of
result sets and use the grid display for the results.
David Gugick
Imceda Software
www.imceda.com
|||I've seen that message in Profiler before, when looking at really active
events without enough filters... Definitely a client issue, I believe.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OGkyz0u5EHA.3996@.TK2MSFTNGP10.phx.gbl...
> Larry wrote:
> I'm not familiar with Timbuktu, but I've only ever seen that message in
> Query Analyzer on a client when I run a batch that creates thousands of
> result sets and use the grid display for the results.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

"Resource is low, some results are dropped" using Timbuktu

You don't need to be familiar with Timbuktu to add value to this. If you
know SQL Server, have you ever had this happen?
I'm using Timbuktu 5.1 to remote into a SQL Server. I sometimes get this
exact message, verbatim, "Resource is low, some results are dropped."
I checked the server and it's got a load of resources. I never thought it
could be my client since I'm only asked Timbuktu to render the server
desktop. Any suggestions or common experience?Larry wrote:
> You don't need to be familiar with Timbuktu to add value to this. If
> you know SQL Server, have you ever had this happen?
> I'm using Timbuktu 5.1 to remote into a SQL Server. I sometimes get
> this exact message, verbatim, "Resource is low, some results are
> dropped."
> I checked the server and it's got a load of resources. I never
> thought it could be my client since I'm only asked Timbuktu to render
> the server desktop. Any suggestions or common experience?
I'm not familiar with Timbuktu, but I've only ever seen that message in
Query Analyzer on a client when I run a batch that creates thousands of
result sets and use the grid display for the results.
--
David Gugick
Imceda Software
www.imceda.com|||I've seen that message in Profiler before, when looking at really active
events without enough filters... Definitely a client issue, I believe.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:OGkyz0u5EHA.3996@.TK2MSFTNGP10.phx.gbl...
> Larry wrote:
> > You don't need to be familiar with Timbuktu to add value to this. If
> > you know SQL Server, have you ever had this happen?
> >
> > I'm using Timbuktu 5.1 to remote into a SQL Server. I sometimes get
> > this exact message, verbatim, "Resource is low, some results are
> > dropped."
> >
> > I checked the server and it's got a load of resources. I never
> > thought it could be my client since I'm only asked Timbuktu to render
> > the server desktop. Any suggestions or common experience?
> I'm not familiar with Timbuktu, but I've only ever seen that message in
> Query Analyzer on a client when I run a batch that creates thousands of
> result sets and use the grid display for the results.
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

"Resource is low, some results are dropped"

I've been getting this message in a dialog box intermittently. Most recently
when trying to execute 'select @.@.trancount' after a statement attempted to
insert 100 rows into a table with only 10 rather small, varchar(256) columns
Everything that turned up on a Google search basically said throw more
hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
1039304, available memory = 333208(K) and System Cache = 507972 along with
2% CPU so I don't believe that it's a memory issue or more hardware will
solve the underlying issue.
Does anyone have an answer for this other than throw more hardware at it?
Where do you see this error? Query Analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256) columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K) of
> 1039304, available memory = 333208(K) and System Cache = 507972 along with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Hi,
Looks like you are executing the query in GRID result pane in query
analyzer. Could you change the mode to Text and try.
How to change:-
1. In query analyzer
2. Go to Query menu
3. Select "Result in Text"
4. Execute the query.
Thanks
Hari
SQL Server MVP
"Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
> I've been getting this message in a dialog box intermittently. Most
> recently
> when trying to execute 'select @.@.trancount' after a statement attempted to
> insert 100 rows into a table with only 10 rather small, varchar(256)
> columns
> Everything that turned up on a Google search basically said throw more
> hardware at it. I'd like to know what the underlying issue is. I'm on a
> 3ghz
> pentium 4 with 1 gig of ram. Task Manager shows total Physical Memory(K)
> of
> 1039304, available memory = 333208(K) and System Cache = 507972 along
> with
> 2% CPU so I don't believe that it's a memory issue or more hardware will
> solve the underlying issue.
> Does anyone have an answer for this other than throw more hardware at it?
|||Or use the shortcuts CTRL + T for text mode, CTRL + D for grid
http://sqlservercode.blogspot.com/
"Hari Prasad" wrote:

> Hi,
> Looks like you are executing the query in GRID result pane in query
> analyzer. Could you change the mode to Text and try.
>
> How to change:-
> 1. In query analyzer
> 2. Go to Query menu
> 3. Select "Result in Text"
> 4. Execute the query.
> Thanks
> Hari
> SQL Server MVP
>
>
> "Barry Forrest" <barry.forrest@.no-spam.ps.net> wrote in message
> news:DB948FD8-DF27-4EC3-AA40-59F9718318FB@.microsoft.com...
>
>
|||re: Resource is low, some results are dropped
There are a few different reasons this could happen.
1) Not patched up! Make sure you have the latest patches from Microsoft.
Service Packs
http://www.microsoft.com/sql/downloads/2000/sp4.mspx
Security Patches
You can check Technet for the latest patches...
http://www.microsoft.com/technet/security/current.aspx
2) How, and how many, results are returned.
Query Analyzer returns results in one of two ways when you execute SQL Statements (Text or Grid) It can execute and return the most complicated queries from huge databases with large result sets.
However, it has a problem returning multiple results to grid. Each grid requires a certain amount of resources, and if you execute a large number of queries, it eventually will run out memory and drop some results.
I was looping throogh sysobjects and syscolumns, executing a select statement on every column for every table. (Code GEnerator) It was trying to open a grid for every column in my database. Not that Pubs or Northwind would cause it, but my production da
tabase had a lot more objects. My guess is that this requires too many resources to complete.
If I changed the output for executing the queries to text mode, or I printed the information to the screen instead of executing t-SQL I did not get the error.
Try running it in text mode. Menu Bar - Query - Results in grid or Results in Text.
The keyboard shortcuts are Ctrl+D for Grid and Ctrl+T for Text.
Mike Pittser
Database Architect
sql2k5dba@.yahoo.com
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...