February 22, 2011 at 8:24 am
I have a database which will not return a simple ‘SELECT Count(*)FROM dbo.APProduct’. I created this database from a backup on a database where the query works just fine. Also the query will run against other databases within the same instance.
To validate the process I also restore to 3 other servers on which the query runs successful. I have validated that the instance configurations are the same. I am somewhat of a novice on SQL Server and do not know were to look now. Can anyone assist? Thanks
February 22, 2011 at 8:35 am
wemorganjr (2/22/2011)
I have a database which will not return a simple ‘SELECT Count(*)FROM dbo.APProduct’.
I am not exactly sure what you mean by this... like does it fail, or return with no output or does it just hang there forever? I will assume the latter, if so it sounds as though some other process has the table locked and your query is just waiting.
run sp_who to see what other processes are running against tables in that database or have any resources locked.
The probability of survival is inversely proportional to the angle of arrival.
February 22, 2011 at 8:45 am
Sorry, the query never comes back. After running sp_who, the select was the only thing running.
I can run a 'select * ' from the table and it returns. I am able to perform the same select count against the other tables, it appears to be just one table. if I restore the db to another server it works fine. count an index be corrupted?
February 22, 2011 at 8:49 am
how many rows are in the table? (I mean approximately?)
The reason a select * can return almost immediately is that it doesn't have to read the entire table (all data pages) before returning results, whereas a count(*) does.
I still think somehow one or more of the data pages are being locked so you process is being blocked.
When you run sp_who2 what else does it say about the process.... is it runnable?
The probability of survival is inversely proportional to the angle of arrival.
February 22, 2011 at 8:51 am
1,047,800
February 22, 2011 at 8:54 am
You may not have seen my edits:
The reason a select * can return almost immediately is that it doesn't have to read the entire table (all data pages) before returning results, whereas a count(*) does.
I still think somehow one or more of the data pages (or index pages if it is trying to scan an index) are being locked so you process is being blocked.
When you run sp_who2 what else does it say about the process.... is it runnable?
The probability of survival is inversely proportional to the angle of arrival.
February 22, 2011 at 8:59 am
68 RUNNABLE WINNDIXIEUS\morgaweWKS-HDQ-MJ71809 .ApolloWebSELECT INTO 103911405302/22 10:55:49Microsoft SQL Server Management Studio - Query68 0
February 22, 2011 at 9:03 am
that is not the process doing the count(*) that is your sp_who2 process.
to avoid confustion. USE MASTER then run the sp_who2 ... look only for results concerning that database.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply