July 24, 2005 at 4:09 am
July 24, 2005 at 6:01 am
I am not sure exactually what you are looking for. From BOL on rollback
Transactions are managed at the connection level. When a transaction is started on a connection, all Transact-SQL statements executed on that connection are part of the transaction until the transaction ends.
You can start transactions in Microsoft® SQL Server™ as explicit, autocommit, or implicit transactions.
Explicit transactions
Explicitly start a transaction by issuing a BEGIN TRANSACTION statement.
Autocommit transactions
This is the default mode for SQL Server. Each individual Transact-SQL statement is committed when it completes. You do not have to specify any statements to control transactions.
Implicit transactions
Set implicit transaction mode on through either an API function or the Transact-SQL SET IMPLICIT_TRANSACTIONS ON statement. The next statement automatically starts a new transaction. When that transaction is completed, the next Transact-SQL statement starts a new transaction.
Connection modes are managed at the connection level. If one connection changes from one transaction mode to another it has no effect on the transaction modes of any other connection.
You can end transactions with either a COMMIT or ROLLBACK statement.
COMMIT
If a transaction is successful, commit it. A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. A COMMIT also frees resources, such as locks, used by the transaction.
ROLLBACK
If an error occurs in a transaction, or if the user decides to cancel the transaction, then roll the transaction back. A ROLLBACK statement backs out all modifications made in the transaction by returning the data to the state it was in at the start of the transaction. A ROLLBACK also frees resources held by the transaction.
You can identify when SQL Server transactions start and end with Transact-SQL statements or API functions and methods.
Transact-SQL statements
Use the BEGIN TRANSACTION, COMMIT TRANSACTION, COMMIT WORK, ROLLBACK TRANSACTION, ROLLBACK WORK, and SET IMPLICIT_TRANSACTIONS statements to delineate transactions. These are primarily used in DB-Library applications and in Transact-SQL scripts, such as the scripts that are run using the osql command prompt utility.
API functions and methods
Database APIs such as ODBC, OLE DB, and ADO contain functions or methods used to delineate transactions. These are the primary mechanisms used to control transactions in a SQL Server application.
Each transaction must be managed by only one of these methods. Using both methods on the same transaction can lead to undefined results. For example, you should not start a transaction using the ODBC API functions, and then use the Transact-SQL COMMIT statement to complete the transaction. This would not notify the SQL Server ODBC driver that the transaction was committed. In this case, use the ODBC SQLEndTran function to end the transaction.
If a severe error prevents the successful completion of a transaction, SQL Server automatically rolls back the transaction and frees all resources held by the transaction. If the client's network connection to SQL Server is broken, any outstanding transactions for the connection are rolled back when the network notifies SQL Server of the break. If the client application fails or if the client computer goes down or is restarted, this also breaks the connection, and SQL Server rolls back any outstanding connections when the network notifies it of the break. If the client logs off the application, any outstanding transactions are rolled back.
If a run-time statement error (such as a constraint violation) occurs in a batch, the default behavior in SQL Server is to roll back only the statement that generated the error. You can change this behavior using the SET XACT_ABORT statement. After SET XACT_ABORT ON is executed, any run-time statement error causes an automatic rollback of the current transaction. Compile errors, such as syntax errors, are not affected by SET XACT_ABORT.
It is the responsibility of the programmer to code the application to specify the correct action (COMMIT or ROLLBACK) if a run-time or compile error occurs.
You might want to Check BOL under timeout.
Mike
July 25, 2005 at 1:05 pm
I am not aware of any way to tell the server to rollback a transaction at some point in the future, usually when an error occurs that requires a rollback it should be started immediately. Perhaps "rollback setting" is terminology from some other brand of database? I don't know what you're looking for, but here are my guesses.
If a process is being rolled back, you can check the time SQL Server estimates the rollback will finish with "KILL <spid> WITH STATUSONLY".
If you want to make a database change that requires booting other users out, you can use "ALTER DATABASE <db> SET ... WITH {ROLLBACK AFTER nnn [SECONDS] | ROLLBACK IMMEDIATE | NO_WAIT}". If this is the case you're asking about (i.e. how many seconds are left), I don't know of any way to check.
July 25, 2005 at 3:18 pm
Hello Gentlemen,
I appologise for the vague question, obviously not correctly asked. What i am after is to find out what is set on a database with the
Alter database set rollback after n seconds | rollback immediate | no_wait
statement. so basically i'd like to know if a query exists that can tell me if the rollback setting of a database is currently n seconds, or rollback immediate or no_wait.
Kind Regards,
Adriaan.
July 25, 2005 at 3:37 pm
Again, I don't know the query for that, but it raises some other questions.
Did you issue the ALTER TABLE yourself, and forget what the time limit was? I hope not, because I don't think there is any solution.
Is a sysadmin issuing an ALTER TABLE statement without warning the users? If you're not prepared to knock some sense into him, try forwarding his resume to all the headhunters you can find. Maybe he'll get an offer to abuse users somewhere else.
Is some automated process (like log shipping) using this to kill users without warning? This would usually be on a fixed schedule, so try to remember it. If it's happening randomly, or if the admin is not willing to share the schedule with you, see previous paragraph.
Are you trying to find out whether there's enough time to get some work done before you're kicked out? If I tell users they've got 5 minutes to get out, and they respond by throwing as much work as possible at the server before the deadline, I might just decide to change it to 30 seconds. I might warn the users, say after about 25 seconds.
I think this is an issue that requires more admin/user communication.
July 25, 2005 at 3:48 pm
Hi Scott,
I don't have an issue with the setting in an operational sense. I am just curious about how to interrogate the server as to what that setting is. I seems that most of what you can set for a DB can be retrieved again, either by querying system tables, or executing DATABASEPROPERTYEX, but it seems the folks are hiding this setting from us.
Thanks for your response, much appreciated.
Adriaan.
July 25, 2005 at 3:52 pm
If you're under the impression that this is some kind of a permanent setting, it's not. The rollback option only applies to the ALTER DATABASE statement it is part of. Both WITH ROLLBACK IMMEDIATE and WITH ROLLBACK NO_WAIT happen immediately, the only thing you might run a query for is the current countdown value on a WITH ROLLBACK AFTER nnn condition.
I still can't find a query that will give you the time remaining, you'd have better luck asking the database admin. Ask enough times and he might find a way to keep you informed.
July 25, 2005 at 3:57 pm
A case of rampant curiousity, I understand the symptoms. It's an interesting enough question that I attempted a web search or two, but came up empty.
July 25, 2005 at 4:01 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply