.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Rollback Transaction or Rollback Work

Posted By:      Posted Date: September 16, 2010    Points: 0   Category :Sql Server
Hi all, I run a delete statement,  but it deleted wrong rows that I need in my table. I didn't start with begin transaction statement, is there a way to bring these data back using any Rollback statement or any other ways? Thank you.  

View Complete Post

More Related Resource Links

Doesn't restart sql server rollback uncommitted transaction?

hi, i executed a long-running sql in the management studio. The sql updates many rows and all updates are within a transaction. After runing 30 mins, the hard disk is full. That's because of the uncommitted updates. Then, i restarted the sql server because I thought after restarting, the uncommitted transaction is rollback and the occupied hard disk space is also released. But i found that the transaction is rollbacked but the hard disk space is not released. Why the hard disk space is not released? how can i release the disk space now? Thank you for your help.

transaction does not rollback


I've just had a real WTF moment, so I'll try not to be too incoherent...

I have a transaction coded up to do inserts in header/detail tables.  I nested everything in a transaction, thinking that if there was a problem, the transaction would rollback and, while it would commit my new data, it would nevertheless leave the database in a consistent state.  But it seems to not have worked...

Here's the transaction:

		DECLARE	@DeliveryPointID	INT				= 1,
				@TrailerID			INT				= NULL,
				@LoadPercent		DECIMAL(8, 4)	= 100.0
		INSERT INTO dbo.Slips (DeliveryPointID, GrowerContractID, TrailerID, PackRequestID, GrossWeight, LoadPercent, ReceivedDate, VarietyID, Notes)
		VALUES (@DeliveryPointID, @ContractID, @TrailerID, @PackRequestID, @GrossWeight, @LoadPercent, @ReceivedDate, @VarietyID, @Notes)
		SELECT @SlipID = 

Transaction failed to rollback


I have a client which is running SQL 2005 Express on a Windows 2003 box. The system is very much barebones with extremely small storage space (10GB total!)

The other day I had done a column modification for them on one of their larger tables. Unknowingly, I was up against their threshold for diskspace and was unable to complete the transaction. I received the following:

Could not allocate space for object 'dbo.Tmp_jobresumes' in database '###' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Thereafter, the the table could not be found. Neither the original table name nor the tmp table name.This is a huge issue as I found out that they also do not have a backup of the database where I could start to restore our log files up until the point of failure.

I'm in a major pinch because the tables contains all of their assets for candidates (they are a staffing firm) over the past 7 years and I cannot seem to figure out how best to retrieve this information - in part due to the fact that I am reluctant to make any moves (i.e. restarting server, etc.) as I do not want to loose any change of recovery unless I know the implications fully. I know that the data is st

try catch and rollback don't work



I have the ww_myProc, in which, I created a TRY CATCH and TRANSACTION block. I change my code (in group by of select) to throw exception in running time. So when I execute ww_myProc, it shows me an error in select line after running first update. But after error, CATCH block don’t run and finally Rollback don’t run and the change of first update doesn’t back before of transaction. So what is my problem?

 Best Regards. Morteza


ALTER PROCEDURE [dbo].[ww_myProc]

      @idHost int

How to rollback transaction in wcf method if CommunicationException occured?



I have a simple wcf-service (wsHttpBinding) hosted in IIS which inserts data into database and returns number of inserted records and collection of errors (such as foreign key problems while inserting the data).

int ProcessCustomers(Customer[] customers, out ErrorLog[] errorLog)
This is a implementation:
public int ProcessCustomers(Customer[] customers, out ErrorLog[] errorLog)
 using (var tran = new TransactionScope())
 //insert data into database
 return cnt;

 And i have a very simple wcf-client which invokes this method and trying to catch exceptions.

 int inserted = client.ProcessCustomer(customers, out errorLogs);
catch (TimeException timeEx)
 //write to log
catch (FaultException faultEx)
 //write to log
catch (CommunicationException commEx)
 //write to log
catch (Exception ex)
 //write to log

//close the proxy

Nested transactions, rollback causes error "Transaction count after EXECUTE indicates that a COMMIT



I have a stored proc that does something that requires a transaction, and so I've put in the begin tran, commit, and rollback on error logic in it. That works as expected. But what if I now want to use the proc in the context of a larger transaction, such as calling it multiple times or doing other things as part of the same transaction? This seems impossible to achieve without either raising an error (throwing an exception) or having SQL Server do so on my behalf, rather unhelpfully.

For instance, I wanted to test a proc and reckoned a simple way to do so would be to start a transaction, execute the procedure, and roll back the transaction if the procedure didn't abort it:

begin transaction 
exec someProc
if XACT_STATE() = 1 rollback

This works if someProc doesn't encounter any errors (so it begins and commits a transaction and the transaction count is 1 at entry and exit of the proc), but crashes and burns if the procedure rolls back the transaction, producing SQL error

Msg 266, Level 16, State 2, Procedure ExecuteImport, Line 0
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.

I don't understand why that is. To my mind, it is perfectly normal that the transaction count can go from any level at entry to ze

Rollback transaction for calling web services


Dear all,


I have the web form whcih processing several web services calls.

If one call failed, it will rollback transactions. But it cannot.

Due to the web services will complete for each call.

I am using sql connection and sql command.

Is it possible to do this??? 

Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing



We have Sharepoint 2007 SP1 databases on SQL Server 2005 SP3 x64. Users are uploading and viewing the documents with NO issues but we are frequently getting the below error in share point logs:

