September 28, 2020 at 11:26 am
The following Error occurs while updating extended property of a table in SQL Server 2008 R2.
What is the reason for this error and how to find who is causing lock?
===================================
Alter failed for Table 'dbo.Ticket'. (Microsoft.SqlServer.Smo)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.4000.0+((KJ_PCU_Main).120628-0827+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Alter+Table&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
at Microsoft.SqlServer.Management.Smo.Table.Alter()
at Microsoft.SqlServer.Management.SqlManagerUI.ExtendedProperties.OnRunNow(Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.PanelExecutionHandler.Run(RunType runType, Object sender)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.DoPreProcessExecutionAndRunViews(RunType runType)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.ExecuteForSql(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.SqlMgmtTreeViewControl.Microsoft.SqlServer.Management.SqlMgmt.IExecutionAwareSqlControlCollection.PreProcessExecution(PreProcessExecutionInfo executionInfo, ExecutionMode& executionResult)
at Microsoft.SqlServer.Management.SqlMgmt.ViewSwitcherControlsManager.RunNow(RunType runType, Object sender)
===================================
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(StringCollection sqlCommands, ExecutionTypes executionType)
at Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQuery(StringCollection queries)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.ExecuteNonQuery(StringCollection queries, Boolean includeDbContext)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplFinish(StringCollection alterQuery, ScriptingOptions so)
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImplWorker()
at Microsoft.SqlServer.Management.Smo.SqlSmoObject.AlterImpl()
===================================
Lock request time out period exceeded. (.Net SqlClient Data Provider)
------------------------------
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.6000&EvtSrc=MSSQLServer&EvtID=1222&LinkId=20476
------------------------------
Server Name: PC\SQLEXPRESS,25111
Error Number: 1222
Severity: 16
State: 56
Procedure: sp_updateextendedproperty
Line Number: 36
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException)
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteNonQuery(String sqlCommand, ExecutionTypes executionType)?
September 28, 2020 at 11:37 am
You can use my beta_lockinfo to find out about locks and blocking in the system, http://www.sommarskog.se/sqlutil/beta_lockinfo.html.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
September 28, 2020 at 3:13 pm
Would you provide the actual T-SQL statement you were attempting to execute?
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 28, 2020 at 3:23 pm
Scott, with that kind of stack dump, it is clear that "IT Researcher" is working from a GUI and not a query window. In any case, the error details makes it clear what is going on. (And the dump also tells us which system procedure that is being invoked.)
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 6, 2020 at 1:32 pm
We are using dot net Application. When application opened SQL connection will be opened and it will be closed when application closed. The Extended property is updated using application but not using begin/commit statements in our code. Still Getting the Lock error.
October 6, 2020 at 1:55 pm
So have you tried troubleshooting the issue with my beta_lockinfo yet? http://www.sommarskog.se/sqlutil/beta_lockinfo.html.
There is no one here can answer what is going on without access to your system - or troubleshooting information that you share.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
October 7, 2020 at 11:11 am
We have added stored procedure betalockinfo , error not repeated but we have already troubleshooted using sp_who2,sp_who3 and using blocked by handle we were able to locate pc and closed application and solved the problem.
we are trying to find reason, since we are not using commit in our code, how it is locked for long time?
October 7, 2020 at 12:46 pm
Obviously, we cannot answer any questions without knowledge of what is going on. Wait until you have the issue again and use my beta_lockinfo and share the output so we can look at it.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply