transaction-log | Automatically rebalance or trade your crypto portfolio | Portfolio library

Remove the TRUNCATEONLY.

The first thing to understand is what kind of recovery mode the database is using.

If you are in FULL recovery mode, it's not enough to take regular backups. You must also take frequent (every 20 minutes, or even faster) transaction log backups. Sql Server will never recycle the transaction log unless you do this, and it will continue to grow until you run out of space.

After you have run a transaction log backup, you should be able to shrink the log file and reclaim that disk space.

If you are not in FULL recovery mode, you may be able to just manually clear or expand the transaction log in Sql Server management studio.

This is just an XYProblem. The problem isn't the transaction log size, it that's you aren't taking transaction log back ups and wondering why the transaction log is growing. It's growing because you aren't backing it up.

Either you need to add an agent task to regularly create transaction log back ups, or change the recovery model. Considering your statement "actually the transaction logs are not very important" I suggest the latter, and then set the max size of the file:

First lets put some common vocabulary. We have two or more parties

• Transaction Coordinator this is where our business logic resides. This is the side that orchestrates the distributed transaction.
• Transaction Participant (XAResource) this can be any Dababase supporting distributed transactions or some other entity supporting the XA protocol like messaging service.

Lets highlight the main API functions performed during XA transaction. - start(XID) - end(XID) - prepare(XID) - commit(XID)

The first 2 operations are visible in our source code. This is when we initiate the transaction do some work and then say commit. Once we send the commit message from the source code the Transaction Coordinator and the transaction Participant take over and do some work.

XID parameter is used as a unique key identifying the transaction. Each transaction coordinator and each participant at any time can participate in more than one transaction so this is needed in order to identify them. The XID has two parts one part identifies the global transaction, the second part identifies the participant. This mean that each participant in the same transaction will have its own sub identifier. Once we reach the transaction prepare phase , each transaction participant writes its work to the transaction log and each Transaction Participant(XARersource) votes if its part is OK or FAILED. Once all votes are received the transaction is committed. If the power goes down the both the Transaction Coordinator and the Transaction Participant keep their transaction logs durable and can presume their work. If one of the participant vote FAILED during transaction commit then subsequent rollback will be initiated.

Implications in terms of performance

According to the CAP theorem each application(functionality) falls somewhere in between the triangle defined by Consistency, Partitioning and Availability. The main issue with the XA/ Distributed transaction is that it requires extreme consistency.

This requirement results into very high network and disk IO activity.

Disk activity Both the transaction coordinator and the transaction participant need to maintain a transaction log. This log is held on the disk each transaction needs to force information withing this disklog, this information is not buffered information. Having large parallelism will result in high amount of small messages forced to the disk in each transaction log. Normally if we copy one 1GB file from one hard disk to another hard disk this will be very fast operation. If we split the file into 1 000 000 parts of couple of bytes each the file transfer will be extremely slow.

Disk forcing grows with the number of participants.

1 participant is treated as normal transaction
2 participants the disk forcing is 5
3 equals 7

Network Activity In order to draw a parallel for distributed XATransaction we need to compare it to something. The network activity during normal transaction is the following. 3 network trips -enlist transaction, send some SQLs, commit.

For a XA transaction it is one idea more complicated. If we have 2 Participants. We enlist the resources in a transaction 2 network trips. Then we send prepare message another 2 trips then we commit with another 2 trips.

The actual network activity that is happening for 2 resources grows even more the more participants you enlist in the transaction.

The conclusion on how to get a distributed transaction fast

• To do this you need to ensure you have a fast network with minimum latency
• Ensure you have Hard drives with minimum latency and maximum random write speed. A good SSD can do miracle. -Try to enlist as minimum as possible distributed resources in the transaction
• Try to divide your data into data that has strong requirement for Consistency and Availability (Live data) and data that has low consistency requirements. Live data use Distributed transaction. For offline data use normal transaction, or no transaction if your data does not require it.

My answer is based on what I have read in "XA exposed" (and personal experience) which appears to be no longer available on internet which triggered me to write this.

You should use sys.dm_db_index_usage_stats DMV.

user_updates column responds on your first question (insert/update statements):

Number of updates by user queries. This includes Insert, Delete, and Updates representing number of operations done not the actual rows affected. For example, if you delete 1000 rows in one statement, this count increments by 1

Other columns like

• user_seeks Number of seeks by user queries.
• user_scans Number of scans by user queries that did not use 'seek' predicate.
• user_lookups Number of bookmark lookups by user queries.

will help you to determine how useful was this index for user queries.

The only thing to remember here is that DMV holds the statistics only for the time db is online, I mean if you restart your server or simply put database offline/restore it the statistics are reset to 0.

Is it possible to do this by using the transaction-log

SELECT statements are not logged.

Will committing intermittently within the SP allow the transaction-log to truncate?

If the client starts a transaction, it's not recommended to COMMIT that transaction inside a stored procedure. It's not allowed to exit the stored procedure with a different @@trancount than it was entered with.

The following pattern is technically allowed, although I have never seen it used in the real world:

I'm not one for hyperbole, but there are literally billions of articles written about SQL Server transaction logs.

Reader's digest version: if you delete 1,000,000 rows at a time, the logs are going to get large because it is writing those 1,000,000 deletes in case it has to roll back the transaction. The space needed to hold those records does not get released until the transaction commits. If your logs are not big enough to hold 1,000,000 deletes, the log will get filled, throw that error you saw, and rollback the whole transaction. Then all that space will most likely get released. Now you have a big log with lots of free space.

You probably hit a limit on your log file at 4.5gb and it wont get any bigger. To avoid filling your logs in the future, chunk down your transactions to smaller amounts, like deleting 1,000 records at a time. A shrink operation will reduce the physical size of the file, like from 4.5gb down to 1gb.

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-shrinkfile-transact-sql?view=sql-server-2017

Closing the socket using it's ID should close it completely.

However, simply set the soTimeout property and the operating system will notify the framework, which will close the socket if no data is received in that time.

throw new SoftEndOfStreamException

I am not sure where you are doing that, but I can't think of a scenario where it would close the server socket.

That exception should only be thrown from a deserializer (when it detects the socket closed between messages).

set com.atomikos.icatch.threaded_2pc=true in jta.properties fixed my problem. Idk why this default value was change to false in web application.