Archive
XACT_STATE or @@Trancount (To control Nested Transaction)
http://msdn.microsoft.com/en-us/library/ms189797.aspx
XACT_STATE returns the following values.
| Return value | Meaning |
| 1 | The current request has an active user transaction. The request can perform any actions, including writing data and committing the transaction. |
| 0 | There is no active user transaction for the current request. |
| -1 | The current request has an active user transaction, but an error has occurred that has caused the transaction to be classified as an uncommittable transaction. The request cannot commit the transaction or roll back to a savepoint; it can only request a full rollback of the transaction. The request cannot perform any write operations until it rolls back the transaction. The request can only perform read operations until it rolls back the transaction. After the transaction has been rolled back, the request can perform both read and write operations and can begin a new transaction.When a batch finishes running, the Database Engine will automatically roll back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application. This message indicates that an uncommittable transaction was detected and rolled back. |
Both the XACT_STATE and @@TRANCOUNT functions can be used to detect whether the current request has an active user transaction.
@@TRANCOUNT cannot be used to determine whether that transaction has been classified as an uncommittable transaction.
XACT_STATE cannot be used to determine whether there are nested transactions.
http://msdn.microsoft.com/en-us/library/ms181299.aspx
ROLLBACK TRANSACTION without a savepoint_name or transaction_name rolls back to the beginning of the transaction. When nesting transactions, this same statement rolls back all inner transactions to the outermost BEGIN TRANSACTION statement.
In both cases, ROLLBACK TRANSACTION decrements the @@TRANCOUNT system function to 0. ROLLBACK TRANSACTION savepoint_name does not decrement @@TRANCOUNT.
ROLLBACK TRANSACTION cannot reference a savepoint_name in distributed transactions started either explicitly with BEGIN DISTRIBUTED TRANSACTION or escalated from a local transaction.
A transaction cannot be rolled back after a COMMIT TRANSACTION statement is executed, except when the COMMIT TRANSACTION is associated with a nested transaction that is contained within the transaction being rolled back. In this instance, the nested transaction will also be rolled back, even if you have issued a COMMIT TRANSACTION for it.
Within a transaction, duplicate savepoint names are allowed, but a ROLLBACK TRANSACTION using the duplicate savepoint name rolls back only to the most recent SAVE TRANSACTION using that savepoint name.
Paul recommand doesn’t use Nested Transaction.
Index rebuild vs. Statistics update
Another good article.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html
Index operation
Index rebuild and Statistics update
http://weblogs.sqlteam.com/billg/archive/2011/02/08/sql-server-scripts-i-use.aspx
Logging issue
Version : SQL2008 R2
http://msdn.microsoft.com/en-us/library/ms191484%28v=sql.105%29.aspx
|
Index operation |
Full |
Bulk-logged |
Simple |
| ALTER INDEX REORGANIZE | Fully logged | Fully logged | Fully logged |
| ALTER INDEX REBUILD | Fully logged | Minimally logged | Minimally logged |
| CREATE INDEX | Fully logged | Minimally logged | Minimally logged |
| DBCC INDEXDEFRAG | Fully logged | Fully logged | Fully logged |
| DBCC DBREINDEX | Fully logged | Minimally logged | Minimally logged |
| DROP INDEX | Index page deallocation is fully logged; new heap rebuild, if applicable, is fully logged. | Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged. | Index page deallocation is fully logged; new heap rebuild, if applicable, is minimally logged. |
You need to change Bulk-logged operation when starting the index rebuild.
Even if Bulk-logged, “Alter index reoranize” is fully logged. So, please change the schedule of Trasanction log backup frequently.
Index rebuild
http://sqlfool.com/2010/04/index-defrag-script-v4-0/
Statistics update
distributed transaction insert exec error
error message would be like this.
Msg 7391, Level 16, State 2, Procedure xxx, Line xxx
The operation could not be performed because OLE DB provider “SQLNCLI10″ for linked server “LinkedServer B” was unable to begin a distributed transaction.
If you don’t care about distributed transaction for the remote exec and insert statement, you can simply change the option for the linked server.
sp_serveroption ServerName, 'remote proc transaction promotion', 'false'
