1
Guys, I have two distinct client bases in SQL Server 2008, and both using the same system, which uses BDE for connection.
I have my own app Scripter to make SQL queries in databases.
Problem
On both counts, I execute select * from TABELA for Scripter and try to get into the system, with you no problems.
But if we execute select * from TABELA order by COLUNA desc, and try to get into the system, on one basis I can get in, and on another.
I’m using the sp_WhoIsActive to try to find the problem, and identified that in both databases, the SELECT stays ASYNC_NETWORK_IO.
At the base I can’t get inside it says SELECT is blocking a INSERT.
Attempts
I’ve tried recreating Inddice, shirink from the database and nothing. I compared the bases, and both have the same configuration.
Remarks
- I identified the database that generates the
lock, thetempdb_allocationsandtempdb_currentare zeroed in, does it have anything to do with? - Another detail is that in the database that does not generate lock, the Space Available is large, and in the database that generates the lock is almost 0 (zero) as shown below
Does not lock
Gera lock
Doubt
What you can do to identify the reason for the error?
Additional data (20/02/2018 at 13:25)
I ran sp_lock and the result was:
spid dbid ObjId IndId Type Resource Mode Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
51 42 0 0 DB S GRANT
52 42 0 0 DB S GRANT
54 76 0 0 DB S GRANT
55 103 0 0 DB S GRANT
56 103 0 0 MD 5(1:0:0) Sch-S GRANT
56 103 0 0 DB S GRANT
56 103 366624349 6 PAG 1:624712 S GRANT
56 103 366624349 6 PAG 1:624713 S GRANT
56 103 366624349 6 PAG 1:624714 S GRANT
56 103 366624349 6 PAG 1:624715 S GRANT
56 103 366624349 6 PAG 1:624708 S GRANT
56 103 366624349 6 PAG 1:624709 S GRANT
56 103 366624349 6 PAG 1:624710 S GRANT
56 103 366624349 6 PAG 1:624711 S GRANT
56 103 366624349 6 PAG 1:624704 S GRANT
56 103 366624349 6 PAG 1:624705 S GRANT
56 103 366624349 6 PAG 1:624706 S GRANT
56 103 366624349 6 PAG 1:624707 S GRANT
56 103 366624349 6 PAG 1:624660 S GRANT
56 103 366624349 6 PAG 1:624661 S GRANT
56 103 366624349 6 PAG 1:624662 S GRANT
56 103 366624349 6 PAG 1:624663 S GRANT
56 103 366624349 6 PAG 1:624659 S GRANT
56 103 366624349 1 PAG 1:526026 IS GRANT
56 103 366624349 6 PAG 1:624716 S GRANT
56 103 366624349 6 PAG 1:624717 S GRANT
56 103 366624349 0 TAB IS GRANT
57 103 366624349 0 TAB IX GRANT
57 103 366624349 6 PAG 1:624717 IX WAIT
57 103 0 0 DB S GRANT
57 103 366624349 1 KEY (07680471601e) X GRANT
57 103 366624349 2 KEY (9d8e9d84975e) X GRANT
57 103 366624349 3 KEY (2fcebe7f747e) X GRANT
57 103 366624349 1 PAG 1:927056 IX GRANT
57 103 366624349 2 PAG 1:573725 IX GRANT
57 103 1477580302 0 TAB IS GRANT
57 103 366624349 4 PAG 1:607421 IX GRANT
57 103 1938105945 0 TAB IS GRANT
57 103 366624349 3 PAG 1:582200 IX GRANT
57 103 366624349 4 KEY (da0106a2694d) X GRANT
57 103 366624349 5 PAG 1:617162 IX GRANT
57 103 0 0 MD 5(1:0:0) Sch-S GRANT
57 103 366624349 5 KEY (1d3df3b848ca) X GRANT
58 76 0 0 DB S GRANT
59 103 0 0 DB S GRANT
60 103 0 0 DB S GRANT
61 103 0 0 DB S GRANT
62 103 0 0 DB S GRANT
63 103 0 0 DB S GRANT
66 42 0 0 DB S GRANT
66 1 1131151075 0 TAB IS GRANT
67 42 0 0 DB S GRANT
70 103 0 0 DB S GRANT