|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
No rows returnedWe have encountered a problem where a table has become 'damaged' (for lack of
a better word) such that when we either attempt to perform an update on the table or do a simple query of 'select * from unit', the queries 'hang'. However when we look at the properties on that table it says there are 480 (or some similar number). If we do 'select top 400 * from unit' the query returns rows but select all rows or a value > 480 and it hangs. I've done a checktable and checkdb and it indicates there is no problem. This has happened at several customer sites and need a solution. What happened to this table and how do we repair it? Cordially, Mark Mark Boettcher wrote:
> We have encountered a problem where a table has become 'damaged' (for lack of If you look at the sysprocesses table while this query is "hung", what > a better word) such that when we either attempt to perform an update on the > table or do a simple query of 'select * from unit', the queries 'hang'. > However when we look at the properties on that table it says there are 480 > (or some similar number). If we do 'select top 400 * from unit' the query > returns rows but select all rows or a value > 480 and it hangs. I've done a > checktable and checkdb and it indicates there is no problem. > > This has happened at several customer sites and need a solution. What > happened to this table and how do we repair it? > > Cordially, > Mark does it show? Is there a waittype specified? Is the spid being blocked? The waittype says <binary>, the blocked column says 54, the lastwaittype is
'LCK_M_S', the status =- 'sleeping' Show quote "Tracy McKibben" wrote: > Mark Boettcher wrote: > > We have encountered a problem where a table has become 'damaged' (for lack of > > a better word) such that when we either attempt to perform an update on the > > table or do a simple query of 'select * from unit', the queries 'hang'. > > However when we look at the properties on that table it says there are 480 > > (or some similar number). If we do 'select top 400 * from unit' the query > > returns rows but select all rows or a value > 480 and it hangs. I've done a > > checktable and checkdb and it indicates there is no problem. > > > > This has happened at several customer sites and need a solution. What > > happened to this table and how do we repair it? > > > > Cordially, > > Mark > > If you look at the sysprocesses table while this query is "hung", what > does it show? Is there a waittype specified? Is the spid being blocked? > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > Mark Boettcher wrote:
> The waittype says <binary>, the blocked column says 54, the lastwaittype is What is spid 54 doing? It's blocking your query...> 'LCK_M_S', the status =- 'sleeping' > I agree with Tracy, it sounds like a row is blocked.
-- Show quoteAndrew J. Kelly SQL MVP "Mark Boettcher" <Mark Boettc***@discussions.microsoft.com> wrote in message news:3AFB0CA4-FD75-4DCA-8867-9FF4B07F91C3@microsoft.com... > We have encountered a problem where a table has become 'damaged' (for lack > of > a better word) such that when we either attempt to perform an update on > the > table or do a simple query of 'select * from unit', the queries 'hang'. > However when we look at the properties on that table it says there are 480 > (or some similar number). If we do 'select top 400 * from unit' the query > returns rows but select all rows or a value > 480 and it hangs. I've done > a > checktable and checkdb and it indicates there is no problem. > > This has happened at several customer sites and need a solution. What > happened to this table and how do we repair it? > > Cordially, > Mark Should I remove that row from the sysprocesses table that has that spid
LCK_M_S? If not, how should I go about clearing that lock? Show quote "Andrew J. Kelly" wrote: > I agree with Tracy, it sounds like a row is blocked. > > -- > Andrew J. Kelly SQL MVP > > "Mark Boettcher" <Mark Boettc***@discussions.microsoft.com> wrote in message > news:3AFB0CA4-FD75-4DCA-8867-9FF4B07F91C3@microsoft.com... > > We have encountered a problem where a table has become 'damaged' (for lack > > of > > a better word) such that when we either attempt to perform an update on > > the > > table or do a simple query of 'select * from unit', the queries 'hang'. > > However when we look at the properties on that table it says there are 480 > > (or some similar number). If we do 'select top 400 * from unit' the query > > returns rows but select all rows or a value > 480 and it hangs. I've done > > a > > checktable and checkdb and it indicates there is no problem. > > > > This has happened at several customer sites and need a solution. What > > happened to this table and how do we repair it? > > > > Cordially, > > Mark > > > Mark Boettcher wrote:
> Should I remove that row from the sysprocesses table that has that spid You don't "remove" rows from the sysprocesses table. That table shows > LCK_M_S? If not, how should I go about clearing that lock? > you the list of active processes on your server. What you've told us thus far is that you have a process, SPID 54 (that's the process ID), that is currently blocking another process. It's blocking because it has locked a portion of your table in order to do some work with it. Your job, as the DBA, is to determine what that process (SPID 54) is doing, why it has established this lock, and how you can optimize it to prevent that lock from being held for too long. Further review of the sysprocesses table will reveal a row for SPID 54, along with columns that tell you various things about the process, such as how long it has been active, what user initiated the process, and what machine that user connected from. You can use DBCC INPUTBUFFER(54) to see the SQL statement that was last issued by the process. I did review those rows and killed the appropriate spids. The table is now
unlocked and we are working again. Thank you, Tracy. Show quote "Tracy McKibben" wrote: > Mark Boettcher wrote: > > Should I remove that row from the sysprocesses table that has that spid > > LCK_M_S? If not, how should I go about clearing that lock? > > > > You don't "remove" rows from the sysprocesses table. That table shows > you the list of active processes on your server. What you've told us > thus far is that you have a process, SPID 54 (that's the process ID), > that is currently blocking another process. It's blocking because it > has locked a portion of your table in order to do some work with it. > > Your job, as the DBA, is to determine what that process (SPID 54) is > doing, why it has established this lock, and how you can optimize it to > prevent that lock from being held for too long. > > Further review of the sysprocesses table will reveal a row for SPID 54, > along with columns that tell you various things about the process, such > as how long it has been active, what user initiated the process, and > what machine that user connected from. You can use DBCC INPUTBUFFER(54) > to see the SQL statement that was last issued by the process. > > > -- > Tracy McKibben > MCDBA > http://www.realsqlguy.com > Well it obviously happens often enough to get to the root cause. Just
killing the spid does not solve the reason why it happened and won't prevent it from happening again. You need to find out why the transaction was started and never committed. -- Show quoteAndrew J. Kelly SQL MVP "Mark Boettcher" <MarkBoettc***@discussions.microsoft.com> wrote in message news:A11316BD-ED10-4A4F-B447-FA840F900239@microsoft.com... >I did review those rows and killed the appropriate spids. The table is now > unlocked and we are working again. > > Thank you, Tracy. > > "Tracy McKibben" wrote: > >> Mark Boettcher wrote: >> > Should I remove that row from the sysprocesses table that has that spid >> > LCK_M_S? If not, how should I go about clearing that lock? >> > >> >> You don't "remove" rows from the sysprocesses table. That table shows >> you the list of active processes on your server. What you've told us >> thus far is that you have a process, SPID 54 (that's the process ID), >> that is currently blocking another process. It's blocking because it >> has locked a portion of your table in order to do some work with it. >> >> Your job, as the DBA, is to determine what that process (SPID 54) is >> doing, why it has established this lock, and how you can optimize it to >> prevent that lock from being held for too long. >> >> Further review of the sysprocesses table will reveal a row for SPID 54, >> along with columns that tell you various things about the process, such >> as how long it has been active, what user initiated the process, and >> what machine that user connected from. You can use DBCC INPUTBUFFER(54) >> to see the SQL statement that was last issued by the process. >> >> >> -- >> Tracy McKibben >> MCDBA >> http://www.realsqlguy.com >> You can't expect anything more than a quick fix on a friday afternoon :)
-Omni Show quote "Andrew J. Kelly" wrote: > Well it obviously happens often enough to get to the root cause. Just > killing the spid does not solve the reason why it happened and won't prevent > it from happening again. You need to find out why the transaction was > started and never committed. > > -- > Andrew J. Kelly SQL MVP The transaction was started by a program of ours and the program was then
shutdown by using Task Manager to close the program, thus leaving the transaction uncomitted. Show quote "Omnibuzz" wrote: > You can't expect anything more than a quick fix on a friday afternoon :) > > -Omni > > "Andrew J. Kelly" wrote: > > > Well it obviously happens often enough to get to the root cause. Just > > killing the spid does not solve the reason why it happened and won't prevent > > it from happening again. You need to find out why the transaction was > > started and never committed. > > > > -- > > Andrew J. Kelly SQL MVP >  |
|||||||||||||||||||||||
Other interesting topics