lock request time out period exceded

Hi,

while my db is executing a store procedure i try to view the current activity in the managent but it returns to me 'Lock request time out period exceded'. It happens until the store procedure is finished. After that everything is ok. However i can see the activity executing sp_who and the db seems to work ok, maybe a little slow.

what does it means??

Sorry for my english.
Thanks. EduardoYou should understand the database engine's locking mechanism. Your stored procedures probably is modifying your data, which means an exclusive lock. If you or another process is trying to view the data, the engine tries to get a shared lock. Since there is already an exclusive lock, the request for getting the shared lock expires, which is shown to you.sql

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:
>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.sql

Lock Information

I need some help in interpreting the information displayed under
Management -> Current Activity -> Locks / Process ID in Enterprise
Manager.
There seems to be a number of locks against the "Water.dbo.sched_lock"
object. The values in the Index column are different for the different
rows: "Gazetteer", "Employees" and "pk_workbank...".
The strange thing is that these are not indexes of the Sched_Lock
table. Gazetteer and Employees refer to 2 different tables within the
database.
Why are they being displayed against a lock which is supposed to be
held against the "sched_lock" table?No ideas, anyone?|||I never use that to monitor locks. A few years ago there
were posts like yours so I played around with viewing locks
from Enterprise Manager and found that it's pretty easy to
get bogus, useless info. I specifically remember seeing the
wrong table references. And then if you look at the stored
procedure used by Enterprise Manager,
sp_MSset_currency_activity, you can see how the results
wouldn't be too reliable.
Use sp_lock, sp_who2 to monitor things. Or even better is
the stored procedure Erland wrote:
http://www.sommarskog.se/sqlutil/aba_lockinfo.html
-Sue
On 7 Sep 2006 09:39:26 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>I need some help in interpreting the information displayed under
>Management -> Current Activity -> Locks / Process ID in Enterprise
>Manager.
>There seems to be a number of locks against the "Water.dbo.sched_lock"
>object. The values in the Index column are different for the different
>rows: "Gazetteer", "Employees" and "pk_workbank...".
>The strange thing is that these are not indexes of the Sched_Lock
>table. Gazetteer and Employees refer to 2 different tables within the
>database.
>Why are they being displayed against a lock which is supposed to be
>held against the "sched_lock" table?|||Thanks very much Sue - it's a pain that a part of Enterprise Manager
itself would be unreliable.
I've had a look at sp_MSset_current_activity, and I've seen where the
Index column is likely to get mixed up. I can't see why the Table
column would be unreliable though - it seems like a fair bet to me that
the data in the table column is correct, and the locks are being held
against the "sched_lock" table. Would you agree?|||In your case, yes.
In terms of the other areas of unreliability in that stored
procedure, look at the isolation levels being used.
Many GUI tools for databases are have issues - not just
Microsoft's. You're often better off just knowing the SQL
commands to execute. It generally gives you more flexibility
in writing some of your own scripts to retrieve the data you
want, with the information you need or care about.
-Sue
On 11 Sep 2006 08:21:22 -0700, "Paul H"
<del.monte@.ntlworld.com> wrote:

>Thanks very much Sue - it's a pain that a part of Enterprise Manager
>itself would be unreliable.
>I've had a look at sp_MSset_current_activity, and I've seen where the
>Index column is likely to get mixed up. I can't see why the Table
>column would be unreliable though - it seems like a fair bet to me that
>the data in the table column is correct, and the locks are being held
>against the "sched_lock" table. Would you agree?|||Indeed.
Thanks very much.

Location of dbases.

Hi!

When I'm looking at the properties of my dbases I see there are at c:\program files\ms sql server\ etc..

But I want to put ALL my current and future databases to E:\.

Can somebody tell me how to achieve that?

You should use alter Database command to move the files to the new location.

When you create your database you can specfic the location that you want the files create as part of the create Database command.

Michelle

|||

I've found out how to place new databases.

But can't I igive up a default location? Cause people that don't know about it maybe put it in the wrong place.

And the alter command? I tried to change it true the GUI but the location properties where grey.

Local temporary table schema

It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich WoodIs there a way to get the schema (columns) of a local temporary table in SQL
Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be
a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
" #MyTable________________________________
___________________________________
_______________________________________0
000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich Wood|||It seems odd that you would need to do this, since your code for the current
connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT *
FROM Tempdb..sysobjects
WHERE name LIKE '%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#Temp')
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich Wood|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...|||Arnie Rowland wrote:

> It seems odd that you would need to do this, since your code for the curr=
ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=
54-45EB-8978-6391D4939CD1@.microsoft.com...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>It seems odd that you would need to do t=
his, since
> your code for the current connection had to know how to create the #Temp
> Table.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=

cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
========================================
==========
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't kno
w
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-075
4-45EB-8978-6391D4939CD1@.microsoft.com...