Identify which SQL user is posing a lock on the table

Hi Experts,

I am trying to add a field to a table (thru ssms) and I get the attached message.

How can I identify which user and SQL statement is causing it?
Untitled1.png
LVL 5
bfuchsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
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.
0
bfuchsAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

bfuchsAuthor Commented:
@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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
0
bfuchsAuthor Commented:
@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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
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%'

Open in new window

0
bfuchsAuthor Commented:
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
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Strange, that should run in any database. Anyway you can always say to SQL Server to change to master database before running the script:
USE master
GO

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%'

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bfuchsAuthor Commented:
Works great, Thank you!

BTW, does this list all connections or only those who are blocking (preventing the adding a field to that table)?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
Lists all but you have the blocked column that will show which process is blocking. If zero then no blocks.
0
bfuchsAuthor Commented:
Thanks again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.