So, today I will address these two specific queries and also show a handy TSQL which could be used to remove Index fragmentation.ReBuilding Index means the Index will be ReBuild from scratch using the same definition of its creation. It is same as using DBCC DBREINDEX. Please note that for new development do no use DBCC DBREINDEX as its deprecated from SQL Server 2005 onwards. It is generally preferred to rebuild OFFLINE. However, rebuild could be done ONLINE by specifying appropriate options.ReOrganizing Index means the data at the Index leaf will be re-organized and the fragments will be removed to the max possible extent. It is same as using DBCC INDEXDEFRAG. Please note that for new development do no use DBCC INDEXDEFRAG as its deprecated from SQL Server 2005 onwards. ReOrganizing an Index is an ONLINE process.When to use what – As per Microsoft, the recommendations are as under -
If Avg. Fragmentation < 5%, no action is required
else if 5% < Avg. Fragmentation <= 30%, reorganization is required
else if Avg. Fragmentation > 30%, rebuild is required
So, based on the above recommendations, the below TSQL could be used to serve the purpose -

To keep the indexes in proper shape, a timely defragmentation is necessary. When to defragment has got no THUMB RULE, it depends on the environment. However, the above script could be scheduled as per ones need to make sure that the indexes gets defragmented when need.

Friday, 6 January 2012

Problem
While working with System.Transactions in .net 2.0 or higher versions, we often come across a situation where we need to execute some long running processes inside Transactions and our process gets aborted after few seconds. This demands for some long running transactions. So, lets see how this can be achieved -

Setting the CommandTimeout for the Command object – Command object is used to execute raw TSQL/ Stored procedures though .net. This object has got a getter/setter property CommandTimeout which holds the wait time before terminating the attempt to execute the command. By default, the value is 30 seconds. So, a long running process will certainly get aborted after 30 seconds. In order to extend it over this time, we can set an appropriate timeout value. We need to set it to 0 (zero) to set it to infinite. For more information, please refer this msdn article.

Next important factor in this sequence is setting the timeout value for the TransactionScope. The TransactionScope class has got an overloaded constructor, where we can specify the timeout TimeSpan

By default, the value is 60 seconds. We can an appropriate value for this property or can also set it to infinite the same way as we did for the command object. We need to set it to 0 (zero) to set it to infinite.

This is the point where most of the developers think they are done after setting the timeout values for the command & the transaction objects. But this is not 100% correct. It holds true only to a time limit of 10 minutes. If you want your transaction to continue after that, setting only the above two values is not the solution. And here comes into play the TransactionManager settings.

TransactionManager sits on top of Transaction & Command. The default timeout value of TransactionManager is 10 minutes and it is defined in the machine.config file. The main problem in re-setting this value is -

It can not be changed programmatically.

And due to Point 1, we have to make manual changes in the machine.config file. This results in a machine wide change; i.e. all the applications running on that box will be affected by this change. So, one needs to take utmost care in doing so and should properly assess the consequences before attempting this change.

Please note that we can not specify the machinesettings in our own configuration file. But we need to actually add/change the machinesettings\timeout in the machine.config file. Below shows a timeout of 30 minutes after manual change.

<configuration>

<system.transactions>

<machineSettings maxTimeout="00:30:00" />

</system.transactions>

</configuration>

Conclusion
The timeout of a transaction is decided by MIN(TransactionManager Timeout value, Transaction timeout value, command timeout value); i.e. a minimum value of these 3 settings is the deciding factor.

Search This Blog

About Me

Experience of more than 11+ years in developing & designing Client-Server based applications, smart client applications, web portals and small utility applications using Microsoft technologies.
Strong in designing the Application Architecture, providing out of box solutions & a very good trouble shooter.