Well, I have two separate client bases in SQL Server 2008, and both are using the same system, which uses BDE to connect.
I have an application Scripter to do SQL queries on the databases.
Problem
On both bases, I run select * from TABELA
through Scripter and try to get into the system, I get it without problems.
But if I run select * from TABELA order by COLUNA desc
, and try to get into the system, on one basis I can enter, and not another.
I am using sp_WhoIsActive
to try to find the problem, and I have identified that in both databases, SELECT
is ASYNC_NETWORK_IO
.
On the basis that I can not log in says SELECT
is blocking INSERT
.
Attempts
Ihavetriedtorecreateindex,shirinkofthedatabaseandnothing.Icomparedthebases,andbothhavethesameconfiguration.
Observations
- Ihaveidentifiedthatthedatabasethatgenerates
lock
,tempdb_allocations
andtempdb_current
arezeroed,doesithavesomethingtodo? - Anotherdetailisthatinthedatabasethatdoesnotgeneratelock,SpaceAvailableislarge,andinthedatabasethatgenerateslockisalmost0zero)asshownbelow
Donotgeneratelock
Geralock
Doubt
What can you do to identify the reason for the error?
Additional data (02/20/2018 at 13:25)
I circled 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