Mssql ошибка 1222

3

Sign up

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

One other note about event notification for exceptions: I know that at least one of the other types of message it captures is also bogus. It constantly records messages saying that temporary tables are not valid objects. For example, after
executing sp_help I get a series of «errors» saying «Invalid object name ‘#spcnsttab'» and «Invalid object name ‘#spindtab'». That is clearly wrong. It seems like it is picking up some kind of low-level information
that would not otherwise see the light of day because it is not really an error condition. I strongly suspect that is the case with these timeout messages as well.

This I can explain. You can see this also when you run Profiler and capture the Error:Exception and Error:User Message events, as well as the BatchStarting/Completed and StmtStarting/Completed events.

If you run this:

CREATE PROCEDURE bad_sp
PRINT ‘Hoppla!’
—CREATE TABLE #temp(a int NOT NULL)
SELECT * FROM #temp
go
EXEC bad_sp

You will see something like this:

BatchStarting CREATE PROCEDURE …
StmtStarting CREATE PROCEDURE …
Error:Exception  Invalid Object Name ‘#temp’
StmtCompleted CREATE PROCEDURE …
BatchCompleted CREATE PROCEDURE …
BatchStarting EXEC bad_sp
StmtStartting EXEC bad_sp
Error:Exception  Invalid Object Name
StmtStarting Print ‘Hoppla!’
Error:Exception  Invalid Object Name ‘#temp’
Error:UserMessage Invalid Object Name ‘#temp’
BatchCompleted EXEC bad_sp

That is, you get one exception event when the procedure is created, but the error is suppressed in the spirit of deferred name resolution.

When you invoke the procedure, the procedure is compiled and put into cache, the exception occurs anew, but is again suppresed. Only first when the statement is reached you get both the Exception event and the User Message event.

If you try this again, but uncomment the CREATE TABLE statement, you again get two Error:Exception events, but this time you don’t get the third and the User Message event.

So that is how it works. Pretty? Well, deferred name resolution is one of the biggest misfeatures in SQL Server in my opinion…

Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

• Marked as answer by

  Monday, June 29, 2015 3:28 AM

• Remove From My Forums

• Question

• Hi All,
  Lock request time out period exceeded, error 1222" will appear when I try to expand the tables list in database in SQL Server Management Studio 2012 (Enterprises).
  I am creating partition on my table which is having more than 200 million records. Initially I thought this is happening due to ONLINE=OFF option is set for cluster index which will partition the existing table. But this is not true. I tried with ONLINE=ON & still I can see same problem.
  
  
  
    
  
  
                         
    
  
  
  
  Below is the index which I am running to Partition my table:
  USE [MY_DB]
  GO
  BEGIN TRANSACTION
  CREATE CLUSTERED INDEX [TRANS_PS_635112970545378233] ON [dbo].[Table]
  (
  	[Date]
  )WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [MY_PS]([Date])
  DROP INDEX [TRANS_PS_635112970545378233] ON [dbo].[Table]
  COMMIT TRANSACTION
  After running this query which is taking around 9-10 hours I am getting error: "Lock request time out period exceeded. (Microsoft SQL Server, Error: 1222)"  when I try to expand the tables list in database in SQL Server Management Studio 2012 (Enterprises) 
  
  
  
    
  
  
                         
    
  
  
  
  My DBA suggested that need to snapshot_isolation_state =1 in sys.databases( SELECT * FROM sys.databases) at DB level. I am not sure so looking for opinion.
  http://technet.microsoft.com/en-us/library/ms178534.aspx

  ---

  Thanks Shiven:) If Answer is Helpful, Please Vote

  • Edited by

    Monday, August 5, 2013 7:51 AM

  • Moved by
    Kalman Toth
    Monday, August 5, 2013 7:53 AM
    Not db design