"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.

"max text repl size" safety

I just increased the max size of text replication to 524288 bytes with below
Sql statements. The system allows me to do that but I'm wondering if there
is any issue/drawback that I should know about pumping the max size up by so
much. Thank you in advance for your advice.
EXEC sp_configure 'max text repl size', '524288'
go
RECONFIGURE WITH OVERRIDE
go
Basically it means that SQL can replicate data where a row contains more
than 524288 bytes. This will cause performance degradation on your system as
rows this size are going through.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>I just increased the max size of text replication to 524288 bytes with
>below
> Sql statements. The system allows me to do that but I'm wondering if
> there
> is any issue/drawback that I should know about pumping the max size up by
> so
> much. Thank you in advance for your advice.
> EXEC sp_configure 'max text repl size', '524288'
> go
> RECONFIGURE WITH OVERRIDE
> go
>
|||If you have a separate distributor server where does this setting need to be
issued. At the distributor or the publisher?
If the system is moving large files (2- 10 MB) what kind of impact will it
have? What impact will it have on both servers?
Thanks
"Hilary Cotter" wrote:

> Basically it means that SQL can replicate data where a row contains more
> than 524288 bytes. This will cause performance degradation on your system as
> rows this size are going through.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>
>
|||I believe its on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2DB417F1-5573-48EA-B1AC-C10B8760D75E@.microsoft.com...[vbcol=seagreen]
> If you have a separate distributor server where does this setting need to
> be
> issued. At the distributor or the publisher?
> If the system is moving large files (2- 10 MB) what kind of impact will it
> have? What impact will it have on both servers?
> Thanks
> "Hilary Cotter" wrote:

"max text repl size" safety

I just increased the max size of text replication to 524288 bytes with below
Sql statements. The system allows me to do that but I'm wondering if there
is any issue/drawback that I should know about pumping the max size up by so
much. Thank you in advance for your advice.
EXEC sp_configure 'max text repl size', '524288'
go
RECONFIGURE WITH OVERRIDE
goBasically it means that SQL can replicate data where a row contains more
than 524288 bytes. This will cause performance degradation on your system as
rows this size are going through.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>I just increased the max size of text replication to 524288 bytes with
>below
> Sql statements. The system allows me to do that but I'm wondering if
> there
> is any issue/drawback that I should know about pumping the max size up by
> so
> much. Thank you in advance for your advice.
> EXEC sp_configure 'max text repl size', '524288'
> go
> RECONFIGURE WITH OVERRIDE
> go
>|||If you have a separate distributor server where does this setting need to be
issued. At the distributor or the publisher?
If the system is moving large files (2- 10 MB) what kind of impact will it
have? What impact will it have on both servers?
Thanks
"Hilary Cotter" wrote:

> Basically it means that SQL can replicate data where a row contains more
> than 524288 bytes. This will cause performance degradation on your system
as
> rows this size are going through.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>
>|||I believe its on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2DB417F1-5573-48EA-B1AC-C10B8760D75E@.microsoft.com...[vbcol=seagreen]
> If you have a separate distributor server where does this setting need to
> be
> issued. At the distributor or the publisher?
> If the system is moving large files (2- 10 MB) what kind of impact will it
> have? What impact will it have on both servers?
> Thanks
> "Hilary Cotter" wrote:
>

"max text repl size" safety

I just increased the max size of text replication to 524288 bytes with below
Sql statements. The system allows me to do that but I'm wondering if there
is any issue/drawback that I should know about pumping the max size up by so
much. Thank you in advance for your advice.
EXEC sp_configure 'max text repl size', '524288'
go
RECONFIGURE WITH OVERRIDE
go
Basically it means that SQL can replicate data where a row contains more
than 524288 bytes. This will cause performance degradation on your system as
rows this size are going through.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>I just increased the max size of text replication to 524288 bytes with
>below
> Sql statements. The system allows me to do that but I'm wondering if
> there
> is any issue/drawback that I should know about pumping the max size up by
> so
> much. Thank you in advance for your advice.
> EXEC sp_configure 'max text repl size', '524288'
> go
> RECONFIGURE WITH OVERRIDE
> go
>
|||If you have a separate distributor server where does this setting need to be
issued. At the distributor or the publisher?
If the system is moving large files (2- 10 MB) what kind of impact will it
have? What impact will it have on both servers?
Thanks
"Hilary Cotter" wrote:

> Basically it means that SQL can replicate data where a row contains more
> than 524288 bytes. This will cause performance degradation on your system as
> rows this size are going through.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>
>
|||I believe its on the publisher.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2DB417F1-5573-48EA-B1AC-C10B8760D75E@.microsoft.com...[vbcol=seagreen]
> If you have a separate distributor server where does this setting need to
> be
> issued. At the distributor or the publisher?
> If the system is moving large files (2- 10 MB) what kind of impact will it
> have? What impact will it have on both servers?
> Thanks
> "Hilary Cotter" wrote:

"max text repl size" safety

