Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Blastrix
Posting Yak Master
208 Posts |
 Posted - 2004-08-04 : 13:18:59
|
| Is there a way to "fix" a table? I have a table that when querying with some parameters in the WHERE clause it will retrieve fine. However, if I just try a SELECT * FROM table the process running it will lock the table, all related tables, and block all other access to the table. The only way to release is to kill the process. This happens in QA, EM, and from the application. Should I try dropping and recreating? Is there a DBCC command I can run on it? There's only about 30-35 rows in the table, so it's not like there's just too much data to retrieve. Any help would be appreciated.Thanks,Steve |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2004-08-04 : 13:25:42
|
| I have seen this happen when the table has a filter by or order by applied to a collum that has been removed. Removeing the filter or order by fixes the problem.JimUsers <> Logic |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-04 : 13:27:45
|
| You can check the integrity of the database using DBCC CHECKTABLE. DBCC CHECKDB will check the integrity of the entire database. I wouldn't suspect that SELECT * FROM Table is causing your problem. I suspect some other process is blocking you. You can find out with sp_who, sp_who2, or SQL Profiler.Tara |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-08-05 : 14:40:58
|
| I checked, and there are no existing indexes for non-existant columns.I ran CHECKDB and was informed there were no allocation or consistency errors. The problem still happens though. As you mentioned Tara, it isn't the SELECT that causes the problem, the SELECT is merely the on the receiving end of the problem. The blocking is caused by a query which has never had any problems running, and now all of a sudden holds locks, but seems to still commit all of the information to the tables. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 14:43:27
|
| Run SQL Profiler to determine exactly what is going on.You need to investigate the query that is causing the problem. You might not have had enough data until to see a performance problem with the query. You might have outgrown your hardware (use Performance Monitor to find this out). You might need additional indexes to support the query. ...Tara |
|
|
|
Blastrix
Posting Yak Master
208 Posts |
Posted - 2004-08-05 : 15:55:55
|
| Are there any particular events I should be interested in with Profiler? |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-08-05 : 16:22:19
|
| Probably just need to take a look at the query first before checking out SQL Profiler.Could you post the query that is causing the problem? Let us know which columns are indexed, the columns in the query at least. How many rows in the table(s)? And anything else relevant.Tara |
|
|
|
|
|
|
|
|
Broken Table?