A situation that you may find yourself in when developing or managing a database with high throughput is that the transaction log tends to grow faster than you would like.
The easiest way out is to switch the database to use simple logging. But this might not be an option ....for instance, you may need to ensure that you can roll back data and provide full recovery ability.
The second option is to increase the frequency of your full backups and clear your transaction log out more often. This may keep youi running for a little longer, but can be lots more work.
The third option is to look at your Stored Procedure code and reduce unwanted transactions.
Transactions - good or bad
Theres really no argument to say that transactions are bad on the whole. Without them , databases would be as reliable as a Bin-man on strike. But they can be too much of a good thing on busy, data critical dataabses.
A developer will go round ensuring all essential logic is encased in a try-catch and is enclosed in begin trans / commit trans /rollback transaction statement(s).
This can be bad if all the code is doing is a select statement.
Transactions are only needed when something gets UPDATED/DELETED/INSERTED or the database gets modified in any way.
Step 1: Remove explicitly declared transactions where theyre not needed.
Transactions where you dont expect them or didnt think.
Next, comes a more sneaky culprit. "Temporary tables". These are often added to help process some complex logic or extract data from some XML file.
The act of creating a temporary table and doing insert/update statements creates a transaction for each one.
Step 2: Look for stored procedures who should just be doing select statements and check for temporary tables. If they're being used try to remove them completely or replace them with table variables. These wont modify the dataabase structure and therefore reduce the transactions.
More transactions means less!!!?
Table variabls may not be possible for your solution, or you may not have time to rre-factor your code.
An alternative method may be to re-instate or add explicit transactions to enclose the whole stored procedure and then perform a purposefull Rollback to clear the unneccesary transactions from the log.
This esentially doesnt change your code much , but gives you control over the many unexpected additional transactions that are present in your code.
How to spot them
Use SQL profiler and add "Transaction" events to those you are monitoring. You will find your stored procedures execute but there are many additional entries without any SQL text displayed. These are the hidden transactions that are being created.
Also use this stored procedure to help find use of temporary tables. It searches the stored procedures for some text.......
-------------------------------------------------------------------
CREATE PROCEDURE [dbo].[Find_Text_In_StoredProcedure]
@StringToSearch varchar(100)
--WITH_ENCRYPTION_REPLACE_ME_FOR_LOCAL--
AS
SET @StringToSearch = '%' +@StringToSearch + '%'
SELECT Distinct SO.Name
FROM sysobjects SO (NOLOCK)
INNER JOIN syscomments SC (NOLOCK) on SO.Id = SC.ID
AND SO.Type = 'P'
AND SC.Text LIKE @stringtosearch
ORDER BY SO.Name
--------------------------------------------------------------------