SQL Server Performance Forum – Threads Archive
Table problem
Greetings!! I’ve a table which contained 600 rows. The problem is when I run the query: Select * from table It is hanging up. If I pass the following query: select top 570 * from table It’s working fine. It seems problem has begun from 571 th record. Its not even allowing me to delete the records from 571 to 600. I’m not even able to truncate or rename the table. Any type of help can be appreciated.Rajendar ok
Can you just try DTS to export to a new table and see the contents.
Any error message or information in erro log?
Run PROFILER to see the activity while running this query. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Run DBCC DBcheck to see if there is any asignation problem or consistence. Luis Martin …Thus mathematics may be defined as the subject in which we never know what we are talking about, nor whether what we are saying is true.
Bertrand Russell
Any image/text columns are involved?
What is the structure of the table? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Dear all, Thank U for prompt response. Table structure does not contain any image/text column. It has is very general columns like varchar,int etc
Rajendar ok
Run DBCC CHECKTABLE and see the result.
Is this happened recently, if so any changes to this table specifically?
_________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Dear Satya, I’ve ran the DTS. But it is stucking at that particular record and neither stopping the process nor firing the error. Rajendar ok
Since when this is happening on this table? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
Run SP_WHO2 while running this query and see if it is being blocked by any other process. There could be an exclusive row lock due to an open transaction that is updating some rows in this table.
You can also confirm this by running the query as ‘select * from table with (nolock)’. This will not honor any exclusive locks and makes dirty reads.
So the DBCC CHECKTABLE came back reporting the table being ok? Cheers
Twan
If I run this query:
select * from table with (nolock) Its working fine. How should I remove the lock on this table
Rajendar ok
run sp_lock and sp_who2 to see the blocking process. in sp_who you will see a column blkby which will give you the SPId of the blocking process. run DBCC INPUTBUFFER(spid) to see what this process it doing. if there’s no other way, kill it (KILL spid).<br />Nice catch ykchakri <img src=’/community/emoticons/emotion-1.gif’ alt=’
‘ /><br /><br />Bambola.
Dear Bambola, I’ve run the sp_who2. All the rows of blkby are empty. I did even tried to kill the process. But process is not killing. Shall I restart the SQL Server
Rajednar ok
IF the process is not KILLed then you must refresh the SQL services. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com
If one process is sitting idle and not releasing lock, the condition will not be termed as deadlock and the blkby column will not show any data. So the only way is check what process is maintaining a lock on the object id of table in question. Then kill that process. HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
]]>