Doug:

Log truncation happens automatically in simple recovery model which keeps log from filling. The truncation process reduces the size of the logical log file by marking as inactive the virtual log files that do not hold any part of the logical log. Backup the transaction log file regularly to delete the inactive transactions in the transaction log. One of the reasons to grow log file so huge in the simple recovery model is that a single transaction that lasted for so long that it forced the transaction log to grow so huge. There could be another scenario where you have large amount of data updated in a single data transaction which lead the transaction log to grow large.

Do you have replication set up on this database? From the error message, it looks like you have set it up.

Also, I would recommend setting the database in full recovery model and do the transaction log backup every 15 minutes and see if that help. You might want to shrink the log file for now (though it is not highly recommended) to keep going and then do the log backup regularly. Transaction log also grows when you have index and statistics maintenance plan.

Also look at the auto growth setting for the transaction log file and see what is the max size allocated for the transaction log.

When you say "Backup the transaction log file regularly to delete the inactive transactions in the transaction log." are you meaning for us to do that in "simple recovery model "? If so, those radio buttons are inactive so we are not allowed to backup the .ldf in that model. Until we can change to "Full recovery model" what can we do at this point to shrink the .ldf?

We cannot do the log backup in the simple recovery model. You have to change the recovery model from simple to full to do the log backup. You can shrink the log file. Are there any open/active transactions now?

Initial Size (MB) - 108819 Autogrowth - 10% unrestricted growth

Can I find the open transactions in - dmtransession_transactions

If so, there are 21 rows in that table.

Use DBCC OPENTRAN command to determine whether an open transaction exists.

DBCC OPENTRAN returns the following result set when there are no open transactions in the transaction log:

No active open transactions. DBCC execution completed. If DBCC printed error messages, contact your system administrator.

If there is no open/active transactions, you can shrink the log file.

Here are the results, so does indicate there are open transactions

Transaction information for database 'ECOMLIVE'.

Replicated Transaction Information: Oldest distributed LSN : (1118920:226253:4) Oldest non-distributed LSN : (1118920:226307:1) DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Is it OK to change models while production is up and running?

Is this database used for replication? This message says that the database was or is marked for replication.

Thanks

Yes - and I checked with our web host developer and below is his response.

That is your transaction log for the live DB, replication shouldn’t be involved.
When was the last time you had a successful full backup? If the log file didn’t shrink automatically, you can shrink it manually.

Well, if there is a replication going on, you have to check if the replication is working properly.

Replication is working properly - we confirmed

For now we just need to shink the log file Do you see any reason not to do the following during business hours?

USE [ECOMLIVE]
GO
DBCC SHRINKFILE (N'ECOMGEN_log' , 0, TRUNCATEONLY)
GO

I think your replication is unable to cope up with what is going on in the log file.Had everything been fine the o/p for Oldest non distributed LSN should had been (0:0:0).I strongly believe a long never ending transaction to be real culprit.

I would recommend you run the following and post back the result you see in the query output.

select log_reuse_wait_desc 
from sys.databases 
where database_id = db_id()

This will tell you why the TRN log cannot be truncated.

My next question is, is your production database in simple recovery model? My next question is what type of replication are you using? And how you could do transactional replication if the database is in simple recovery model? I am confused now!!! Instead of going to shrink it rightaway i would find out the cause of the growth of the log, it will grow again if the same issue exist, so better try to find out what is causing it to grow and can you tell me what type of replication is being setup there.

Thanks, Abi

AbiChapagai,

Transactional replication or any replication for that matter has nothing to do with type of recovery model.There is a myth that has been going around for some time that replication needs recovery model to be in full recovery mode which absolutely wrong.

1.) We found the problem - Last Thursday there was a stored procedure caught in a loop with an update statement within the loop and wasn't caught until the next morning. Our backup failed the same night so I'm sure that is what caused the growth of the .ldf

2.) The results to the script you provided" select logreusewaitdesc
from sys.databases
where databaseid = 5 --dbid() Output - ACTIVEBACKUPORRESTORE

3.) Our database is in simple recovery model

4.) Replication type is transactional

5.) How you could do transactional replication if the database is in simple recovery model? I had a similar question - I'm still looking into this part

Doug,

Well the log reader agent reads the log files and selects the records which are marked for replication and replicates them over to the distributor database and changes the status of those records to replicated.It is a known fact that log writes are done even in simple recovery mode and are truncated on checkpoints.

So until transaction replication does not replicates the records to the distributor DB the checkpoint wont include those records in the truncation process.So a replication as a whole is not dependent on any kind of recovery model.

2.) The results to the script you provided"

select logreusewaitdesc
from sys.databases
where databaseid = 5 --dbid()

I ran it three time

Output1 - ACTIVEBACKUPORRESTORE Output2- NOTHING Output3 - REPLICATION

Is running the shrink script dependent on these results?