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...
> have
>|||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...
query.[vbcol=seagreen]
rows[vbcol=seagreen]
query[vbcol=seagreen]
>|||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...
> query.
> rows
> query
>|||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...
I[vbcol=seagreen]
I[vbcol=seagreen]
>|||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 hav
e
> 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...[vbcol=seagreen]
> 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:
>