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...
>>
>>