Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Archive for April, 2012

XACT_STATE or @@Trancount (To control Nested Transaction)

Posted by Simon Cho on 04/23/2012

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.

http://sqlskills.com/BLOGS/PAUL/post/A-SQL-Server-DBA-myth-a-day-%282630%29-nested-transactions-are-real.aspx

Advertisements

Posted in Common | Tagged: , , , , | Leave a Comment »

Index rebuild vs. Statistics update

Posted by Simon Cho on 04/11/2012

http://sqlserverpedia.com/blog/sql-server-bloggers/update-statistics-before-or-after-an-index-rebuild/

http://www.sqlskills.com/blogs/paul/post/Search-Engine-QA-10-Rebuilding-Indexes-and-Updating-Statistics.aspx

Another good article.
http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

Posted in Common | Tagged: , | Leave a Comment »

Index operation

Posted by Simon Cho on 04/11/2012

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

http://weblogs.sqlteam.com/billg/archive/2010/12/02/script-to-update-statistics-with-time-window.aspx

Posted in Common | Tagged: , , , | Leave a Comment »

distributed transaction insert exec error

Posted by Simon Cho on 04/05/2012

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'

Posted in Common | Tagged: | 1 Comment »