При выполнении текущей команды возникла серьезная ошибка при наличии результатов они должны быть аннулированы

Running the following query in SQL Server Management Studio gives the error below.

update table_name set is_active = 0 where id  = 3

A severe error occurred on the current command. The results, if any, should be discarded.

• The logs have been truncated
• there is an update trigger but this isnt the issue
• the transaction count is zero (@@trancount)

I have tried the same update statement on a couple of other tables in the database and they work fine.

DBCC CHECKTABLE('table_name');

gives

DBCC results for 'table_name'.
There are 13 rows in 1 pages for object "table_name".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I just had the same error, and it was down to a corrupted index.
Re-indexing the table fixed the problem.

answered Jul 24, 2009 at 4:12

In my case, I was using System.Threading.CancellationTokenSource to cancel a SqlCommand but not handling the exception with catch (SqlException) { }

This seems to happen when there’s a generic problem with your data source that it isn’t handling.

In my case I had inserted a bunch of data, the indexes had become corrupt on the table, they needed rebuilding. I found a script to rebuild them all, seemed to fix it. To find the error I ran the same query on the database — one that had worked 100+ times previously.

answered Oct 20, 2019 at 22:46

answered Dec 7, 2020 at 13:27

I was having the error in Hangfire where I did not have access to the internal workings of the library or was I able to trace what the primary cause was.

Building on @Remus Rusanu answer, I was able to have this fixed with the following script.

    --first set the database to single user mode
    ALTER DATABASE TransXSmartClientJob
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO

    -- Then try to repair
    DBCC CHECKDB(TransXSmartClientJob, REPAIR_REBUILD)

    -- when done, set the database back to multiple user mode
    ALTER DATABASE TransXSmartClientJob
    SET MULTI_USER;
    GO

answered Aug 28, 2020 at 11:46

One other possible solution we just found after having this issue across multiple databases/tables on the same server.

Is the max connections open to the sql server. We had an app that wasn’t closing it’s SQL connection and was leaving them open so we were running around 28K-31K connections (SQL Sever has a max out at 32K ish), and we noticed that once we killed a few thousand sleeping connections it took care of the error listed on this question.

The fix was to update the apps to make sure they closed their connections instead of leaving them open.

answered Oct 21, 2020 at 15:36

In my case it was something else, += operator caused this. I had to replace += X with field = field + X to overcome this. I assume this is a bug though I wasn’t able to find any related KB on Microsoft sites.

I am using SQL Server 2008 R2(10.50.1600).

answered Jan 4, 2021 at 9:44

Just wanted to add my 2cents to help whomever the next person that comes across this is.

We noticed that a specific report was failing after half an hour. I assumed it was a timeout as the amount of data being returned was huge. It turns out the SSRS default timeout setting is 1800 seconds (30mins). I changed the setting on this specific report to run indefinitely with no timeout and that resolved our issue.

Next step is to identify ways to improve the performance of the MSSQL behind the report

answered Jun 14, 2022 at 3:21

I had the same issue but the table was too big the I couldn’t fix it from the UI or by dropping and creating all indexes.
So here is another way to solve this , or at least it worked for me.

1. I have backed up the database to a BAK file
2. I have create a new database from newly backed up file and called the new database the same name but with appendix of «_BK» like this : myDatabase_BK
3. The «bad table» on the new database was already working well without the error — so it seems that the deployment fixed the problem for the new database
4. So what left was to rename the bad table on the live database(myDatabase) and then to create the same table from the backup (after renaming myDatabase.myTable):select * into myDatabase.myTable from myDatabase_BK.myTable
5. Make sure to create all indexes on the new table.

answered Jun 19, 2022 at 7:14

I received this same exception, but it was due to the fact that I was using Polybase to issue a query to an external table from an API request that was enlisting in a Transaction via TransactionScopes. The exception thrown from ADO.Net was the unhelpful: «A severe error occurred on the current command».

However, in SSMS, I did the following which finally made me realize that it was the transaction that was causing this issue:

• View Object Explorer
• Expand the Management Node
• Expand SQL Server Logs
• View the current log, and the errors that are logged when this exception was logged.

I saw the following:

Unsupported transaction manager request 0 encountered. SQL Server Parallel DataWarehousing TDS endpoint only supports local transaction request for 'begin/commit/rollback'.

I then placed this particular query in another transaction scope, suppressing the transactionscope that it would have enlisted in, and it worked.

using (var transactionScope = this.transactionScopeFactory.Create(
    System.Transactions.TransactionScopeOption.Suppress,
    new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted },
    System.Transactions.TransactionScopeAsyncFlowOption.Enabled))
{
    // ... query

    transactionScope.Complete(); // Suppress scope option renders this unnecessary, but just a good habit
}

