Identify which SQL user is posing a lock on the table

Check whether the column does allow NULL value or not. If it does not allow NULL, enable it, or adjust your query. I think you're trying to put NULL to the non-null column.

Hi,

No, its not the case, I know for sure its someone running some query against this table that is preventing me from create new fields.

Any idea how to find out which user and what query?

thanks,
Ben

The user should be you and the statement is a ALTER TABLE (ADD COLUMN ...) statement.

When you're changing a table structure you should be sure that no one is using it so SQL engine can achieve the necessary lock to make the changes in the table.

@Vitor,

When you're changing a table structure you should be sure that no one is using it

Absolutely, and therefore my question goes, how can I identify who is currently using it?

Thanks,
Ben

Open a new query window and run sp_who2 command. This will return all sessions running in the current MSSQL instance.
Or you can use SSMS gui to check the Activity (right-click on Instance name and select Activity Monitor then open the Processes tab to see all processes running).

@Vitor,

The problem is that those utilities show me all active processes.

I need a way to filter only those processes accessing a certain table.

Thanks,
Ben

You can use this script (replace tablename with the name of the table that you want to find):

SELECT p.spid, p.blocked, r.blocking_session_id, DB_NAME(p.dbid) dbname, p.loginame, p.login_time, p.last_batch, p.status, p.hostname, p.program_name, p.hostprocess, c.client_net_address, t.text
FROM sys.sysprocesses p
	INNER JOIN sys.dm_exec_connections c ON p.spid=c.session_id
	LEFT JOIN sys.dm_exec_requests r ON p.spid=r.session_id
	CROSS APPLY sys.dm_exec_sql_text(c.most_recent_sql_handle) AS t
WHERE text LIKE '%tablename%'

Select allOpen in new window

Excellent Vitor!

Just one question, this only works when running against master DB, while on the other DB's I get the error message "Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '.'."

However I need it to run while connected to the production DB (As would like to link that to my FE applications)

Tried adding Master.sys... but that didnt help, any way to get this work?

Thanks,
Ben

Works great, Thank you!

BTW, does this list all connections or only those who are blocking (preventing the adding a field to that table)?

Lists all but you have the blocked column that will show which process is blocking. If zero then no blocks.

Thanks again.