Microsoft sql server ошибка 1222

DBCC opentran()

exec sp_who2 <SPID>
exec sp_lock <SPID>

exec sp_who2 69
exec sp_lock 69

KILL 69

2

ROLLBACK TRAN

COMMIT

2

Search code, repositories, users, issues, pull requests…

Answers

• Could it be that you changed Tools/Options/Query Execution Execution Timeout to something other than 0?

  ---

  • Marked as answer by

    Wednesday, March 12, 2014 2:14 PM

• I ran this:
  DBCC OPENTRAN(London_Data)

  I get this:
  Transaction information for database ‘London_Data’.

  Oldest active transaction:
      SPID (server process ID): 52
      UID (user ID) : -1
      Name          : DROPOBJ
      LSN           : (3039:5865:421)
      Start time    : Mar 10 2014  3:01:20:687PM
      SID           : 0x4140dd796257644b8692bc55244ae28e
  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  Now what?

  ---

  Hello,

  DROPOBJ here refers to object being dropped.Its is like a delete operation on table ,I guess I am not sure.I even dont know what would happen if you kill this transaction.Killing it will put it into rollback state and you might get relief from error of lock
  timeout but I dont know how much time transaction rollback will take

  Can you do DBCC INPUTBUFFER(52) and post query here.Lets see if we can kill it.

  ---

  Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  • Marked as answer by
    ryguy72
    Wednesday, March 12, 2014 2:14 PM

• Hello,

  This message implies that whatever transaction is running is not able to take lock on resource to perform the activity.Today is Monday( Issue started on friday) ,so are you still getting this issue ?

  What  version or SQL Server we are talking about.Can you click on new query and query DMV sys.dm_tran_locks filter your table name use below link for detailed query.

  http://technet.microsoft.com/en-us/library/ms190345.aspx

  Look for the locks and blocking any .Any information in errorlog

  ---

  Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers

  • Marked as answer by
    ryguy72
    Wednesday, March 12, 2014 2:14 PM

• Apparently you have some doomed transactions which were not closed. Is it your local SQL Server on your local machine? If Shanky’s advices would not work for you, then make backup of all your databases first. Then I would probably just re-start the server
  but bear in mind it may lead to other problems (potentially in the worse case scenario you may need to re-install).

  ---

  • Marked as answer by
    ryguy72
    Wednesday, March 12, 2014 2:14 PM

• What does SELECT @@Trancount return ?

  ---

  • Marked as answer by
    ryguy72
    Wednesday, March 12, 2014 2:14 PM

• Hello ryguy,

  When will you will get error like — «There are uncommitted transactions. 
  Do you wish to commit these before closing the window?» that means query analyzer is configure to automatically start a transaction when running queries. It depends on you what you want?
   “COMMIT» before you close the query analyzer, or to «rollback, before you close. If you say «no», all the UPDATE, DELETE, INSERT etc are lost since the last BEGIN TRANSACTION (note, statements like CREATE … issue an implicit transaction…)

  You can change that setting in SQL Query Analyzer > Tools > Options. On the Connection Properties tab, uncheck “Set Implicit_transactions»

  Or Using simply write —
  SET
  IMPLICIT_TRANSACTIONS OFF

  You can use the DBCC OPENTRAN Function to find any uncommitted transactions.

  Regards,

  Naveen Kumar Thandra

  naveenthandra.blogspot.hk

  • Proposed as answer by
    Naveen Kumar Thandra
    Wednesday, March 12, 2014 8:35 AM
  • Unproposed as answer by
    Shanky_621MVP
    Wednesday, March 12, 2014 11:37 AM
  • Marked as answer by
    ryguy72
    Wednesday, March 12, 2014 2:13 PM