I just increased the max size of text replication to 524288 bytes with below
Sql statements. The system allows me to do that but I'm wondering if there
is any issue/drawback that I should know about pumping the max size up by so
much. Thank you in advance for your advice.
EXEC sp_configure 'max text repl size', '524288'
go
RECONFIGURE WITH OVERRIDE
goBasically it means that SQL can replicate data where a row contains more
than 524288 bytes. This will cause performance degradation on your system as
rows this size are going through.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Zeng" <Zeng5000@.hotmail.com> wrote in message
news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>I just increased the max size of text replication to 524288 bytes with
>below
> Sql statements. The system allows me to do that but I'm wondering if
> there
> is any issue/drawback that I should know about pumping the max size up by
> so
> much. Thank you in advance for your advice.
> EXEC sp_configure 'max text repl size', '524288'
> go
> RECONFIGURE WITH OVERRIDE
> go
>|||If you have a separate distributor server where does this setting need to be
issued. At the distributor or the publisher?
If the system is moving large files (2- 10 MB) what kind of impact will it
have? What impact will it have on both servers?
Thanks
"Hilary Cotter" wrote:
> Basically it means that SQL can replicate data where a row contains more
> than 524288 bytes. This will cause performance degradation on your system as
> rows this size are going through.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Zeng" <Zeng5000@.hotmail.com> wrote in message
> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
> >I just increased the max size of text replication to 524288 bytes with
> >below
> > Sql statements. The system allows me to do that but I'm wondering if
> > there
> > is any issue/drawback that I should know about pumping the max size up by
> > so
> > much. Thank you in advance for your advice.
> >
> > EXEC sp_configure 'max text repl size', '524288'
> > go
> > RECONFIGURE WITH OVERRIDE
> > go
> >
> >
>
>|||I believe its on the publisher.
--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:2DB417F1-5573-48EA-B1AC-C10B8760D75E@.microsoft.com...
> If you have a separate distributor server where does this setting need to
> be
> issued. At the distributor or the publisher?
> If the system is moving large files (2- 10 MB) what kind of impact will it
> have? What impact will it have on both servers?
> Thanks
> "Hilary Cotter" wrote:
>> Basically it means that SQL can replicate data where a row contains more
>> than 524288 bytes. This will cause performance degradation on your system
>> as
>> rows this size are going through.
>> --
>> Hilary Cotter
>> Looking for a SQL Server replication book?
>> http://www.nwsu.com/0974973602.html
>> Looking for a FAQ on Indexing Services/SQL FTS
>> http://www.indexserverfaq.com
>> "Zeng" <Zeng5000@.hotmail.com> wrote in message
>> news:e3DD325%23FHA.3992@.TK2MSFTNGP15.phx.gbl...
>> >I just increased the max size of text replication to 524288 bytes with
>> >below
>> > Sql statements. The system allows me to do that but I'm wondering if
>> > there
>> > is any issue/drawback that I should know about pumping the max size up
>> > by
>> > so
>> > much. Thank you in advance for your advice.
>> >
>> > EXEC sp_configure 'max text repl size', '524288'
>> > go
>> > RECONFIGURE WITH OVERRIDE
>> > go
>> >
>> >
>>

"IF"s in a view?

I Access SQL one can imbed "IF" statements in a select query (or view).
In SQL Server, Microsoft seems to only allow the use of control statements such as IF and CASE in Stored Procedures but not in a view.

Question: Is there any way to use "IF" or "CASE" in a view? If a stored procdure is the best route, how do i access the results set afterwards?Case works fine - please post the view you tried to create.|||Here is the SQL fragment converted from ACCESS IIF's to SQL Server CASE. I don't have access to the SQL Server at the moment but I will try out your suggestion first chance I get.

CASE
WHEN [IncidentHeaderDynamicsCustNmbr] Like '99900%' THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
WHEN IsNull(IncidentHeaderSiteID])=True And IsNull(IncidentHeaderClientAddressID])=True) THEN
[CustName],
[CntcPrsn],
[StmtName],
[Address1],
[Address2],
[City],
[State],
[Country],
[Zip],
[Phone1],
[Phone2],
[Fax],
ELSE
Trim([IncidentHeaderSiteCustName]))) AS InvoiceHeaderSiteCustName,
Trim([IncidentHeaderSiteCntcPrsn]))) AS InvoiceHeaderSiteCntcPrsn,
Trim([IncidentHeaderSiteStmtName]))) AS InvoiceHeaderSiteStmtName,
Trim([IncidentHeaderSiteAddress1]))) AS InvoiceHeaderSiteAddress1,
Trim([IncidentHeaderSiteAddress2]))) AS InvoiceHeaderSiteAddress2,
Trim([IncidentHeaderSiteCity]))) AS InvoiceHeaderSiteCity,
Trim([IncidentHeaderSiteState]))) AS InvoiceHeaderSiteState,
Trim([IncidentHeaderSiteCountry]))) AS InvoiceHeaderSiteCountry,
Trim([IncidentHeaderSiteZip]))) AS InvoiceHeaderSiteZip,
Trim([IncidentHeaderSitePhone1]))) AS InvoiceHeaderSitePhone1,
Trim([IncidentHeaderSitePhone2]))) AS InvoiceHeaderSitePhone2,
Trim([IncidentHeaderSiteFax]))) AS InvoiceHeaderSiteFax,
END|||The problem looks like with your syntax (check out BOL) - the case statement goes as follows:

CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

or

CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END

Anyway, you have case when - but you are missing the end statement - Maybe you are thinking you can return multiple results - You will have to test for each case.