answered Nov 3, 2022 at 19:40

Running the following query in SQL Server Management Studio gives the error below.

update table_name set is_active = 0 where id  = 3

A severe error occurred on the current command. The results, if any, should be discarded.

• The logs have been truncated
• there is an update trigger but this isnt the issue
• the transaction count is zero (@@trancount)

I have tried the same update statement on a couple of other tables in the database and they work fine.

DBCC CHECKTABLE('table_name');

gives

DBCC results for 'table_name'.
There are 13 rows in 1 pages for object "table_name".
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

I just had the same error, and it was down to a corrupted index.
Re-indexing the table fixed the problem.

answered Jul 24, 2009 at 4:12

In my case, I was using System.Threading.CancellationTokenSource to cancel a SqlCommand but not handling the exception with catch (SqlException) { }

This seems to happen when there’s a generic problem with your data source that it isn’t handling.

In my case I had inserted a bunch of data, the indexes had become corrupt on the table, they needed rebuilding. I found a script to rebuild them all, seemed to fix it. To find the error I ran the same query on the database — one that had worked 100+ times previously.

answered Oct 20, 2019 at 22:46

answered Dec 7, 2020 at 13:27

I was having the error in Hangfire where I did not have access to the internal workings of the library or was I able to trace what the primary cause was.

Building on @Remus Rusanu answer, I was able to have this fixed with the following script.

    --first set the database to single user mode
    ALTER DATABASE TransXSmartClientJob
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE;
    GO

    -- Then try to repair
    DBCC CHECKDB(TransXSmartClientJob, REPAIR_REBUILD)

    -- when done, set the database back to multiple user mode
    ALTER DATABASE TransXSmartClientJob
    SET MULTI_USER;
    GO

answered Aug 28, 2020 at 11:46

One other possible solution we just found after having this issue across multiple databases/tables on the same server.

Is the max connections open to the sql server. We had an app that wasn’t closing it’s SQL connection and was leaving them open so we were running around 28K-31K connections (SQL Sever has a max out at 32K ish), and we noticed that once we killed a few thousand sleeping connections it took care of the error listed on this question.

The fix was to update the apps to make sure they closed their connections instead of leaving them open.

answered Oct 21, 2020 at 15:36

In my case it was something else, += operator caused this. I had to replace += X with field = field + X to overcome this. I assume this is a bug though I wasn’t able to find any related KB on Microsoft sites.

I am using SQL Server 2008 R2(10.50.1600).

answered Jan 4, 2021 at 9:44

Just wanted to add my 2cents to help whomever the next person that comes across this is.

We noticed that a specific report was failing after half an hour. I assumed it was a timeout as the amount of data being returned was huge. It turns out the SSRS default timeout setting is 1800 seconds (30mins). I changed the setting on this specific report to run indefinitely with no timeout and that resolved our issue.

Next step is to identify ways to improve the performance of the MSSQL behind the report

answered Jun 14, 2022 at 3:21

I had the same issue but the table was too big the I couldn’t fix it from the UI or by dropping and creating all indexes.
So here is another way to solve this , or at least it worked for me.

1. I have backed up the database to a BAK file
2. I have create a new database from newly backed up file and called the new database the same name but with appendix of «_BK» like this : myDatabase_BK
3. The «bad table» on the new database was already working well without the error — so it seems that the deployment fixed the problem for the new database
4. So what left was to rename the bad table on the live database(myDatabase) and then to create the same table from the backup (after renaming myDatabase.myTable):select * into myDatabase.myTable from myDatabase_BK.myTable
5. Make sure to create all indexes on the new table.

answered Jun 19, 2022 at 7:14

I received this same exception, but it was due to the fact that I was using Polybase to issue a query to an external table from an API request that was enlisting in a Transaction via TransactionScopes. The exception thrown from ADO.Net was the unhelpful: «A severe error occurred on the current command».

However, in SSMS, I did the following which finally made me realize that it was the transaction that was causing this issue:

• View Object Explorer
• Expand the Management Node
• Expand SQL Server Logs
• View the current log, and the errors that are logged when this exception was logged.

I saw the following:

Unsupported transaction manager request 0 encountered. SQL Server Parallel DataWarehousing TDS endpoint only supports local transaction request for 'begin/commit/rollback'.

I then placed this particular query in another transaction scope, suppressing the transactionscope that it would have enlisted in, and it worked.

using (var transactionScope = this.transactionScopeFactory.Create(
    System.Transactions.TransactionScopeOption.Suppress,
    new System.Transactions.TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadCommitted },
    System.Transactions.TransactionScopeAsyncFlowOption.Enabled))
{
    // ... query

    transactionScope.Complete(); // Suppress scope option renders this unnecessary, but just a good habit
}

answered Nov 3, 2022 at 19:40