Unspecified error occurred on sql server connection may have been terminated by the server

Error: Unspecified error occurred on SQL Server

January 21, 2009 at 2:19 am

I have job in sql server which restores database backup and permissions thereafter.

Sometime one step in this job fails with below message. This step restores users permissions on the restored database.

Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0). The step failed.

Please help. I don’t find any specific error message in log file also.

January 22, 2009 at 6:55 am

I have known unhelpful messages but this has gotta be the worst

no advice from me, but a certain amount of moral support

January 22, 2009 at 7:12 am

Nothing in the SQL error log at all?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

November 2, 2009 at 1:26 am

The same situation, and really, nothing at all on this errorlog instance msg.

Except one thing: when the process of restoring started, the sqlagent log contains the following msg till the end of restoring:

2009-11-01 12:30:02 — ! [298] SQLServer Error: 18456, Login failed for user ‘DIAMONDsqladmin64’. [SQLSTATE 28000]

2009-11-01 12:30:02 — ! [298] SQLServer Error: 4060, Cannot open database «bankDoc» requested by the login. The login failed. [SQLSTATE 42000]

November 2, 2009 at 1:30 am

Guys, I found the culprit.

One script in a job was kiiling connections. That was useless script :).

Viewing 5 posts — 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Источник

Unspecified error occurred on sql server connection may have been terminated by the server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

we have created a job to rebuild indices, this job is failed to complete, could you tell the solutions?

Domainuser. Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0) Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed.

Answers

You need to look in the SQL Server errorlog. An internal error in SQL Server has aborted the execution, and you should find a stack dump there. It is not likely that you will be able to take any corrective actions from the errorlog, but it may give an indication if the dump is to due to corruption.

It is very likely that you will need to open a case with Microsoft, and if the issue is indeed a bug in SQL Server, the case should be of no cost for you.

However, I would recommend that prior to openning case you check that you are on the latest service pack (you could post the output of SELECT @@version and we can tell you), and if time permits run DBCC CHECKDB on the database.

Источник

Unspecified error occurred on sql server connection may have been terminated by the server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Asked by:

Question

The following error messages received during Index Maint job.

«Msg 0, Sev 0, State 1: Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000]»

This error captured in after enabling the logging of the job to a txt file. Just before the error the UPDATE STATISTICS statement is executed. However after the error ALTER INDEX statement has also executed and then job failed. Nothing in SQL Error logs or Windows Event log at that time.

Below is the SQL and OS version.

Microsoft SQL Server 2008 R2 (SP2) — 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

Источник

Unspecified error occurred on sql server connection may have been terminated by the server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

we have created a job to rebuild indices, this job is failed to complete, could you tell the solutions?

Domainuser. Unspecified error occurred on SQL Server. Connection may have been terminated by the server. [SQLSTATE HY000] (Error 0) Cannot continue the execution because the session is in the kill state. [SQLSTATE HY000] (Error 596). The step failed.

Answers

You need to look in the SQL Server errorlog. An internal error in SQL Server has aborted the execution, and you should find a stack dump there. It is not likely that you will be able to take any corrective actions from the errorlog, but it may give an indication if the dump is to due to corruption.

It is very likely that you will need to open a case with Microsoft, and if the issue is indeed a bug in SQL Server, the case should be of no cost for you.

However, I would recommend that prior to openning case you check that you are on the latest service pack (you could post the output of SELECT @@version and we can tell you), and if time permits run DBCC CHECKDB on the database.

Источник

Unspecified error occurred on sql server connection may have been terminated by the server

This forum has migrated to Microsoft Q&A. Visit Microsoft Q&A to post new questions.

Answered by:

Question

We have a long running SSIS package that failed this morning which failed with the following error:

«Unspecified error occured on Sql Server. Connection may have been terminated by the server». Possible failure reasons: Problems with the query.»Result Set» property not set correctly, parameters not set correctly, or connection not established correctly.

Database was at Standard S3 level.

Is Azure sql connection not reliable enough for long running process?

Answers

I raised a support ticket to microsoft support and got my doubt clear that Azure database connection is not reliable for long running transactions. Below is the response from microsoft support.

Connections idle for more than 5 minutes may be closed by SQL Azure. If you are performing lots of DML operations try to perform them on batches.

When throttling occurs connections get affected. Below queries will help you monitor successful/terminated/throttled connections.

where start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)

order by start_time desc

where event_type <> ‘connection_successful’

and start_time >= CAST(FLOOR(CAST(getdate() AS float)) AS DATETIME)

order by start_time desc

Please define an event handler error for the whole SSIS package. This should give you more detailed information about the error.

Are you sure the package is able to authenticate to SQL Azure?

Have you created a variable of type object and assigned the result set data to it?

Hope this helps.

• Edited by Alberto Morillo MVP Tuesday, September 19, 2017 4:29 AM

Thanks Alberto for your reply.

I can’t find any record of terminated/throttled connection in the first query and no record in the second query.

Yes, the package was able to authenticate to Sql Azure. SSIS package that failed actually executes a lot of dml statements and it runs for 4-5 hours. The package failed after running for 11 mins.

I will put in more error handling but it’s strange though that when I re-ran the SSIS this morning, it ran without any issues.

Источник

Let us consider the following connection string which connects sql server through odbc driver as

"Driver={SQL Server Native Client 10.0};Server=xxx.xx.xx.xx,xxxx;Database=mydata;Uid=xx;Pwd=xxxxxx;"

Database type: Sql server

Driver:sql server-ODBC native client 10.0

Provider type: System.Data.Odbc

In which i am executing 50 commands through odbc connection…

In that 50 first 25 in connection string 1 and rest 25 in connection string 2.

There by

when i am executing those command around 35 commands executed well after that i got the error as

ERROR [HY000] [Microsoft][SQL Server Native Client 10.0]Unspecified error occurred on SQL Server. Connection may have been terminated by the server. 

for rest all queries executed after that and

Stack trace:

  at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
   at System.Data.Odbc.OdbcCommand.ExecuteNonQuery()
   at testprovision.CommonDAL.ExecuteNonQuery(String connectionString, CommandType cmdType, String cmdText, OdbcParameter[] commandParameters) in 

Execute non query loop Code which i used was

public static int ExecuteNonQuery(string connectionString, CommandType cmdType, string cmdText, params OdbcParameter[] commandParameters)
{
    int val = 0;
    OdbcCommand cmd = new OdbcCommand();
    try
    {
       using (OdbcConnection conn = new OdbcConnection(connectionString))
       {
           CommonCommand(cmd, conn, null, cmdType, cmdText, commandParameters);
           val = cmd.ExecuteNonQuery();
           cmd.Parameters.Clear();
           conn.Close();
        }
    }
    catch (Exception ex)
    {
    }
    finally
    {
        cmd.Dispose();
    }
    return val;
}

What i have to do now in order to solve the issue

Waiting for your responses..