Jira transaction log getting really big

Question

Haven't really been watching this, for the past 6 months or so, but noticed some server slowdown (MSSQL2012) and noticed that the Jira transaction log has skyrocketed to 650GB!  Any way to 'start over' with a new transaction log?

Things I've noticed

the log file was set to autogrow (not really what I want) - I've since reset this, but that doesn't address my immediate problem

Things I've tried

Going into SQL Management Studio and trying to shrink - no joy there.  Kinda afraid to just delete it.

Innovación - Proyectos Compartidos · Answer

how did you do this? " have sinced configured the database to 'not' autogrow"

aj senol · Answer

Well, don't know if any of that helped - you see I never got an error, I simply noticed slow performance and went looking for 'why'. That's when I discovered that the transaction log went bazinga.

I was able to clear the issue but I'm not really certain 'how' I did it.  I tried all sorts of things and was never able to actually delete the log when full recovery mode was enabled.  The transaction log kept growing to consume all resources.  it wasn't until I selected 'simple' recovery model that I was actually able to reduce the size of that log using 'shrink'.  Technically, that shouldn't have worked either.

I should note that nothing, adminstratively, changed in the way MSSQL was dealing with the jira database.  I have sinced configured the database to 'not' autogrow, so that the log wraps on itsself and re-enabled full recovery mode,

somethingblue · Answer

Hi AJ,

There is a knowledge base by Microsoft that may help you with this issue titled Troubleshoot a Full Transaction Log (SQL Server Error 9002).  While your log is not full there are some mitigation steps listed:

The appropriate response to a full transaction log depends partly on what condition or conditions caused the log to fill.

To discover what is preventing log truncation in a given case, use the log_reuse_wait and log_reuse_wait_desc columns of the sys.database catalog view. For more information, see sys.databases (Transact-SQL). For descriptions of factors that can delay log truncation, see The Transaction Log (SQL Server).

Alternatives for responding to a full transaction log include:

* Backing up the log.

* Freeing disk space so that the log can automatically grow.

* Moving the log file to a disk drive with sufficient space.

* Increasing the size of a log file.

* Adding a log file on a different disk.

* Completing or killing a long-running transaction.

Take a look at the article for further instructions related to each step.  Hopefully that helps.

Cheers,

Branden