Hi,

When try to execute below statement, i am getting timeout error some times and this is written to execute as a script, so i can not determine at which point it gets timed out.

Declare @ID BIGINT;  
  Select @ID = Max(PMTDID) from TBLT_PaymentModeTransferDetail with (NOLOCK)  
  if @Id IS NOT NULL  
  BEGIN  
   DBCC CHECKIDENT(TBLT_PaymentModeTransferDetail, RESEED, @ID)  
  END  
  ELSE If @ID IS NULL AND Ident_Current('TBLT_PaymentModeTransferDetail') <> (Select IDENT_SEED(TABLE_NAME) AS Seed from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'TBLT_PaymentModeTransferDetail')  
  BEGIN  
   Set @ID = 1404  
   DBCC CHECKIDENT(TBLT_PaymentModeTransferDetail, RESEED, @ID)  
  END  
  ELSE IF 1404 IS NOT NULL AND Ident_Current('TBLT_PaymentModeTransferDetail') = (Select IDENT_SEED(TABLE_NAME) AS Seed from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'TBLT_PaymentModeTransferDetail')  
  BEGIN  
   Select @ID = (IDENT_SEED(TABLE_NAME) -1) from INFORMATION_SCHEMA.TABLES where TABLE_NAME = 'TBLT_PaymentModeTransferDetail'  
   DBCC CHECKIDENT(TBLT_PaymentModeTransferDetail, RESEED, @ID)  
  END  

Thanks,

Rajnikant

SQL Server does not timeout a query.  Your caller must be timing out.  How are you calling this script?

Hi,

Thanks for the reply.

I am calling it from .net application, which have connection string and connection timeout has not been set.

Thanks,

Rajnikant

Please see:

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.commandtimeout%28v=vs.110%29.aspx?f=255&MSPPError=-2147217396

Also see the "Note" section under Remarks.

If you can accept that the query runs for some time, change the CommandTimeout property on the Command object to 0, which means "wait forever".

If are impatient and you think that the scriptis taking too long time, I would suspect that you are victim to blocking. Use sp_who2 to find out. If you see a value in the Blk column, this spid is blocking the spid on this line.

SQL server throws timeout error when any one of the following causes occurs

before setting up connectionTimeout = 0 in your code (not recommend) or Connection Timeout = 0 in connection string

try to run

EXEC sp_updatestats on your database to update stats. 

SQL Server does NOT send a timeout.  .NET reports a timeout for those reasons. 

The reasons you posted are caused by CONNECTION timeout, not COMMAND timeout.  The actual error would indicate which is causing the issue.