I have a question about transaction when lock_timeout error occurs
the procedure as follows,
CREATE PROCEDURE dbo.usp_glock
AS
BEGIN
SET NOCOUNT ON
SET LOCK_TIMEOUT 2000
UPDATE glocktbl SET name='john'
WHERE id=1
IF @.@.Error<>0
BEGIN
GOTO Err_Handle
END
Return 0
Err_Handle:
DECLARE @.intID INT
--/*
DECLARE cursor_Sql CURSOR
LOCAL
FORWARD_ONLY
STATIC
FOR
SELECT TOP 1 id FROM gcurtbl
OPEN cursor_Sql
FETCH NEXT FROM cursor_Sql
INTO @.intID
WHILE @.@.FETCH_STATUS = 0
BEGIN
print @.intID
End
Close cursor_Sql
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
END
When lock happens, then error message 1222, "Lock request time-out period
exceeded" was catched by error handle. In normal case, the transaction will
not be rolled back, and this store procedure can continue to next statement
,until execute 'insert into gerrtbl (errdesc) values('Lock time out
error.')'.but actually,this procedure terminated when execute 'OPEN
cursor_Sql FETCH NEXT FROM cursor_Sql'.
who can help me explain such phenomenon?
thanks a lot.Try setting the lock-timeout to 0 (indefinite) or increase it as appropriate
.
SET LOCK_TIMEOUT 0;
http://msdn.microsoft.com/library/d... />
a_5n78.asp
http://msdn.microsoft.com/library/d... />
t_1yr8.asp
ML
http://milambda.blogspot.com/|||use 'Exec dbo.usp_glock'
the transaction will not be rolled back when lock happens.
thanks a lot.
--
wq352
"ML" wrote:
> Try setting the lock-timeout to 0 (indefinite) or increase it as appropria
te.
> SET LOCK_TIMEOUT 0;
> http://msdn.microsoft.com/library/d...>
_7a_5n78.asp
> http://msdn.microsoft.com/library/d...>
set_1yr8.asp
> ML
> --
> http://milambda.blogspot.com/|||So, what measures have you taken to solve the problem? Have you increased th
e
timeout or turned it off?
ML
http://milambda.blogspot.com/|||On Tue, 16 May 2006 09:04:02 -0700, wq352 wrote:
(snip)
>this procedure terminated when execute 'OPEN
>cursor_Sql FETCH NEXT FROM cursor_Sql'.
Hi wq352,
Since you didn't post an error message, I tried to reproduce it. I had
to change some table names to make it run. After that, I didn't get any
error message - instead, I got into an endless loop here:
> WHILE @.@.FETCH_STATUS = 0
> BEGIN
> print @.intID
> End
Generally, a loop that starts wiith WHILE @.@.FETCH_STATUS = 0 should
include at least one FETCH statement. This loop holds only a PRINT
statement, which will never change the value of @.@.FETCH_STATUS.
However, I also fail to see why you use a looop at all - considering
that you include a TOP 1 clause, you'll get just one row annyway and
there's no need to use a cursor at all.
Err_Handle:
DECLARE @.intID INT
--/*
SET @.intID = (SELECT TOP 1 id FROM gcurtbl)
PRINT @.intID
--*/
insert into gerrtbl (errdesc)
values('Lock time out error.')
Another important note - using TOP without ORDER BY means that you're
getting just one row, but it's unpredictable what row it will be. Are
you sure that that's what yoou want?
Hugo Kornelis, SQL Server MVP
Showing posts with label error. Show all posts
Showing posts with label error. Show all posts
Friday, March 30, 2012
Lock_timeout
Hello,
in EM (SQL7) i got the following errormessage, when i click on
current activity:
"Error: 1222 lock request time out period exceeded"
I run the sp_who /sp_who2, but nothing is blocked.
I set the lock_timeout th 5000, but it does not help.
Does anyone have an idea ?
best regards
Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.
also try to run a performance monitor to the db server.
check for cpu usage and latches.
if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...
this can mean that the server is small, the app is poor, problem with your net h/w...
but this is some place to start with...
Cheers.|||Thanks for help Arounis,
network traffic is not possible, because i can log on to the server an got
the same error message.
For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.
Checking avg.latch time and total latch time results:
520 constant for avg latch time
0 - 16 for total latch time.
You don't think that the problem occours about a "user - transaction" ?
Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses
select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null
You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.
One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.
This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.
Maybe a restart will help ?
Thnx
in EM (SQL7) i got the following errormessage, when i click on
current activity:
"Error: 1222 lock request time out period exceeded"
I run the sp_who /sp_who2, but nothing is blocked.
I set the lock_timeout th 5000, but it does not help.
Does anyone have an idea ?
best regards
Martinnetwork traffic is an idea
try to change for instance from tcp/ip to netbeui, or vice versa as the protocol you use to connect to the db server
see what happens.
also try to run a performance monitor to the db server.
check for cpu usage and latches.
if avg.latch wait time is high, ie. over 1000 and total latch wait time is also high i.e. over 100 for instance, then you probably experience sql server locks (watch for lock timeout /second)...
this can mean that the server is small, the app is poor, problem with your net h/w...
but this is some place to start with...
Cheers.|||Thanks for help Arounis,
network traffic is not possible, because i can log on to the server an got
the same error message.
For remember: I only get the error message, when i click on current activity in EM. No User told me that a problem still exits.
Checking avg.latch time and total latch time results:
520 constant for avg latch time
0 - 16 for total latch time.
You don't think that the problem occours about a "user - transaction" ?
Martin|||Try seeing if there is a locked resource which doesn't have a corresponding entry in sysprocesses
select *
from master..syslocks a
left join master..sysprocesses b on a.spid = b.spid
where b.spid is null
You shouldn't get any results.
Don't know what to tell you if you get any results.|||Thanks Wilso_s, but i get no results.
One more detail i detected, is that i can't access the
tempdb in EM. EM hangs up.
This short KB artikel Q308518 describes problems with
tempdb, but there is no workaround.
Maybe a restart will help ?
Thnx
Wednesday, March 28, 2012
lock timeout
my SQL studio view is running into timeout error block. how do i insert the
SET LOCK_TIMEOUT -1GO
in the SQL statement of the view to allow this to run to completion? an example of the SQL view is;
SELECT TOP (100) PERCENT dbo.Entry_Race.E_TDR, dbo.Entry_Race.E_Surface, dbo.Entry_Race.E_Race_Class_Codes,
FROM dbo.Entry_Race INNER JOIN
dbo.Entry_Horse ON dbo.Entry_Race.E_TDR = dbo.Entry_Horse.E_TDR
WHERE (CONVERT(varchar(07), dbo.Entry_Horse.E_Date) BETWEEN CONVERT(varchar(07), GETDATE(), 0) AND CONVERT(varchar(07), GETDATE() + 1, 0))
ORDER BY dbo.Entry_Race.E_TDR, dbo.Entry_Horse.E_Horse, dbo.Entry_Horse.E_Traininer
Do you really need to wait indefinitelly? It is not a very normal situation to have the client waiting tens of seconds for a response - why not using a more optimistic locking mechanism?
You shouldn't user the convert funcion to compare the dates, but using dateadd () over the getdate() functions and compare directly - as it is, any indexes over dbo.Entry_Horse.E_Date will not be used by SQL...
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.
Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.
Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 12
0
> seconds. But the above query fails instantly, so it's not even waiting th
e
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 12
0
> seconds. But the above query fails instantly, so it's not even waiting th
e
> 120 seconds. What gives? Any tips would be much appreciated.
>
Lock Timeout
I am getting this error on a 2000 server:
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
"Server: Msg 1222, Level 16, State 54, Line 15
Lock request time out period exceeded."
when I run the following query
"SET ROWCOUNT 10000
declare @.counter bigint
-- Also try 5000, 10000 etc
set @.counter = 0
WHILE 1 = 1
BEGIN
set @.counter = @.counter + 1
print '@.counter = ' + cast(@.counter as varchar(10))
delete from dbname..tabname
where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
IF @.@.ROWCOUNT = 0
BREAK
END
SET ROWCOUNT 0"
I checked in my Query Analyzer options and I've got lock timeout set to 120
seconds. But the above query fails instantly, so it's not even waiting the
120 seconds. What gives? Any tips would be much appreciated.Nevermind. I figured it out.
"CLM" wrote:
> I am getting this error on a 2000 server:
> "Server: Msg 1222, Level 16, State 54, Line 15
> Lock request time out period exceeded."
> when I run the following query
> "SET ROWCOUNT 10000
> declare @.counter bigint
> -- Also try 5000, 10000 etc
> set @.counter = 0
> WHILE 1 = 1
> BEGIN
> set @.counter = @.counter + 1
> print '@.counter = ' + cast(@.counter as varchar(10))
> delete from dbname..tabname
> where process_dt = CONVERT(SMALLDATETIME,'10/13/2005')
> IF @.@.ROWCOUNT = 0
> BREAK
> END
> SET ROWCOUNT 0"
> I checked in my Query Analyzer options and I've got lock timeout set to 120
> seconds. But the above query fails instantly, so it's not even waiting the
> 120 seconds. What gives? Any tips would be much appreciated.
>
Lock Resource
I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?
It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?
It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
Lock Resource
I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
Lock Resource
I've got a Sql Server (2000) that is struggling with disk i/o issues
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
(particularly write). I noticed this in my error log:
The SQL Server cannot obtain a LOCK resource at this time. Rerun your
statement when there are fewer active users or ask the system administrator
to check the SQL Server lock and memory configuration..
Isn't this usually RAM related? Or could it be related somehow to the disk
i/o issues as well?It could be memory but it's hard to say without more info.
You should also check your lock configurations with:
sp_configure 'show advanced options',1
Reconfigure
exec sp_configure 'locks'
to see if you have a value of 0.
You can also execute sp_lock to monitor the number of locks,
lock resources on the server.
-Sue
On Mon, 7 Nov 2005 12:58:08 -0800, CLM
<CLM@.discussions.microsoft.com> wrote:
>I've got a Sql Server (2000) that is struggling with disk i/o issues
>(particularly write). I noticed this in my error log:
>The SQL Server cannot obtain a LOCK resource at this time. Rerun your
>statement when there are fewer active users or ask the system administrator
>to check the SQL Server lock and memory configuration..
>Isn't this usually RAM related? Or could it be related somehow to the disk
>i/o issues as well?
Monday, March 26, 2012
Lock on system table
Hi all,
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>
One of my stored procedure is creating around 7 #tables.
This procedure comes out after 5 mins with error
1222 'Lock request time-out period exceeded'.
My @.@.lock_timeout is -1.
Analysis showed that this stored procedures holds numerous
shared and exclusive locks on sysobjects, sysindexes and
syscolumns on tempdb. It blocks all other processes and
nobody can do anything.
Can anybody suggests why this could be happening and way
out?
Thanks in advance
Himanshu JaniHi Himanshu ,
If your stored procedure uses SELECT ..INTO to create and populate the
temporary tables this can cause locking problems as it will take out a lock
on the system tables to create the temporary table, but will hold this lock
for the duration of the insert. You can solve this problem by creating the
temporary table explicitly with either CREATE TABLE or with SELECT ..
INTO... WHERE 1=0. You then have to insert the data into the temporary table
with a normal insert. If the table creation happens inside a transaction the
locks will be taken for the duration of the transaction, so it makes sense
to create the temporary tables outside a long running transaction. If you
use SQL Server 2000 you can also use table variables instead of temporary
tables, table variables do not participate in transactions, and so you won't
have any locks on the system tables in tempdb.
--
Jacco Schalkwijk
SQL Server MVP
"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
> Hi all,
> One of my stored procedure is creating around 7 #tables.
> This procedure comes out after 5 mins with error
> 1222 'Lock request time-out period exceeded'.
> My @.@.lock_timeout is -1.
> Analysis showed that this stored procedures holds numerous
> shared and exclusive locks on sysobjects, sysindexes and
> syscolumns on tempdb. It blocks all other processes and
> nobody can do anything.
> Can anybody suggests why this could be happening and way
> out?
> Thanks in advance
> Himanshu Jani|||It seems to me that SELECT INTO is just a single command
that combines both the CREATE TABLE and INSERT statements
(with the benefit of minimumally logging the INSERT).
This means that the X-lock(s) on the system tables in
tempdb is held just for the duration of the CREATE and not
for the duration of the entire SELECT INTO statement.
On SQL Server 2000, SP3, I just ran 'SELECT INTO' into a
#temp table from a 12 million record table and was able to
simultaneously run and complete a second SELECT INTO into
another #temp table while the first SELECT INTO was still
running.
Thoughts?
Thanks, -- Brian
>--Original Message--
>Hi Himanshu ,
>If your stored procedure uses SELECT ..INTO to create and
populate the
>temporary tables this can cause locking problems as it
will take out a lock
>on the system tables to create the temporary table, but
will hold this lock
>for the duration of the insert. You can solve this
problem by creating the
>temporary table explicitly with either CREATE TABLE or
with SELECT ..
>INTO... WHERE 1=0. You then have to insert the data into
the temporary table
>with a normal insert. If the table creation happens
inside a transaction the
>locks will be taken for the duration of the transaction,
so it makes sense
>to create the temporary tables outside a long running
transaction. If you
>use SQL Server 2000 you can also use table variables
instead of temporary
>tables, table variables do not participate in
transactions, and so you won't
>have any locks on the system tables in tempdb.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Himanshu Jani" <himanshu@.ocwen.co.in> wrote in message
>news:0a0301c38c94$a10602c0$a401280a@.phx.gbl...
>> Hi all,
>> One of my stored procedure is creating around 7 #tables.
>> This procedure comes out after 5 mins with error
>> 1222 'Lock request time-out period exceeded'.
>> My @.@.lock_timeout is -1.
>> Analysis showed that this stored procedures holds
numerous
>> shared and exclusive locks on sysobjects, sysindexes and
>> syscolumns on tempdb. It blocks all other processes and
>> nobody can do anything.
>> Can anybody suggests why this could be happening and way
>> out?
>> Thanks in advance
>> Himanshu Jani
>
>.
>
Lock manager out of space
Hi,
I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.
I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:
The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.
I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.
I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.
Cheers
Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspxI can see the following setting:
default lock escalation-or-ssce
The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.
It's a far shot, but maybe decreasing this number to 10 or lower will help you.
What does your connection string look like, anyway?
|||
Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:
Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;
I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.
|||Received this information through a Device MVP and thought it might be useful for others:
"You hit this issue:
1) If a single transaction is dealing with more than 1 GB of pages.
2) If you are using v3.0 or v3.1
We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."
Also this:
" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"
Lock manager out of space
Hi,
I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.
I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:
The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.
I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.
I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.
Cheers
Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspxI can see the following setting:
default lock escalation-or-ssce
The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.
It's a far shot, but maybe decreasing this number to 10 or lower will help you.
What does your connection string look like, anyway?
|||
Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:
Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;
I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.
|||Received this information through a Device MVP and thought it might be useful for others:
"You hit this issue:
1) If a single transaction is dealing with more than 1 GB of pages.
2) If you are using v3.0 or v3.1
We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."
Also this:
" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"
Lock manager out of space
Hi,
I'm getting an error which I'm pretty sure I can avoid for now, but I'd like to understand the underlying issue.
I'm have SQL CE 3.1 on Win XP and am replicating a reasonably large set of data with SQL Server 2005 using merge replication. The initial replication works fine, and brings the SQL CE database to around 800MB. Subsequent delta syncs are also fine. However, if I re-initialise the subscription, it chugs away for a while, grows the local database to just over 1Gb then errors with the following message:
The lock manager has run out of space for additional locks. This can be caused by large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space.
I can avoid this issue by either deleteing the .sdf file and recreating the subscription from scratch, or by splitting the publication into smaller sets, and re-intialising each one seperately. Obviously SQL CE uses some sort of temporary or "lock" space to manage the re-initialisation. Is, as the error message suggests, there no way to increase this? How much space is there - ie what is the threshold overwhich I need to split up the update operations into multiple steps.
I'm assuming this is a SQL CE limitation, hence posting this here rather than under the replication forum.
Cheers
Looking at http://msdn2.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.connectionstring.aspxI can see the following setting:
default lock escalation-or-ssce
The number of locks a transaction will acquire before attempting escalation from row to page, or from page to table. If not specified, the default value is 100.
It's a far shot, but maybe decreasing this number to 10 or lower will help you.
What does your connection string look like, anyway?
|||
Thanks for the suggestion. From our application we don't actually set this value, so I expect it's using the default 100. Our connection string is pretty minimal, and looks like this:
Data Source='filename.sdf';Max Database Size = 2048; Max Buffer Size = 1024;
I've tried testing different values in the default lock escalation setting using the subscription wizard in sql management studio which seems to enforce a minimum value of 50. It doesn't appear to make any difference to the result - I still get the lock manager running out of space.
|||Received this information through a Device MVP and thought it might be useful for others:
"You hit this issue:
1) If a single transaction is dealing with more than 1 GB of pages.
2) If you are using v3.0 or v3.1
We have extended the limit in v3.5 (available in v3.5 Beta2), and you can now have a one bulk transaction which is updating more than 1 GB of pages."
Also this:
" The old limit is 2^18-1 lock references, now it is 2^32-1 lock references"
sqlLock Manager does a dead lock search?
I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec
0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec
0xBBA39370) Value:0x800ee67c0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec
0xBBA39370) Value:0x800ee67c0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jl
Sounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec
0xBBA39370) Value:0x800ee67c> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec
0xBBA39370) Value:0x800ee67c> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl
|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>
Lock Manager does a dead lock search?
I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
--
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
--
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
> >I see the following in the SQL (error) log and am curious as to why it
> >shows
> > up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> > 2000
> > SP4. This server uses the Intel EMT processor. We have two other
> > similarly
> > configured servers and I don't see these mesages on those servers (same
> > databases, etc.).
> >
> >
> > Starting deadlock search 34450
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> > ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> > SPID:73 ECID:0 Ec:(0xBBA39370) Value:0x800ee67c
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4
> >
> >
> > 0
> > 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> > was not found.
> >
> > --
> > jl
>
>
Lock Manager does a dead lock search?
I see the following in the SQL (error) log and am curious as to why it shows
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec0xBBA39370) Value:0x800ee67c
> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>
up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server 2000
SP4. This server uses the Intel EMT processor. We have two other similarly
configured servers and I don't see these mesages on those servers (same
databases, etc.).
Starting deadlock search 34450
0
2005-10-12 09:49:51.31 spid4 Target Resource Owner:
0
2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
ECID:0 Ec
0xBBA39370) Value:0x800ee67c0
2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
SPID:73 ECID:0 Ec
0xBBA39370) Value:0x800ee67c0
2005-10-12 09:49:51.31 spid4
0
2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
was not found.
jlSounds like a trace flag has been enabled for that SQL instance.
Perhaps -T1205. Look in the startup parameters for SQL Server or use DBCC
TRACESTATUS(-1) to see all currently enabled trace flags.
"John L" <JohnL@.discussions.microsoft.com> wrote in message
news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>I see the following in the SQL (error) log and am curious as to why it
>shows
> up (repeatedly). Server OS is Windows 2003 x64 Enterprise, SQL Server
> 2000
> SP4. This server uses the Intel EMT processor. We have two other
> similarly
> configured servers and I don't see these mesages on those servers (same
> databases, etc.).
>
> Starting deadlock search 34450
> 0
> 2005-10-12 09:49:51.31 spid4 Target Resource Owner:
>
> 0
> 2005-10-12 09:49:51.31 spid4 ResType:ExchangeId Stype:'AND' SPID:73
> ECID:0 Ec
0xBBA39370) Value:0x800ee67c> 0
> 2005-10-12 09:49:51.31 spid4 Node:1 ResType:ExchangeId Stype:'AND'
> SPID:73 ECID:0 Ec
0xBBA39370) Value:0x800ee67c> 0
> 2005-10-12 09:49:51.31 spid4
>
> 0
> 2005-10-12 09:49:51.31 spid4 End deadlock search 34450 ... a deadlock
> was not found.
> --
> jl|||Lori,
We don't have the 1205 flag enabled.
jl
"Lori Clark" wrote:
> Sounds like a trace flag has been enabled for that SQL instance.
> Perhaps -T1205. Look in the startup parameters for SQL Server or use DBC
C
> TRACESTATUS(-1) to see all currently enabled trace flags.
> "John L" <JohnL@.discussions.microsoft.com> wrote in message
> news:A460B54F-CE69-4601-82EC-4EB2806FDF2C@.microsoft.com...
>
>
Friday, March 23, 2012
Lock Issue
I've got a 2000 server (2G usable RAM) where we just
installed SP2. Immediately after installation I began
getting the below error:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration.
Error: 1204, Severity: 19, State: 1
I've got the default configuration setting for locks of 0
and nothing has changed on this server that I know except
for SP2. Furthermore, I've been monitoring the total
number of locks at any given time and I'm not seeing
anything that should cause problems. The most locks I've
seen is about 40,000.
The last time we got this error msg in the error log, I
had two index defrag jobs going and there was bulk load
and/or delete job running.
Is this simply memory related? Is there anything with SP2
that might cause this (especially if it uses extra memory
for index defrags)?
Any insight would be much appreciated?!Hi Curt.
What is your configuration for locks? Run this to find out:
exec sp_configure 'locks'
Perhaps it got set to 40000 maximum during SP2 installation somehow?
Regards,
Greg Linwood
SQL Server MVP
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> I've got a 2000 server (2G usable RAM) where we just
> installed SP2. Immediately after installation I began
> getting the below error:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration.
> Error: 1204, Severity: 19, State: 1
> I've got the default configuration setting for locks of 0
> and nothing has changed on this server that I know except
> for SP2. Furthermore, I've been monitoring the total
> number of locks at any given time and I'm not seeing
> anything that should cause problems. The most locks I've
> seen is about 40,000.
> The last time we got this error msg in the error log, I
> had two index defrag jobs going and there was bulk load
> and/or delete job running.
> Is this simply memory related? Is there anything with SP2
> that might cause this (especially if it uses extra memory
> for index defrags)?
> Any insight would be much appreciated?!
>|||It is set to 0 for run and config value (which I am sure
it was pre-SP2 as well).
>--Original Message--
>Hi Curt.
>What is your configuration for locks? Run this to find
out:
>exec sp_configure 'locks'
>Perhaps it got set to 40000 maximum during SP2
installation somehow?
>Regards,
>Greg Linwood
>SQL Server MVP
>"CurtM" <cndmoyer@.hotmail.com> wrote in message
>news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
>> I've got a 2000 server (2G usable RAM) where we just
>> installed SP2. Immediately after installation I began
>> getting the below error:
>> The SQL Server cannot obtain a LOCK resource at this
time.
>> Rerun your statement when there are fewer active users
or
>> ask the system administrator to check the SQL Server
lock
>> and memory configuration.
>> Error: 1204, Severity: 19, State: 1
>> I've got the default configuration setting for locks of
0
>> and nothing has changed on this server that I know
except
>> for SP2. Furthermore, I've been monitoring the total
>> number of locks at any given time and I'm not seeing
>> anything that should cause problems. The most locks
I've
>> seen is about 40,000.
>> The last time we got this error msg in the error log, I
>> had two index defrag jobs going and there was bulk load
>> and/or delete job running.
>> Is this simply memory related? Is there anything with
SP2
>> that might cause this (especially if it uses extra
memory
>> for index defrags)?
>> Any insight would be much appreciated?!
>
>.
>|||At this point it is just memory issue and you may have a lot of low
granularity locks such as RID or Key. You may want to escalate if possible
using hints for the problematic query
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:08c201c391c9$77b35000$a301280a@.phx.gbl...
> It is set to 0 for run and config value (which I am sure
> it was pre-SP2 as well).
> >--Original Message--
> >Hi Curt.
> >
> >What is your configuration for locks? Run this to find
> out:
> >
> >exec sp_configure 'locks'
> >
> >Perhaps it got set to 40000 maximum during SP2
> installation somehow?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"CurtM" <cndmoyer@.hotmail.com> wrote in message
> >news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> >> I've got a 2000 server (2G usable RAM) where we just
> >> installed SP2. Immediately after installation I began
> >> getting the below error:
> >>
> >> The SQL Server cannot obtain a LOCK resource at this
> time.
> >> Rerun your statement when there are fewer active users
> or
> >> ask the system administrator to check the SQL Server
> lock
> >> and memory configuration.
> >>
> >> Error: 1204, Severity: 19, State: 1
> >>
> >> I've got the default configuration setting for locks of
> 0
> >> and nothing has changed on this server that I know
> except
> >> for SP2. Furthermore, I've been monitoring the total
> >> number of locks at any given time and I'm not seeing
> >> anything that should cause problems. The most locks
> I've
> >> seen is about 40,000.
> >>
> >> The last time we got this error msg in the error log, I
> >> had two index defrag jobs going and there was bulk load
> >> and/or delete job running.
> >>
> >> Is this simply memory related? Is there anything with
> SP2
> >> that might cause this (especially if it uses extra
> memory
> >> for index defrags)?
> >>
> >> Any insight would be much appreciated?!
> >>
> >
> >
> >.
> >
installed SP2. Immediately after installation I began
getting the below error:
The SQL Server cannot obtain a LOCK resource at this time.
Rerun your statement when there are fewer active users or
ask the system administrator to check the SQL Server lock
and memory configuration.
Error: 1204, Severity: 19, State: 1
I've got the default configuration setting for locks of 0
and nothing has changed on this server that I know except
for SP2. Furthermore, I've been monitoring the total
number of locks at any given time and I'm not seeing
anything that should cause problems. The most locks I've
seen is about 40,000.
The last time we got this error msg in the error log, I
had two index defrag jobs going and there was bulk load
and/or delete job running.
Is this simply memory related? Is there anything with SP2
that might cause this (especially if it uses extra memory
for index defrags)?
Any insight would be much appreciated?!Hi Curt.
What is your configuration for locks? Run this to find out:
exec sp_configure 'locks'
Perhaps it got set to 40000 maximum during SP2 installation somehow?
Regards,
Greg Linwood
SQL Server MVP
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> I've got a 2000 server (2G usable RAM) where we just
> installed SP2. Immediately after installation I began
> getting the below error:
> The SQL Server cannot obtain a LOCK resource at this time.
> Rerun your statement when there are fewer active users or
> ask the system administrator to check the SQL Server lock
> and memory configuration.
> Error: 1204, Severity: 19, State: 1
> I've got the default configuration setting for locks of 0
> and nothing has changed on this server that I know except
> for SP2. Furthermore, I've been monitoring the total
> number of locks at any given time and I'm not seeing
> anything that should cause problems. The most locks I've
> seen is about 40,000.
> The last time we got this error msg in the error log, I
> had two index defrag jobs going and there was bulk load
> and/or delete job running.
> Is this simply memory related? Is there anything with SP2
> that might cause this (especially if it uses extra memory
> for index defrags)?
> Any insight would be much appreciated?!
>|||It is set to 0 for run and config value (which I am sure
it was pre-SP2 as well).
>--Original Message--
>Hi Curt.
>What is your configuration for locks? Run this to find
out:
>exec sp_configure 'locks'
>Perhaps it got set to 40000 maximum during SP2
installation somehow?
>Regards,
>Greg Linwood
>SQL Server MVP
>"CurtM" <cndmoyer@.hotmail.com> wrote in message
>news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
>> I've got a 2000 server (2G usable RAM) where we just
>> installed SP2. Immediately after installation I began
>> getting the below error:
>> The SQL Server cannot obtain a LOCK resource at this
time.
>> Rerun your statement when there are fewer active users
or
>> ask the system administrator to check the SQL Server
lock
>> and memory configuration.
>> Error: 1204, Severity: 19, State: 1
>> I've got the default configuration setting for locks of
0
>> and nothing has changed on this server that I know
except
>> for SP2. Furthermore, I've been monitoring the total
>> number of locks at any given time and I'm not seeing
>> anything that should cause problems. The most locks
I've
>> seen is about 40,000.
>> The last time we got this error msg in the error log, I
>> had two index defrag jobs going and there was bulk load
>> and/or delete job running.
>> Is this simply memory related? Is there anything with
SP2
>> that might cause this (especially if it uses extra
memory
>> for index defrags)?
>> Any insight would be much appreciated?!
>
>.
>|||At this point it is just memory issue and you may have a lot of low
granularity locks such as RID or Key. You may want to escalate if possible
using hints for the problematic query
"CurtM" <cndmoyer@.hotmail.com> wrote in message
news:08c201c391c9$77b35000$a301280a@.phx.gbl...
> It is set to 0 for run and config value (which I am sure
> it was pre-SP2 as well).
> >--Original Message--
> >Hi Curt.
> >
> >What is your configuration for locks? Run this to find
> out:
> >
> >exec sp_configure 'locks'
> >
> >Perhaps it got set to 40000 maximum during SP2
> installation somehow?
> >
> >Regards,
> >Greg Linwood
> >SQL Server MVP
> >
> >"CurtM" <cndmoyer@.hotmail.com> wrote in message
> >news:127e01c391b1$29933ce0$a001280a@.phx.gbl...
> >> I've got a 2000 server (2G usable RAM) where we just
> >> installed SP2. Immediately after installation I began
> >> getting the below error:
> >>
> >> The SQL Server cannot obtain a LOCK resource at this
> time.
> >> Rerun your statement when there are fewer active users
> or
> >> ask the system administrator to check the SQL Server
> lock
> >> and memory configuration.
> >>
> >> Error: 1204, Severity: 19, State: 1
> >>
> >> I've got the default configuration setting for locks of
> 0
> >> and nothing has changed on this server that I know
> except
> >> for SP2. Furthermore, I've been monitoring the total
> >> number of locks at any given time and I'm not seeing
> >> anything that should cause problems. The most locks
> I've
> >> seen is about 40,000.
> >>
> >> The last time we got this error msg in the error log, I
> >> had two index defrag jobs going and there was bulk load
> >> and/or delete job running.
> >>
> >> Is this simply memory related? Is there anything with
> SP2
> >> that might cause this (especially if it uses extra
> memory
> >> for index defrags)?
> >>
> >> Any insight would be much appreciated?!
> >>
> >
> >
> >.
> >
Wednesday, March 21, 2012
Location of error log?
Hopefully this will be a simple question.
Where is the error log located? I am looking in
program files\microsoft sql server\mssql\reporting services\LogFile
at a file called ReportServer__09_27_2005_11_38_23.log inside that file is a
message:
e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Where can I fine the error log it refers to? Everything has been installed
to default locations.
ThanksYou are looking at the error log. The log contains the text that is
displayed to users so it is a little misleading when seeing it in the log
itself. Can you include the entire call stack?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> Hopefully this will be a simple question.
> Where is the error log located? I am looking in
> program files\microsoft sql server\mssql\reporting services\LogFile
> at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> a
> message:
> e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more
> details., ;
> Where can I fine the error log it refers to? Everything has been
> installed
> to default locations.
> Thanks|||Thanks for that. I am getting different errors in the log at different times
from the same problem. The problem in detailed in my post "Login Prompt
after ~3min timeout".
I am reguarly getting out memory errors, but I have looked at the machine
and it is only using ~1/3 of available memory and CPU so I think that is a
red herring.
The report runs fine if the data allows it to complete before 2.5 - 3mins -
this seems to be 2.5mins on the test server I am using and 3mins on the live
environment.
The following messages are the only consistant ones I am seeing in the log:
w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
RunningJobContext.Cancel; failed
w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
Any thoughts, pointers or suggestions will be gratefully received.
"Daniel Reib [MSFT]" wrote:
> You are looking at the error log. The log contains the text that is
> displayed to users so it is a little misleading when seeing it in the log
> itself. Can you include the entire call stack?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> > Hopefully this will be a simple question.
> >
> > Where is the error log located? I am looking in
> >
> > program files\microsoft sql server\mssql\reporting services\LogFile
> >
> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> > a
> > message:
> >
> > e ERROR: Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> > An internal error occurred on the report server. See the error log for
> > more
> > details., ;
> >
> > Where can I fine the error log it refers to? Everything has been
> > installed
> > to default locations.
> >
> > Thanks
>
>|||The orphaned requests means that the client is disconnected from the server.
In this case RS will stop processing the job.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:E2F9A42E-C173-4B35-8BA3-749239454E90@.microsoft.com...
> Thanks for that. I am getting different errors in the log at different
> times
> from the same problem. The problem in detailed in my post "Login Prompt
> after ~3min timeout".
> I am reguarly getting out memory errors, but I have looked at the machine
> and it is only using ~1/3 of available memory and CPU so I think that is a
> red herring.
> The report runs fine if the data allows it to complete before 2.5 -
> 3mins -
> this seems to be 2.5mins on the test server I am using and 3mins on the
> live
> environment.
> The following messages are the only consistant ones I am seeing in the
> log:
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
> RunningJobContext.Cancel; failed
> w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> Any thoughts, pointers or suggestions will be gratefully received.
> "Daniel Reib [MSFT]" wrote:
>> You are looking at the error log. The log contains the text that is
>> displayed to users so it is a little misleading when seeing it in the log
>> itself. Can you include the entire call stack?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
>> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
>> > Hopefully this will be a simple question.
>> >
>> > Where is the error log located? I am looking in
>> >
>> > program files\microsoft sql server\mssql\reporting services\LogFile
>> >
>> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file
>> > is
>> > a
>> > message:
>> >
>> > e ERROR: Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
>> > An internal error occurred on the report server. See the error log for
>> > more
>> > details., ;
>> >
>> > Where can I fine the error log it refers to? Everything has been
>> > installed
>> > to default locations.
>> >
>> > Thanks
>>
Where is the error log located? I am looking in
program files\microsoft sql server\mssql\reporting services\LogFile
at a file called ReportServer__09_27_2005_11_38_23.log inside that file is a
message:
e ERROR: Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
An internal error occurred on the report server. See the error log for more
details., ;
Where can I fine the error log it refers to? Everything has been installed
to default locations.
ThanksYou are looking at the error log. The log contains the text that is
displayed to users so it is a little misleading when seeing it in the log
itself. Can you include the entire call stack?
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> Hopefully this will be a simple question.
> Where is the error log located? I am looking in
> program files\microsoft sql server\mssql\reporting services\LogFile
> at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> a
> message:
> e ERROR: Throwing
> Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> An internal error occurred on the report server. See the error log for
> more
> details., ;
> Where can I fine the error log it refers to? Everything has been
> installed
> to default locations.
> Thanks|||Thanks for that. I am getting different errors in the log at different times
from the same problem. The problem in detailed in my post "Login Prompt
after ~3min timeout".
I am reguarly getting out memory errors, but I have looked at the machine
and it is only using ~1/3 of available memory and CPU so I think that is a
red herring.
The report runs fine if the data allows it to complete before 2.5 - 3mins -
this seems to be 2.5mins on the test server I am using and 3mins on the live
environment.
The following messages are the only consistant ones I am seeing in the log:
w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
RunningJobContext.Cancel; failed
w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
RunningJobContext.IsClientConnected; found orphaned request
Any thoughts, pointers or suggestions will be gratefully received.
"Daniel Reib [MSFT]" wrote:
> You are looking at the error log. The log contains the text that is
> displayed to users so it is a little misleading when seeing it in the log
> itself. Can you include the entire call stack?
> --
> -Daniel
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
> > Hopefully this will be a simple question.
> >
> > Where is the error log located? I am looking in
> >
> > program files\microsoft sql server\mssql\reporting services\LogFile
> >
> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file is
> > a
> > message:
> >
> > e ERROR: Throwing
> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
> > An internal error occurred on the report server. See the error log for
> > more
> > details., ;
> >
> > Where can I fine the error log it refers to? Everything has been
> > installed
> > to default locations.
> >
> > Thanks
>
>|||The orphaned requests means that the client is disconnected from the server.
In this case RS will stop processing the job.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
news:E2F9A42E-C173-4B35-8BA3-749239454E90@.microsoft.com...
> Thanks for that. I am getting different errors in the log at different
> times
> from the same problem. The problem in detailed in my post "Login Prompt
> after ~3min timeout".
> I am reguarly getting out memory errors, but I have looked at the machine
> and it is only using ~1/3 of available memory and CPU so I think that is a
> red herring.
> The report runs fine if the data allows it to complete before 2.5 -
> 3mins -
> this seems to be 2.5mins on the test server I am using and 3mins on the
> live
> environment.
> The following messages are the only consistant ones I am seeing in the
> log:
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!1568!28/09/2005-09:46:45:: w WARN:
> RunningJobContext.Cancel; failed
> w3wp!runningjobs!1568!28/09/2005-09:47:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:48:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:49:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:50:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!fe4!28/09/2005-09:51:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> w3wp!runningjobs!15f0!28/09/2005-09:52:45:: i INFO:
> RunningJobContext.IsClientConnected; found orphaned request
> Any thoughts, pointers or suggestions will be gratefully received.
> "Daniel Reib [MSFT]" wrote:
>> You are looking at the error log. The log contains the text that is
>> displayed to users so it is a little misleading when seeing it in the log
>> itself. Can you include the entire call stack?
>> --
>> -Daniel
>> This posting is provided "AS IS" with no warranties, and confers no
>> rights.
>>
>> "Nicola Jones" <NicolaJones@.discussions.microsoft.com> wrote in message
>> news:83BFFE98-BBF5-4CD6-9D71-CAC85D4E3228@.microsoft.com...
>> > Hopefully this will be a simple question.
>> >
>> > Where is the error log located? I am looking in
>> >
>> > program files\microsoft sql server\mssql\reporting services\LogFile
>> >
>> > at a file called ReportServer__09_27_2005_11_38_23.log inside that file
>> > is
>> > a
>> > message:
>> >
>> > e ERROR: Throwing
>> > Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException:
>> > An internal error occurred on the report server. See the error log for
>> > more
>> > details., ;
>> >
>> > Where can I fine the error log it refers to? Everything has been
>> > installed
>> > to default locations.
>> >
>> > Thanks
>>
Monday, March 19, 2012
Locating S
I recently uploaded my site to the internet. But now, when I try to to log in, I receive the following error message:
An error has occurred while establishing a connection tothe server. When connecting to SQL Server 2005, this failure may becaused by the fact that under the default settings SQL Server does notallow remote connections. (provider: SQL Network Interfaces, error: 26- Error Locating Server/Instance Specified)
How do I fix this?
I assumed you mean you get this error when you uploaded your site to your hosting service.
This error usually means that your ASP.NET application is trying to connect to a SQL Express database. In general, very few webhosting provider support SQL Express because it is not intended for production use. Does your host offer SQL 2005? If so, migrate your data to the SQL 2k5 database and update your application's connection string in the web.config (or in the code itself) to point to the SQL 2k5 database.
Hope this helps.
|||You're right. Thanks for the help. Fixed it now.Locate Database Object Given The Page
Hi Folks,
Getting the following error after a DBCC CHECKDB
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:15010) in database ID 9 is allocated in the SGAM (3:3) and PFS (3:8088), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Trying to located the object that the allocation has gone to pot over.
Any quick way to resolve a datbase object using a page ID?
Thank In Advancewhoops. not enough coffee.|||"database ID 9" relates to the the entry in master.dbo.sysdatabases
I need to know what object within this database the page is allocated to.
Thanks
Getting the following error after a DBCC CHECKDB
Server: Msg 8906, Level 16, State 1, Line 1
Page (3:15010) in database ID 9 is allocated in the SGAM (3:3) and PFS (3:8088), but was not allocated in any IAM. PFS flags 'MIXED_EXT ALLOCATED 0_PCT_FULL'.
Trying to located the object that the allocation has gone to pot over.
Any quick way to resolve a datbase object using a page ID?
Thank In Advancewhoops. not enough coffee.|||"database ID 9" relates to the the entry in master.dbo.sysdatabases
I need to know what object within this database the page is allocated to.
Thanks
Subscribe to:
Posts (Atom)