I am running a series of archiving queries from a SPROC on a SOURCE database and a DESTINATION database on Sybase ASE. I run these queries in batches or in a series of transactions, which means only n number of records will be archived in each transaction.
However, there are times when the Sybase ASE will run out of log space and end SPROC.
My question is: when the Sybase ASE runs out of log space and ends my SPROC, will my transaction rollback the data it was working with at the time the "out of log space" error ends my SPROC?
I know all transactions committed before the "out of log space" error is permanent. But I am not sure whether or not the transaction will rollback on this error and I also find it difficult to test this.
Many Thanks
This will ususally depend on individual database settings.
If the database is set to "Abort Tran on Log Full", then when the transaction log fills up, the transaction will be aborted/rolled back. If that option is not set, then the database will go into "LOG SUSPEND" mode, and will pause all activity within the database until space is freed up, or added to the log. Once log space is available, the transaction will be allowed to complete.
The flags currently set in a database can be found by issuing the sp_helpdb {DBNAME} command and looking at the status column.