Microsoft sql server ошибка 1222

I am working in a database where I load data in a raw table by a data loader. But today the data loader got stuck for unknown reasons. Then I stopped the data loader from windows task manager. But then I again tried to load data in the raw table but found its locked and I can’t do any operation on it. I tried restarting SQL Server service but it was not resolved. And I have no permission to kill processes on this server.

Below is the message showed by SQL Server.

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.NamedSmoObject.RenameImplWorker(String
newName)
at Microsoft.SqlServer.Management.Smo.NamedSmoObject.RenameImpl(String
newName)

===================================

Lock request time out period exceeded. Either the parameter @objname
is ambiguous or the claimed @objtype (OBJECT) is wrong. (.Net
SqlClient Data Provider)

Server Name: 162.44.25.59
Error Number: 1222
Severity: 16 State: 56
Procedure: sp_rename Line Number: 282

My SQL Server version is 2008 R2.

halfer's user avatar

halfer

19.9k17 gold badges100 silver badges187 bronze badges

asked Nov 24, 2011 at 14:35

user960340's user avatar

3

In the SQL Server Management Studio,
to find out details of the active transaction, execute following command

DBCC opentran()

You will get the detail of the active transaction, then from the SPID of the active transaction, get the detail about the SPID using following commands

exec sp_who2 <SPID>
exec sp_lock <SPID>

For example, if SPID is 69 then execute the command as

exec sp_who2 69
exec sp_lock 69

Now , you can kill that process using the following command

KILL 69

starball's user avatar

starball

21.1k7 gold badges47 silver badges255 bronze badges

answered Feb 28, 2014 at 10:06

AbdulRahman Ansari's user avatar

2

It’s been a while, but last time I had something similar:

ROLLBACK TRAN

or trying to

COMMIT

what had allready been done free’d everything up so I was able to clear things out and start again.

answered Nov 25, 2011 at 15:18

shawty's user avatar

shawtyshawty

5,7292 gold badges37 silver badges71 bronze badges

2

To prevent this, make sure every BEGIN TRANSACTION has COMMIT

The following will say successful but will leave uncommitted transactions:

BEGIN TRANSACTION
BEGIN TRANSACTION
<SQL_CODE?
COMMIT

Closing query windows with uncommitted transactions will prompt you to commit your transactions. This will generally resolve the Error 1222 message.

codingbiz's user avatar

codingbiz

26.2k8 gold badges59 silver badges96 bronze badges

answered Jan 27, 2016 at 22:01

Paul Totzke's user avatar

Paul TotzkePaul Totzke

1,47017 silver badges33 bronze badges

I had these SQL behavior settings enabled on options query execution: ANSI SET IMPLICIT_TRANSACTIONS checked. On execution of your query e.g create, alter table or stored procedure, you have to COMMIT it.

Just type COMMIT and execute it F5

marc_s's user avatar

marc_s

734k176 gold badges1332 silver badges1460 bronze badges

answered Jun 27, 2019 at 13:38

Jerry Iriri's user avatar

In my case, I was trying to disable a trigger on a table when I received error 1222 «Lock request time out period exceeded.»

I followed suggestions in this answer:

  1. Open two query windows in SSMS.
  2. In the first, type/paste the command that is timing out (due to a lock). In the lower right hand corner of SSMS, you should see the username and (in parentheses) the SPID of the connection you’re using. Note the SPID of this query window connection. Don’t execute this query just yet.
  3. In the second query window, type/paste SELECT * FROM sysprocesses WHERE spid = <SPID you noted in step 2>
  4. Execute the first query that is timing out, and while it is executing (but before it times out) switch over to the second query window and execute it (the SELECT * from sysprocesses... one)
  5. You should get some results in the results pane. Look at the ‘blocked’ field in the results. In my case, it contained the SPID of the process that was locking the table.
  6. Research the locking process further by executing SELECT * FROM sysprocesses WHERE spid = <SPID from the ‘blocked’ field in step 5>.
  7. If the locking process can be safely terminated, kill it with kill <locking SPID>

answered May 25, 2021 at 21:30

Baodad's user avatar

BaodadBaodad

2,4252 gold badges38 silver badges39 bronze badges

Источник

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

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Sign up

Источник

Stuck with an error 1222 microsoft sql server? Bobcares is at your service!

The error 1222 is a common error when you are working in Microsoft SQL Server Management Studio. It often pops up when you are attempting to view tables, procedures, or tables in object explorer. Find out how our Support engineers helped out a customer with their SQL server 1222 error recently.

What is MS SQL server error 1222?

The error is a result of a longer query wait time than lock timeout settings. The lock timeout indicates the time spent waiting for a backend resource to be available.

Fortunately, the Support Engineers at Bobcares have a quick fix for this.

Tips to resolve the pesky error 1222 Microsoft SQL server

Now that you have got an idea about why error 1222 pops up. Let’s dive into resolving the issue once and for all.

  1. Our engineers use sp_who2 to check the currently established sessions in the database as well as any sessions with high CPU usage, blocking, high I/O usage, or sessions with multiple entries for identical SPID. This may be the cause behind lock time-outs.
  2. You can change the lock time-out period by running the following command: 
     SET LOCK_TIMEOUT timeout_period

    The timeout_period indicates the number of milliseconds allowed to pass before a locking error is returned by Microsoft SQL Server. Its default value is  -1, which specifies no time-out period. Changing the lock time-out period will prevent error 1222 from occurring frequently.

[Looking for assistance with Server Management? We are just a click away]

Conclusion

This easy fix to resolve Microsoft SQL server management studio error 1222 tip comes from our top experts at Bobcares.
If you have any queries about your SQL server or server management, do not hesitate to contact us. We have been helping customers successfully resolve issues related to server management for a long time.

PREVENT YOUR SERVER FROM CRASHING!

Never again lose customers to poor server speed! Let us help you.

Our server experts will monitor & maintain your server 24/7 so that it remains lightning fast and secure.

GET STARTED

Источник

Posted On 2018-12-29

При попытке посмотреть свойства базы данных SQL возникает ошибка SQL Server Error 1222. Какая-то транзакция блокирует базу.

Я решил так

В SQL Server Management Studio, чтобы узнать подробности активной транзакции, выполните следующую команду

DBCC opentran()

Вы получите подробную информацию о активной транзакции, затем из SPID активной транзакции, получите подробную информацию о SPID, используя следующие команды

exec sp_who2 <SPID>
exec sp_lock <SPID>

Например, если SPID равен 69, тогда выполните команду как

exec sp_who2 69
exec sp_lock 69

Теперь вы можете убить этот процесс, используя следующую команду

KILL 69
Источник
  • Remove From My Forums

  • Question

  • Hello experts.  Late Friday I started getting these messages.

    I don’t think I’ve ever seen this before, but I’ve been getting this all day long.  All I can think of, is that on Friday, late in the day, I Indexed 4 tables.  AFAIK, nothing else changed.  Now, I can’t really do anything.  Even if I
    right-click Tables > Refresh, I get that error message.  I don’t think I even had to Index the Tables.  The largest one is just over 1/2 million records; the other three tables are about 10,000 records, and grow by a couple hundred per day.

    Does anyone know how I can get rid of these messages, and get my DB back to normal?

    Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

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

Источник

Понравилась статья? Поделить с друзьями:
  • Microsoft solitaire collection ежедневные задания ошибка загрузки
  • Microsoft setup bootstrapper office 2016 ошибка
  • Microsoft setup bootstrapper office 2013 ошибка
  • Microsoft office ошибка 0 2035
  • Microsoft office обновляется и выдает ошибку