Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is ?
Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is ?
>
|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Lock Database

I have problem with my database, sometimes I can not run query from query
analyzer or from my application.
When I run the query the process is very long (timeout) and might be
something has locked the database. And it's will run normally after I
restart the server. Any one know what happen with the database? and I have
to do to avoid locking database. Please help...Hi
I'd run SQL Server Profiler to capture events while you execute the query.
Also, review your query , do you have indexes on the table, how many rows
your query returns?
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I
have
> to do to avoid locking database. Please help...
>
>|||Yes I have index on the table, the query return approximate 10k rows
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> Hi
> I'd run SQL Server Profiler to capture events while you execute the query.
> Also, review your query , do you have indexes on the table, how many rows
> your query returns?
>
>
> "Firmansyah" <syahmail@.softhome.net> wrote in message
> news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
>> I have problem with my database, sometimes I can not run query from query
>> analyzer or from my application.
>> When I run the query the process is very long (timeout) and might be
>> something has locked the database. And it's will run normally after I
>> restart the server. Any one know what happen with the database? and I
> have
>> to do to avoid locking database. Please help...
>>
>|||Hi
Perhaps you run the query during a workload in your company where there are
many users using sql server.
Does sp_who2 show any block issues?
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> Yes I have index on the table, the query return approximate 10k rows
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> > Hi
> > I'd run SQL Server Profiler to capture events while you execute the
query.
> > Also, review your query , do you have indexes on the table, how many
rows
> > your query returns?
> >
> >
> >
> >
> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> >> I have problem with my database, sometimes I can not run query from
query
> >> analyzer or from my application.
> >> When I run the query the process is very long (timeout) and might be
> >> something has locked the database. And it's will run normally after I
> >> restart the server. Any one know what happen with the database? and I
> > have
> >> to do to avoid locking database. Please help...
> >>
> >>
> >>
> >
> >
>|||check locks from Enterpise Manager.
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I
have
> to do to avoid locking database. Please help...
>
>|||Yes I found block issues, when I saw on Lock/process ID Spid 53 (Block). can
you advice me why this happen and how to release the blocked process.
I can't figure out why the process was blocked.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23X5CzYPsEHA.2560@.tk2msftngp13.phx.gbl...
> Hi
> Perhaps you run the query during a workload in your company where there
> are
> many users using sql server.
> Does sp_who2 show any block issues?
>
>
> "Firmansyah" <syahmail@.softhome.net> wrote in message
> news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
>> Yes I have index on the table, the query return approximate 10k rows
>> "Uri Dimant" <urid@.iscar.co.il> wrote in message
>> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
>> > Hi
>> > I'd run SQL Server Profiler to capture events while you execute the
> query.
>> > Also, review your query , do you have indexes on the table, how many
> rows
>> > your query returns?
>> >
>> >
>> >
>> >
>> > "Firmansyah" <syahmail@.softhome.net> wrote in message
>> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
>> >> I have problem with my database, sometimes I can not run query from
> query
>> >> analyzer or from my application.
>> >> When I run the query the process is very long (timeout) and might be
>> >> something has locked the database. And it's will run normally after I
>> >> restart the server. Any one know what happen with the database? and I
>> > have
>> >> to do to avoid locking database. Please help...
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Hi
http://www.sql-server-performance.com/blocking.asp
"Firmansyah" <syahmail@.softhome.net> wrote in message
news:u9vsvnPsEHA.3556@.TK2MSFTNGP10.phx.gbl...
> Yes I found block issues, when I saw on Lock/process ID Spid 53 (Block).
can
> you advice me why this happen and how to release the blocked process.
> I can't figure out why the process was blocked.
>
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23X5CzYPsEHA.2560@.tk2msftngp13.phx.gbl...
> > Hi
> > Perhaps you run the query during a workload in your company where there
> > are
> > many users using sql server.
> > Does sp_who2 show any block issues?
> >
> >
> >
> >
> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> > news:%23mekrKPsEHA.2612@.TK2MSFTNGP10.phx.gbl...
> >> Yes I have index on the table, the query return approximate 10k rows
> >>
> >> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> >> news:uclwL4OsEHA.2956@.TK2MSFTNGP12.phx.gbl...
> >> > Hi
> >> > I'd run SQL Server Profiler to capture events while you execute the
> > query.
> >> > Also, review your query , do you have indexes on the table, how many
> > rows
> >> > your query returns?
> >> >
> >> >
> >> >
> >> >
> >> > "Firmansyah" <syahmail@.softhome.net> wrote in message
> >> > news:em$FBxOsEHA.1216@.TK2MSFTNGP10.phx.gbl...
> >> >> I have problem with my database, sometimes I can not run query from
> > query
> >> >> analyzer or from my application.
> >> >> When I run the query the process is very long (timeout) and might be
> >> >> something has locked the database. And it's will run normally after
I
> >> >> restart the server. Any one know what happen with the database? and
I
> >> > have
> >> >> to do to avoid locking database. Please help...
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>|||Hi!
I guess one of your transaction still opened, and locking some resource.
First, you should try with sp_who2 to see which spid is blocking your query.
After that if you have the spid, try to run dbcc inputbuffer(#)
(write the spid number instead of the #)
This will probably show what this spid run last.
Another good thing is, that you can run sp_lock #
(write the spid number instead of the #)
This will show the resources blocked by the spid
(if you have the object_id , use select object_name(#object_id) to obtain
the name of the resource)
I hope it will help
Gza
"Firmansyah" wrote:
> I have problem with my database, sometimes I can not run query from query
> analyzer or from my application.
> When I run the query the process is very long (timeout) and might be
> something has locked the database. And it's will run normally after I
> restart the server. Any one know what happen with the database? and I have
> to do to avoid locking database. Please help...
>
>|||Thanks you very much your info useful for me.
Firman
"Gza" <Gza@.discussions.microsoft.com> wrote in message
news:5C993D6E-277A-4399-99BC-D20066F15C40@.microsoft.com...
> Hi!
> I guess one of your transaction still opened, and locking some resource.
> First, you should try with sp_who2 to see which spid is blocking your
> query.
> After that if you have the spid, try to run dbcc inputbuffer(#)
> (write the spid number instead of the #)
> This will probably show what this spid run last.
> Another good thing is, that you can run sp_lock #
> (write the spid number instead of the #)
> This will show the resources blocked by the spid
> (if you have the object_id , use select object_name(#object_id) to obtain
> the name of the resource)
> I hope it will help
> Gza
>
> "Firmansyah" wrote:
>> I have problem with my database, sometimes I can not run query from query
>> analyzer or from my application.
>> When I run the query the process is very long (timeout) and might be
>> something has locked the database. And it's will run normally after I
>> restart the server. Any one know what happen with the database? and I
>> have
>> to do to avoid locking database. Please help...
>>
>>

Local or global variable in an interaction session of the SQL analyzer

Hi,

I am new to SQL. Please bear with me and allow me to ask a dumb
question.

I am debugging a stored procedure (written in Trans-SQL), and I found
that the SQL analyzer that I use doesn't have a debugger. All I can
do it is execute a block of code and see what is going on in an
interaction seesion of the SQL analyzer. I would need to declare some
variable to hold values of the previous query in the interactive
session.

I understand that this can be easily done in a stored procedure via
the Declare command (e.g., Delcare @.order_no int). Is similar
functionality exists in an interaction session of the SQL analyzer?
If so, what is the command. Please advise.

Thank you very much for the help.

AlexAlex Cicco (lluum@.yahoo.com) writes:
> I am debugging a stored procedure (written in Trans-SQL), and I found
> that the SQL analyzer that I use doesn't have a debugger. All I can
> do it is execute a block of code and see what is going on in an
> interaction seesion of the SQL analyzer. I would need to declare some
> variable to hold values of the previous query in the interactive
> session.

I suppose that with SQL Analyzer you refer to Query Analyzer. If you are
using QA, you do indeed have an access to a debugger. Press F8 to get an
Object Browser Window, and then find the procedure. Right-click and at
the bottom of the context menu, voil!

> I understand that this can be easily done in a stored procedure via
> the Declare command (e.g., Delcare @.order_no int). Is similar
> functionality exists in an interaction session of the SQL analyzer?
> If so, what is the command. Please advise.

The same. For instance:

DECLARE @.x datetime
SET @.x = getdate()
SELECT @.x

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp