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 transaction. Show all posts
Showing posts with label transaction. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
lock timeouts
In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...
This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>
|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>
|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>
|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>
|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...
This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>
|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>
|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>
|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>
|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
David Gugick
Quest Software
www.imceda.com
www.quest.com
sql
lock timeouts
In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
David Gugick
Quest Software
www.imceda.com
www.quest.com
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
David Gugick
Quest Software
www.imceda.com
www.quest.com
lock timeouts
In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
>> event. I am assuming this means that some transaction is blocked, however
>> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
>> timeout. So why am I detecting this event?
>> Any help much appreciated...
>|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>> Are you talking about spinlocks here? Isn't it the case that spinlocks
>> never timeout? Anyway, during these timeouts, SQL Profiler does report an
>> objectID of 0 most of the time, but occaisionally the object ID is on one
>> of the user tables - do you still think we shouldn't be concerned with
>> it?
>> --
>> Best regards
>> Mark Baldwin
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>>
>|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
event. I am assuming this means that some transaction is blocked, however
the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never timeout.
So why am I detecting this event?
Any help much appreciated...This also includes internal lightweight locks that timeout and are not the
same as a real lock timeout. It's normal and not to be concerned with.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
> event. I am assuming this means that some transaction is blocked, however
> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
> timeout. So why am I detecting this event?
> Any help much appreciated...
>|||Are you talking about spinlocks here? Isn't it the case that spinlocks never
timeout? Anyway, during these timeouts, SQL Profiler does report an objectID
of 0 most of the time, but occaisionally the object ID is on one of the user
tables - do you still think we shouldn't be concerned with it?
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
> This also includes internal lightweight locks that timeout and are not the
> same as a real lock timeout. It's normal and not to be concerned with.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional Lock:Timeout
>> event. I am assuming this means that some transaction is blocked, however
>> the @.@.LOCK_TIMEOUT value is set to -1 which means that locks never
>> timeout. So why am I detecting this event?
>> Any help much appreciated...
>|||It is hard to say how much of this is caused by the internal stuff or not.
Most likely it is nothing to worry about. But why are you looking at it in
the first place? Did you have an issue specific to locks or timeouts or
just curious? Unless you have an actual problem or other symptoms related
to this I wouldn't worry about it.
--
Andrew J. Kelly SQL MVP
"Mark" <swozz_@.hotmail.com> wrote in message
news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
> Are you talking about spinlocks here? Isn't it the case that spinlocks
> never timeout? Anyway, during these timeouts, SQL Profiler does report an
> objectID of 0 most of the time, but occaisionally the object ID is on one
> of the user tables - do you still think we shouldn't be concerned with it?
> --
> Best regards
> Mark Baldwin
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>|||We did have a deadlock issue which is now resolved but in the process in
investigating I stumbled on these timeouts messages. Yesterday, the web site
developer says he had one timeout issue, the profiler reported 7 timeout
issues on user data and around 40 timeout issues all together. I am just
trying to understand this Lock:Timeout message to determine if I can use it
to resolve and possibly find as yet unknown problems residing in the
database.
--
Best regards
Mark Baldwin
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23xWiDurjFHA.1444@.TK2MSFTNGP10.phx.gbl...
> It is hard to say how much of this is caused by the internal stuff or not.
> Most likely it is nothing to worry about. But why are you looking at it
> in the first place? Did you have an issue specific to locks or timeouts
> or just curious? Unless you have an actual problem or other symptoms
> related to this I wouldn't worry about it.
> --
> Andrew J. Kelly SQL MVP
>
> "Mark" <swozz_@.hotmail.com> wrote in message
> news:%234DOsVrjFHA.1044@.tk2msftngp13.phx.gbl...
>> Are you talking about spinlocks here? Isn't it the case that spinlocks
>> never timeout? Anyway, during these timeouts, SQL Profiler does report an
>> objectID of 0 most of the time, but occaisionally the object ID is on one
>> of the user tables - do you still think we shouldn't be concerned with
>> it?
>> --
>> Best regards
>> Mark Baldwin
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23QGbifmjFHA.2852@.TK2MSFTNGP15.phx.gbl...
>> This also includes internal lightweight locks that timeout and are not
>> the same as a real lock timeout. It's normal and not to be concerned
>> with.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Mark" <swozz_@.hotmail.com> wrote in message
>> news:uzFOVYhjFHA.3960@.TK2MSFTNGP12.phx.gbl...
>> In our SQL Server, the Profiler is detecting the occaisional
>> Lock:Timeout event. I am assuming this means that some transaction is
>> blocked, however the @.@.LOCK_TIMEOUT value is set to -1 which means that
>> locks never timeout. So why am I detecting this event?
>> Any help much appreciated...
>>
>>
>|||Mark wrote:
> We did have a deadlock issue which is now resolved but in the process
> in investigating I stumbled on these timeouts messages. Yesterday,
> the web site developer says he had one timeout issue, the profiler
> reported 7 timeout issues on user data and around 40 timeout issues
> all together. I am just trying to understand this Lock:Timeout
> message to determine if I can use it to resolve and possibly find as
> yet unknown problems residing in the database.
>
You can. But as Andrew stated, many of the Timeout errors are internal
lightweight timeouts. You may be able to use the Duration column to
determine if it is a relevant timeout.
--
David Gugick
Quest Software
www.imceda.com
www.quest.com
Lock table until transaction finish.
HOw do I write a statement to lock a table until my transactions are finish?
e.g...
declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'
'Lock Syantax here
insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value
'Lock Syantax end
The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".
I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.
Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....
err..btw..this is abit out of the topic...
I was wondering,which is much faster and less taxing on cpu processing?
select ident_current('tablename') vs select max(tableidentitycolumn)
as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.
can someone actually really test it out at their environment ?
I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.
Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.
blindman
e.g...
declare @.LatestRecordid int
declare @.Value varchar(10)
set @.Value='just a value'
'Lock Syantax here
insert into table_to_be_lock (tablecolumn) values (@.value)
set @.LatestRecordid=(select ident_current('table_to_be_lock')+1
Update summarytable set LatestId=@.LatestRecordid, ItsValue=@.value
'Lock Syantax end
The reason I need to lock it is because at the second line of code, I am afraid another person has run the same query and inserted another record b4 my second line of code is runned, thus I'll get the wrong Recordid pointing to the wrong "ItsValue".
I am pretty sure there is a syantax for it...but I just can't seem to remember...can anybody help?Not that it will happen because SQl Server Default isolation level is "Read Commited". In other words , other user will see only written records and SQL will handle locks internally (shared, update,Row level, Table level ..etc.)|||Use SCOPE_IDENTITY instead of ident_current - you will have your id.
Anyway it is good idea to open transaction before your insert, check for erros during insert and updates (rollback if it needs) and commit transaction.|||okay....wil try to read on scope....
err..btw..this is abit out of the topic...
I was wondering,which is much faster and less taxing on cpu processing?
select ident_current('tablename') vs select max(tableidentitycolumn)
as both gets the same value. I can't test it out as I only have a small table here, can't tell the difference.
can someone actually really test it out at their environment ?
I'm guessing ident_current will be much faster, correct?|||Patrick, ur guess is correct, max will parse through each row in the table. I guess ident_current,@.@.identity is like a global variable which stores the last ident value.|||So many problems would be so much easier if developers used UniqueIdentifiers instead of GUIDs.
Sigh. :(|||hmm..what are the difference??
isit the ones that is being generated usually during replication??
read somewhere that those datatype are bigger in size and may cause slower indexes...|||They may be bigger in size, but you can use them in very creative SQL schemas and avoid much of the overhead of tracking and looking up identity values.
blindman
Friday, March 23, 2012
Lock issue during insert
:confused:
I opened 2 sql analizer windows to simulate 2 users:
In the first one I did this:
begin transaction;
insert into tst values (15);
In the second one I sent
begin transaction;
select * from tst where col=3;
The second statement is blocked waiting for the lock to be released.
Why? I tried update in plce of the insert and then there is no lock.
What is the problem with insert ?
regards
phildo you have an index on col?
if not then the select will do a table scan and be blocked. It will return nothing until the output buffer is full or the lock is released.
In both cases you should find a few intent exclusive locks and at least one exclusive.
If the select tries to access a resource that is locked then it will be blocked - if not it won't be.
Maybe your insert was forcing some page splits whereas the update wasn't?|||You're right, it does not happen when an index is on the table.
Why this happen only for inserts , and not updates.
What do you mean by lock return when the buffer is full.
regards
phil|||>> What do you mean by lock return when the buffer is full.
When you run a query in query analyser the output will be dumped to the result window when the output buffer is full or when the query completes. That's why if you run a large select you will see the results in batches - and why it doesn't mean that a query is stuck after the last result displayed.
It should happen for both inserts and updates - it just depends on what is being locked.
I opened 2 sql analizer windows to simulate 2 users:
In the first one I did this:
begin transaction;
insert into tst values (15);
In the second one I sent
begin transaction;
select * from tst where col=3;
The second statement is blocked waiting for the lock to be released.
Why? I tried update in plce of the insert and then there is no lock.
What is the problem with insert ?
regards
phildo you have an index on col?
if not then the select will do a table scan and be blocked. It will return nothing until the output buffer is full or the lock is released.
In both cases you should find a few intent exclusive locks and at least one exclusive.
If the select tries to access a resource that is locked then it will be blocked - if not it won't be.
Maybe your insert was forcing some page splits whereas the update wasn't?|||You're right, it does not happen when an index is on the table.
Why this happen only for inserts , and not updates.
What do you mean by lock return when the buffer is full.
regards
phil|||>> What do you mean by lock return when the buffer is full.
When you run a query in query analyser the output will be dumped to the result window when the output buffer is full or when the query completes. That's why if you run a large select you will see the results in batches - and why it doesn't mean that a query is stuck after the last result displayed.
It should happen for both inserts and updates - it just depends on what is being locked.
Wednesday, March 21, 2012
Location of transaction log on another machine
Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
Steve
Basically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default...b;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.c om...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
Steve
Basically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default...b;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.c om...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
Location of transaction log on another machine
Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performa
nce hit for
modifications. For the full story, check out
http://support.microsoft.com/defaul...kb;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performa
nce hit for
modifications. For the full story, check out
http://support.microsoft.com/defaul...kb;en-us;304261
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
Location of transaction log on another machine
Hi,
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
We have SQL server 2000 enterprise edition installed on a machine with
only one hard drive. On this server we have a small but critical
database. I have noticed that the person who created this database
placed the location of the transaction log on the same machine as the
data file.
Can I move the transaction log to another machine on the network that
doesn't have SQL server installed? Will there be a performance hit if
SQL Server has to write to this log over the network? If so will it be
major?
I know ideally we should be using a RAID setup of some sort, but for
finacial and political reasons this is not an option.
Cheers,
SteveBasically, SQL Server doesn't support this. Doing it would be a big performance hit for
modifications. For the full story, check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Steve" <steve.hager@.gmail.com> wrote in message
news:d5c55fd1.0412012214.76710133@.posting.google.com...
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve|||Steve wrote:
> Hi,
> We have SQL server 2000 enterprise edition installed on a machine with
> only one hard drive. On this server we have a small but critical
> database. I have noticed that the person who created this database
> placed the location of the transaction log on the same machine as the
> data file.
> Can I move the transaction log to another machine on the network that
> doesn't have SQL server installed? Will there be a performance hit if
> SQL Server has to write to this log over the network? If so will it be
> major?
> I know ideally we should be using a RAID setup of some sort, but for
> finacial and political reasons this is not an option.
> Cheers,
>
> Steve
How about another hard drive. That's no too much money...
If it's a small database, it'll probably be fine.
David Gugick
Imceda Software
www.imceda.com
Monday, February 20, 2012
local and distributed transaction
Hi all,
Depending on a parameter in a sp, I have to do work either locally or
remotely.
The work to be done is the same in both case.
I wrote:
if @.param = 1
begin transaction
else
begin DISTRIBUTED transaction
/* do the work */
commit transaction
Does anyone knows if Microsoft have stated any recommandation on not writing
code like that (that is not having both type of transaction in the same
place) ?
ThanksHi
though logically what you have given is right, I feel you can use a
distributed transaction for both cases and live with out the if condition.
distributed transactions can also work locally.|||Hi,
Thanks for the answer.
Do you know if there might be any performance penlaty using distributed
transaction locally ?
"Omnibuzz" wrote:
> Hi
> though logically what you have given is right, I feel you can use a
> distributed transaction for both cases and live with out the if condition.
> distributed transactions can also work locally.|||Technically there shouldn't be any difference since if there are no other
resource managers that are participating in the distirbuted transaction
except the local server. Infact SQL Server uses a distirbuted transaction
spanning across databases within the same server. I feel it should be fine t
o
use it.
But if you are uncomfortable about this, u can use two SPs one having the
local transaction and one for the distirbuted transaction. It will look
better than the approach you have suggested. Hope this helps.|||A still better solution. You will have to check it though.
Add this line to the beginning of the proc
SET REMOTE_PROC_TRANSACTIONS ON
and use "begin transaction".
On a remote procedure call, it will promote the local transaction to
distirbuted trasanction, else will keep it as a local transaction.
Hope this helps.
Depending on a parameter in a sp, I have to do work either locally or
remotely.
The work to be done is the same in both case.
I wrote:
if @.param = 1
begin transaction
else
begin DISTRIBUTED transaction
/* do the work */
commit transaction
Does anyone knows if Microsoft have stated any recommandation on not writing
code like that (that is not having both type of transaction in the same
place) ?
ThanksHi
though logically what you have given is right, I feel you can use a
distributed transaction for both cases and live with out the if condition.
distributed transactions can also work locally.|||Hi,
Thanks for the answer.
Do you know if there might be any performance penlaty using distributed
transaction locally ?
"Omnibuzz" wrote:
> Hi
> though logically what you have given is right, I feel you can use a
> distributed transaction for both cases and live with out the if condition.
> distributed transactions can also work locally.|||Technically there shouldn't be any difference since if there are no other
resource managers that are participating in the distirbuted transaction
except the local server. Infact SQL Server uses a distirbuted transaction
spanning across databases within the same server. I feel it should be fine t
o
use it.
But if you are uncomfortable about this, u can use two SPs one having the
local transaction and one for the distirbuted transaction. It will look
better than the approach you have suggested. Hope this helps.|||A still better solution. You will have to check it though.
Add this line to the beginning of the proc
SET REMOTE_PROC_TRANSACTIONS ON
and use "begin transaction".
On a remote procedure call, it will promote the local transaction to
distirbuted trasanction, else will keep it as a local transaction.
Hope this helps.
Subscribe to:
Posts (Atom)