08/01/2010 08:21:33.11  w3wp.exe (0x0DA4)                        0x2168 Windows SharePoint Services    Database                       6f8g Unexpected Unexpected query execution failure, error code 266. Additional error information from SQL Server is included below. "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1." Query text (if available): "DECLARE @@S uniqueidentifier; DECLARE @@DocId uniqueidentifier; DECLARE @@Level tinyint; DECLARE @@DocUIVersion int; DECLARE @@DoclibRowId int; DECLARE @@iRet int;DECLARE @DN nvarchar(256); DECLARE @LN nvarchar(128); DECLARE @FU nvarchar(260); SET @@Level=2 SET @@DocUIVersion =1 SET @@DocId='9FA36FDF-00FF-4810-92CE-A5ED5671A0C9' SET @@S='6780CE0A-2C92-463D-B86D-D8ACBB72E065'; EXEC @@DoclibRowId = proc_GenerateNextId 'C145E0B3-9F7C-4E0F-9644-7DB0AEE53EDF','1E9F697F-4032

How to manage commit of some data and rollback of other data simultaneosly?

Hi, I am quite a beginner in SQL server. Few days back I came across a question, I am wondering what can be the answer for that. Pls help me out if anyone knows the answer. The question goes as below :   There is a stored procedure (say proc1) which has transaction block implemented. Now I call another stored procedure(say proc2) within the proc1. Proc2 contains a simple insert into table statement. Now some error occurs : 1) Tell me whether the inserted data (from proc2) will be rollbacked or not? 2)How can u manage not to rollback the inserted rows  (from proc2) however other data must be rollbacked (proc1).   Thanks in advance.


hi I Have deleted my sql table data (delete from employee) but this was a fault.i was suppose to delete data from other table what should i do to get my data back i have used (ROLLBACK select * from employee) to get my data back but it didnt help me.my DataBase recovery mode is also set to FUll ,still i am not able to get back my data Plaese Help me what query should i run?

I need to enable my email alert ( after rollback my changes )

Hi, same problem I am also facing, but here I need a help, if I am rollback my changes means in “Item-level Permissions” section again I am selecting all items. But still I am not getting email, I need to enable my email alert ,what is solution to enable my email alert .

SQL 2000 Enterprise Rollback


We have a problem with one of our live production systems which runs in SQL 2000 Standard.  The issue is relating to memory utilisation.  We are upgrading to SQL 2000 Enterprise this evening in order to increase memory from 2GB.  However I'm a little concearned as I'm not sure if (In case of major issues) we could revert the SQL 2000 Enterprise database back to SQL 2000 standard.


In theory we will not need to do this, but I need to understand if this is possible for our rollback position.


Any help would be greatfully appriciated.



Is TRUNCATE can be rollback in SQL Server 2005

Is TRUNCATE command can be rollback in SQL Server 2005

(INSERT EXEC statement cannot be nested.) and (Cannot use the ROLLBACK statement within an INSERT-EX


hi all
i have a very important issue,

read this scenario please

i have three Stored Procedures Sp1,Sp2 and Sp3 .

the first one (Sp1) will execute the second one (Sp2) and save returned data into @tempTB1 and the Second one will execute the third one (Sp3) and save data into @tempTB2.

if I execute the Sp2 it will works and it will returned me all my data from the Sp3 ,but the problem is in the Sp1, when i execute it it will display this Error:

INSERT EXEC statement cannot be nested

I tried to change the place of execute Sp2 and it display me another error:

Cannot use the ROLLBACK statement within an INSERT-EXEC statement.

SharePoint Workflow: random error task rollback


I get "Reason: The user who attempted to complete the task is not the user to whom the task is assigned." as the error occured, but this only happens randomly, roughly 50% of the workflows error out with above error with no apparent rhyme or reason as to what causes it to error.

Notice creation by a secretary fires off the workflow, which then gets approved by the attorney, and then it errors with above message, but only 50% of the time and in apparently random order.

SharePoint 2010 Enterprise, SQL Server Enterprise R2

Error handling: writing database errors to a table that will not get removed by a rollback


I have an error handler sproc that issues a RAISERROR statement.  I would like for this sproc to write error messages to a table.  No problem so far.  What if there is a nested transaction with several ROLLBACKs?  If the code is only 1 level deep the error handler sproc can write to the error message table after the ROLLBACK.  If the code is 2 or more levels deep and an error occurs then what is written to the error message table might get rolled back if an error occurs after returning to the 1st level.  What I thought I would like to do is to have the error handler spawn a sub-process that would record the error message, where the sub-process would not be affected by any subsequent ROLLBACKs in the calling sproc.  Thoughts?

SQL Server 2005 64bit -SP3 rollback process on A/P Cluster Environment


Hi all

Erlier I posted a questions Which I got Answer form Alberto Morillo & Siva...

See link http://social.technet.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/52bf813a-cd8b-4e40-a9a3-e80ceea0ee1b

in fact my question suppose to be  "SQL Server 2005 64bit -SP3 rollback process on A/P Cluster Environment"

Please some body guide rollBack process for given Rollout.

Pre-SQL Server SP3 Installation Steps. 

1. Prior to Installing the SP3,Take  the  Full Backup of All Database (System Databases, User Databases), Databases backup is required  for roll back process, If installation causes any Database Damages

2. Disconnect the all Application connects.

3. Announce the Down time for 60 minutes

SQL Server SP3 Installation Rollout Steps. 

1. Log into the server as an account that has privileges to install the service pack. On a failover
Cluster, this must be the cluster service account or a domain account that has ad-ministra

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend