Disable transactions on SQL Server

Question

I need some light here. I am working with SQL Server 2008.

I have a database for my application. Each table has a trigger to stores all changes on another database (on the same server) on one unique table 'tbSysMasterLog'. Yes the log of the application its stored on another database.

Problem is, before any Insert/update/delete command on the application database, a transaction its started, and therefore, the table of the log database is locked until the transaction is committed or rolled back. So anyone else who tries to write in any another table of the application will be locked.

So...is there any way possible to disable transactions on a particular database or on a particular table?

Answer 1

It sounds from the question that you have a create transaction at the start of your triggers, and that you are logging to the other database prior to the commit transaction.

Normally you do not need to have explicit transactions in SQL server.

If you do need explicit transactions. You could put the data to be logged into variables. Commit the transaction and then insert it into your log table.

Normally inserts are fast and can happen in parallel with out locking. There are certain things like identity columns that require order, but this is very lightweight structure they can be avoided by generating guids so inserts are non blocking, but for something like your log table a primary key identity column would give you a clear sequence that is probably helpful in working out the order.

Obviously if you log after the transaction, this may not be in the same order as the transactions occurred due to the different times that transactions take to commit.

We normally log into individual tables with a similar name to the master table e.g. FooHistory or AuditFoo

There are other options a very lightweight method is to use a trace, this is what is used for performance tuning and will give you a copy of every statement run on the database (including triggers), and you can log this to a different database server. It is a good idea to log to different server if you are doing a trace on a heavily used servers since the volume of data is massive if you are doing a trace across say 1,000 simultaneous sessions.

https://learn.microsoft.com/en-us/sql/tools/sql-server-profiler/save-trace-results-to-a-table-sql-server-profiler?view=sql-server-ver15

You can also trace to a file and then load it into a table, ( better performance), and script up starting stopping and loading traces.

The load on the server that is getting the trace log is minimal and I have never had a locking problem on the server receiving the trace, so I am pretty sure that you are doing something to cause the locks.

Answer 2

" the table of the log database is locked": why that?

Normally you log changes by inserting records. The insert of records should not lock the complete table, normally there should not be any contention in insertion.

If you do more than inserts, perhaps you should consider changing that. Perhaps you should look at the indices defined on log, perhaps you can avoid some of them.

Answer 3

You cannot turn off the log. Everything gets logged. You can set to "Simple" which will limit the amount of data saved after the records are committed.