SQL Server 2008: Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation?
-
This is for SQL Server 2008: Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation? This is the idea: - stored proc spTest contains 2 insert statements - exec spTest - spTest executes 1st insert statement - stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash) Will the data in the 1st insert statement be rolled back? Thanks in advance!
Rafferty
-
This is for SQL Server 2008: Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation? This is the idea: - stored proc spTest contains 2 insert statements - exec spTest - spTest executes 1st insert statement - stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash) Will the data in the 1st insert statement be rolled back? Thanks in advance!
Rafferty
If you want it to be.
-
If you want it to be.
What do you mean? Currently, these 2 insert statements are not enclosed in try catch blocks, but I didn't think that I need to because the error is caused by something else that is external to this stored procedure. Or is there some DB configuration-wide that I should set?
Rafferty
-
This is for SQL Server 2008: Will the stored procedure be rolled back if the db server gets an error (server crash or performance problem) in the middle of it's operation? This is the idea: - stored proc spTest contains 2 insert statements - exec spTest - spTest executes 1st insert statement - stored procedure still running... but server gets some problem that's unrelated to this stored procedure, which causes the stored proc to stop (e.g. server crash) Will the data in the 1st insert statement be rolled back? Thanks in advance!
Rafferty
-
Rafferty Uy wrote:
Will the data in the 1st insert statement be rolled back?
How about wrapping them both in a single
transaction[^].I are Troll :suss:
Hi Eddy, thanks for answering. I understand that it's better to wrap both in a single transaction. My question is really what happens if i don't. I want to know the normal behavior of sql server in such a case, not how I can improve the code. I'm investigating a bug and I suspect that this was the cause of the problem... but it seems far fetched because the server didn't crash... it just experienced some performance issues. Thanks again!
Rafferty
-
Hi Eddy, thanks for answering. I understand that it's better to wrap both in a single transaction. My question is really what happens if i don't. I want to know the normal behavior of sql server in such a case, not how I can improve the code. I'm investigating a bug and I suspect that this was the cause of the problem... but it seems far fetched because the server didn't crash... it just experienced some performance issues. Thanks again!
Rafferty
Rafferty Uy wrote:
My question is really what happens if i don't.
It will write up to the point of the crash, and execution will halt. No automatic recovery, If it crashes midway, the first sproc might be saved, the second might not. If it crashes during a write-operation, your entire database might become corrupt.
I are Troll :suss:
-
Rafferty Uy wrote:
My question is really what happens if i don't.
It will write up to the point of the crash, and execution will halt. No automatic recovery, If it crashes midway, the first sproc might be saved, the second might not. If it crashes during a write-operation, your entire database might become corrupt.
I are Troll :suss:
Just the answer I'm looking for! Thanks a lot Eddy! This really helped :)
Rafferty