Locking

[VBXL]

Hi

When I try to apply a index on a table (about 10 million records) , the table locks and the index does not apply.

I get the following error message thousands of times.

EXT 1:2789280 X GRANT
EXT 1:2582280 X GRANT 6
EXT 1:2590448 X GRANT 6
EXT 1:2586400 X GRANT

There is only one user who has access to the database and it happens on all tables with millions of records.

any idea why ?

 

[TomKane]

Do you have the correct admin rights?

You could try doing a select * from sysprocesses or an sp_who while you're aplying the index it might give you an idea of what it is that locking you out.


Take a look at this

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_perfmon_6ofn.asp

 

[VBXL]

hI

I found this (PAGEIOLATCH_SH)
when I ran this query

select * from sysprocesses


what is this ????

 

[TomKane]

It has something to do with the last wait type - this may help - I'm also going to do a faq on the sysprocesses table but the definition of the lastwaittype field is as follows:

lastwaittype nchar(32) A string indicating the name of the last or current wait type.

Troubleshooting Locking
Two locking problems that an application may encounter are deadlocking and blocking.
Deadlocking
Deadlocking is a condition that can occur on any system with multiple users, not only on a relational database management system (RDBMS). A deadlock occurs when two users (or sessions) have locks on separate objects and each user wants a lock on the other’s object. Each user waits for the other to release their lock. Microsoft® SQL Server™ detects when two connections have gotten into a deadlock. One of the connections is chosen as a deadlock victim. The connection’s transaction is rolled back and the application receives an error.
If deadlocks become a common occurrence to the point that their rollbacks are causing excessive performance degradation, you may need to perform more in-depth investigation. Use trace flag 1204. For example, this command starts SQL Server from the command prompt and enables trace flag 1204.
c:\mssql7\binn\sqlservr -T1204 /dc:\mssql7\data\master.mdf
  
All messages will now appear in the console screen where SQL Server was started and in the error log.
Deadlocking can also occur when using distributed transactions. For information about resolving deadlocks with distributed transactions, see Transactions Architecture.
Blocking
An unavoidable characteristic of any lock-based concurrent system is that blocking may occur under some conditions. Blocking happens when one connection holds a lock and a second connection wants a conflicting lock type. This forces the second connection to either wait or block on the first.
In this discussion, the term “connection” refers to a single logged-on session of the database. Each connection appears as a system process ID (SPID). Each of these SPIDs is often referred to as a process, although it is not a separate process context in the usual sense. Rather, each SPID consists of the server resources and data structures necessary to service the requests of a single connection from a given client. A single client application may have one or more connections. From the perspective of SQL Server, there is no difference between multiple connections from a single client application on a single client computer and multiple connections from multiple client applications or multiple client computers. One connection can block another connection, regardless of whether they emanate from the same application or separate applications on two different client computers.
To eliminate deadlocking or blocking problems, contact your system administrator. The system administrator should check the waittype, waittime, lastwaittype, and the waitresource columns of sysprocesses to see what activities each SPID is performing.

 

[TomKane]

faq183-4179