March 14, 2008 at 5:25 am
When I run the upgrade script in customer's database (create/alter/update/insert statements), the server sometimes freezes on a random statement (ie alter view).
After 10 minutes I restart the server and find out the database is about 50 statements back from frozen statement.
As if at one point it switches to SET IMPLICIT_TRANSACTIONS ON and log buffer fills up and when the client disconnects, it rolls back everything.
The script does not use IMPLICIT_TRANSACTIONS nor BEGIN TRANSACTION ... COMMIT
The biggest problem is to find out what was rolled back. Known issue? Any workarounds?
I placed several BEGIN TRANSACTION go COMMIT TRANSACTION go into script and it didn't happen again, what worries me is the apparent randomness of the event.
March 14, 2008 at 8:46 am
No matter whether or not you
SET IMPLICIT_TRANSACTIONS ON
The system will roll back any imcompleted single transaction when it is restarted.
When you run
SET IMPLICIT_TRANSACTIONS OFF
The system write the data change in dirty pages untill you run TRAN COMMITTED. Your data changes may contain more than one commands (transactions).
March 14, 2008 at 9:55 am
I know and that's not the point.
The script looks like
command
go
command
go
There are ~700 of them.
If autocommit is ON, each completed command should be committed and usually is. Uncompleted commands are rolled back.
From time to time (appears randomly on the run of same script) it behaves like if it turns off autocommit (like with explicit commands SET IMPLICIT_TRANSACTIONS ON or BEGIN TRANSACTION).
I restore database and run script. One out of 3 runs dies:
After script run everything may or not be committed. If I execute COMMIT, all appears fine.
If I disconnect the client, last 30-50 statements are rolled back, sometimes one of statements freezes (usually one of "alter view ...") and similar rollback occurs after I restart server.
For this reason I put COMMIT on several places in script and at the end and it appears to work fine.
I just wanted to hear about ideas about what could be causing it. This apparent randomness is puzzling me. It's not a problem in development environment as I can always restore and rerun the script.
Robert.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply