February 27, 2016 at 1:17 am
Hi,
We were running updates to SQL Server databases but it got terminated because of a deadlock.
Looks like the deadlock is happened because of a opened session in management studio. Please advise what caused this deadlock?
2016-02-24 19:45:38.48 spid21s deadlock-list
2016-02-24 19:45:38.48 spid21s deadlock victim=process561d288
2016-02-24 19:45:38.48 spid21s process-list
2016-02-24 19:45:38.48 spid21s process id=process561d288 taskpriority=0 logused=10000 waitresource=DATABASE: 12 waittime=882 schedulerid=3 kpid=4160 status=suspended spid=89 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-02-24T19:17:39.507 lastbatchcompleted=2016-02-24T19:17:39.507 clientapp=Microsoft SQL Server Management Studio - Query hostname=SQL01 hostpid=492 loginname=abc\sqladmin isolationlevel=read committed (2) xactid=0 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
2016-02-24 19:45:38.48 spid21s executionStack
2016-02-24 19:45:38.48 spid21s inputbuf
2016-02-24 19:45:38.48 spid21s process id=process56134c8 taskpriority=0 logused=10000 waitresource=DATABASE: 12 waittime=1173 schedulerid=2 kpid=2496 status=suspended spid=61 sbid=0 ecid=0 priority=0 trancount=1 lastbatchstarted=2016-02-24T19:45:35.300 lastbatchcompleted=2016-02-24T19:45:35.300 clientapp=.Net SqlClient Data Provider hostname=APPSRV1 hostpid=1360 loginname=ABC isolationlevel=read committed (2) xactid=410782 currentdb=12 lockTimeout=4294967295 clientoption1=671219744 clientoption2=128056
2016-02-24 19:45:38.48 spid21s executionStack
2016-02-24 19:45:38.48 spid21s frame procname=unknown line=39 stmtstart=3206 stmtend=3904 sqlhandle=0x03000a00649cc36429847c019da300000100000000000000
2016-02-24 19:45:38.48 spid21s unknown
2016-02-24 19:45:38.48 spid21s inputbuf
2016-02-24 19:45:38.48 spid21s Proc [Database Id = 10 Object Id = 1690541156]
2016-02-24 19:45:38.48 spid21s resource-list
2016-02-24 19:45:38.48 spid21s databaselock subresource=FULL dbid=12 dbname=unknown lockPartition=0 id=lock57b2280 mode=S
2016-02-24 19:45:38.48 spid21s owner-list
2016-02-24 19:45:38.48 spid21s owner id=process56134c8 mode=S
2016-02-24 19:45:38.48 spid21s waiter-list
2016-02-24 19:45:38.48 spid21s waiter id=process561d288 mode=X requestType=wait
2016-02-24 19:45:38.48 spid21s databaselock subresource=FULL dbid=12 dbname=unknown lockPartition=0 id=lock57b2280 mode=S
2016-02-24 19:45:38.48 spid21s owner-list
2016-02-24 19:45:38.48 spid21s owner id=process561d288 mode=S
2016-02-24 19:45:38.48 spid21s waiter-list
2016-02-24 19:45:38.48 spid21s waiter id=process56134c8 mode=X requestType=wait
February 27, 2016 at 9:21 am
2016-02-24 19:45:38.48 spid21s process id=process561d288 taskpriority=0 logused=10000 waitresource=DATABASE: 12 waittime=882 schedulerid=3 kpid=4160 status=suspended spid=89 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-02-24T19:17:39.507 lastbatchcompleted=2016-02-24T19:17:39.507 clientapp=Microsoft SQL Server Management Studio - Query hostname=SQL01 hostpid=492 loginname=abc\sqladmin isolationlevel=read committed (2) xactid=0 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
In above, it says about SQL Server management query. How to find what query was running?
February 27, 2016 at 2:44 pm
hello experts, can anyone please help me to find out what query was executing in management studio in this deadlock?
thanks
February 27, 2016 at 4:07 pm
Someone else might be able to glean a whole lot more from what you listed but, for me, there's not a whole lot of information about the Query that was running that became the victim. From your description, I'm assuming that was the UPDATE(s) that you were running. About the only really useful information about this "victim" was that it was running from an SSMS session on Database_ID = 12.
The other end of the deadlock was a proc on whatever database lives on Database_ID = 10 and the object ID for that proc is 1690541156. You'd have to go find that in sys.objects. It does tell you that line 39 of that proc was responsible. Obviously, this proc (which is on database 10) is doing something to database 12, where your query was running.
From the sound of it, you simply ran an update (I'm assuming it was something reasonably large) at an unfortunate time.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 27, 2016 at 4:36 pm
Thanks Jeff,
I was not running any queries in SSMS. We were running bunch of sql scripts which will do some updates (it's a vendor application, will get an .exe which will do these updates). At the end of running this .exe, the process got terminated with below error:
Step [Update Terminate] failed with message: ALTER DATABASE failed because a lock could not be placed on database 'SQL_PROD'. Try again later. ALTER DATABASE statement failed.
when I looked at error log, at the same time this deadlock is there. But I was not running anything in SSMS.
From the deadlock graph, it's clear that the victim is the one running in SSMS. So the .exe updates should go fine but it got terminated. Why was it got terminated even-though it was not the victim?
and also I'm curious to know what was running in SSMS. Because I'm 100 % sure that nothing is running in SSMS. Is this knid of bug as per the below link
Thanks so much
I was able to generate the XML version deadlock. Please see the attachment.
Please help
February 28, 2016 at 7:45 am
gary1 (2/27/2016)
Thanks Jeff,I was not running any queries in SSMS. We were running bunch of sql scripts which will do some updates (it's a vendor application, will get an .exe which will do these updates). At the end of running this .exe, the process got terminated with below error:
Step [Update Terminate] failed with message: ALTER DATABASE failed because a lock could not be placed on database 'SQL_PROD'. Try again later. ALTER DATABASE statement failed.
when I looked at error log, at the same time this deadlock is there. But I was not running anything in SSMS.
From the deadlock graph, it's clear that the victim is the one running in SSMS. So the .exe updates should go fine but it got terminated. Why was it got terminated even-though it was not the victim?
and also I'm curious to know what was running in SSMS. Because I'm 100 % sure that nothing is running in SSMS. Is this knid of bug as per the below link
Thanks so much
I was able to generate the XML version deadlock. Please see the attachment.
Please help
The XML makes it a bit more clear and, apologies, but I misread the previous post. It's not a run in SSMS that had the failure. It's something from your app server and it gave the SQLHandle for the code which, like I said, is probably long gone.
Here's the section of the XML that I'm talking about...
<process id="process56134c8" taskpriority="0" logused="10000" waitresource="DATABASE: 12 " waittime="1153" schedulerid="2" kpid="2496" status="suspended" spid="61" sbid="0" ecid="0" priority="0" trancount="1" lastbatchstarted="2016-02-26T19:45:35.300" lastbatchcompleted="2016-02-26T19:45:35.300" clientapp[font="Arial Black"]=".Net SqlClient Data Provider" [/font]hostname="[font="Arial Black"]APPSRV1[/font]" hostpid="1360" loginname="ABC" isolationlevel="read committed (2)" xactid="410782" currentdb="12" lockTimeout="4294967295" clientoption1="671219744" clientoption2="128056">
<executionStack>
<frame procname="" line="39" stmtstart="3206" stmtend="3904" [font="Arial Black"]sqlhandle="0x03000a00649cc36429847c019da300000100000000000000" [/font]/>
</executionStack>
Also, you said you were doing "UPDATES", which does NOT mean ALTER DATABASE to me. Why on this good green Earth have you given an app privs to ALTER DATABASE directly? To me, that's a major security violation and could end up with you reading about yourselves getting hacked in tomorrow's news.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2016 at 9:54 am
Thank you very much Jeff,
It's a vendor application and we don't own their code and the application update will happens through exe.
My main worry is to find out if something was running in SSMS. Because deadlock graph has mentioned that something running in SSMS was the deadlock victim. And abc\sqladmin is the sqladmin user which I use and SQL01 is the SQL Server node. Please help me understand what is running in SSMS under abc\sqladmin user? Because I need to answer what was running in SSMS with my account. But I'm 100 % sure I was not even logged into SSMS.
2016-02-24 19:45:38.48 spid21s process id=process561d288 taskpriority=0 logused=10000 waitresource=DATABASE: 12 waittime=882 schedulerid=3 kpid=4160 status=suspended spid=89 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-02-24T19:17:39.507 lastbatchcompleted=2016-02-24T19:17:39.507 clientapp=Microsoft SQL Server Management Studio - Query hostname=SQL01 hostpid=492 loginname=abc\sqladmin isolationlevel=read committed (2) xactid=0 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
We rolled back the databases to the back up we took before the update and re applied the updates and it went fine this time.
But need to find out what was caused this deadlock and how we can avoid next time
February 28, 2016 at 12:07 pm
gary1 (2/28/2016)
Thank you very much Jeff,It's a vendor application and we don't own their code and the application update will happens through exe.
My main worry is to find out if something was running in SSMS. Because deadlock graph has mentioned that something running in SSMS was the deadlock victim. And abc\sqladmin is the sqladmin user which I use and SQL01 is the SQL Server node. Please help me understand what is running in SSMS under abc\sqladmin user? Because I need to answer what was running in SSMS with my account. But I'm 100 % sure I was not even logged into SSMS.
2016-02-24 19:45:38.48 spid21s process id=process561d288 taskpriority=0 logused=10000 waitresource=DATABASE: 12 waittime=882 schedulerid=3 kpid=4160 status=suspended spid=89 sbid=0 ecid=0 priority=0 trancount=0 lastbatchstarted=2016-02-24T19:17:39.507 lastbatchcompleted=2016-02-24T19:17:39.507 clientapp=Microsoft SQL Server Management Studio - Query hostname=SQL01 hostpid=492 loginname=abc\sqladmin isolationlevel=read committed (2) xactid=0 currentdb=12 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
We rolled back the databases to the back up we took before the update and re applied the updates and it went fine this time.
But need to find out what was caused this deadlock and how we can avoid next time
The proc that was running on Database_ID = 10 is the culprit. It gives the object ID so, like I previous said, you should be able to find out what the proc name is from sys.objects and then follow your nose on why it may have been used. It could have been something as simple as a backup job that wouldn't allow exclusive use.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 28, 2016 at 1:09 pm
What Jeff posted twice now is exactly how to find your procedure.
SELECT name FROM sys.databases where database_id = 10;
Then, in your database (id = 10):
SELECT name FROM sys.objects WHERE object_id = 1690541156;
What he posted about the permissions of the login used to connect is also very correct. It may be a vendor application, but the database holds your company's data. If you end up getting hacked and hearing about your company's data breach on the news, do you think saying it was a vendor app will suffice? If it contains any PII, I'd be very careful.
February 28, 2016 at 8:05 pm
Hi Jeff and Ed,
I was able to get procedure name and its code (line 39). But my question is:
1. how to find what was running in SSMS using the login abc\sqladmin? (the deadlock victim)
2. If the query running in SSMS is the deadlock victim, the other process (from application) should go fine. But why it got failed?
Thanks so much for your advise.
February 29, 2016 at 5:26 am
gary1 (2/28/2016)
Hi Jeff and Ed,I was able to get procedure name and its code (line 39). But my question is:
1. how to find what was running in SSMS using the login abc\sqladmin? (the deadlock victim)
2. If the query running in SSMS is the deadlock victim, the other process (from application) should go fine. But why it got failed?
Thanks so much for your advise.
It wasn't running from SSMS. Look at Jeff's post from 2:07 yesterday. The XML he pasted in was from your deadlock graph. It identifies the handle of the SQL that was the victim as coming from APPSRV1. You can query for the statement from sys.dm_exec_sql_text, but it's been almost 5 days since your deadlock occurred so it may no longer be in cache.
February 29, 2016 at 5:29 am
Ed Wagner (2/29/2016)
gary1 (2/28/2016)
Hi Jeff and Ed,I was able to get procedure name and its code (line 39). But my question is:
1. how to find what was running in SSMS using the login abc\sqladmin? (the deadlock victim)
2. If the query running in SSMS is the deadlock victim, the other process (from application) should go fine. But why it got failed?
Thanks so much for your advise.
It wasn't running from SSMS. Look at Jeff's post from 2:07 yesterday. The XML he pasted in was from your deadlock graph. It identifies the handle of the SQL that was the victim as coming from APPSRV1. You can query for the statement from sys.dm_exec_sql_text, but it's been almost 5 days since your deadlock occurred so it may no longer be in cache.
Sorry, I didn't address your second question. It failed because both sessions were waiting for an exclusive lock.
February 29, 2016 at 11:03 am
gary1 (2/28/2016)
Hi Jeff and Ed,I was able to get procedure name and its code (line 39). But my question is:
1. how to find what was running in SSMS using the login abc\sqladmin? (the deadlock victim)
2. If the query running in SSMS is the deadlock victim, the other process (from application) should go fine. But why it got failed?
Thanks so much for your advise.
On #1, go back and read what I wrote after you posted the XML. I said I made a mistake in thinking that it was running from SSMS. If was running from your app server (which both Ed and I have identified) and, as I said, there's no way to identify the exact code that was running because the SQLHandle that points to the code and the code itself have probably been pushed out of memory by now.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply