It's not the same without you

Join the community to find out what other Atlassian users are discussing, debating and creating.

Atlassian Community Hero Image Collage

How can I quickly and safely reduce the size of the AtlassianJira_log.ldf on our SQL db

I have a SQL log file that inordinately large. I cannot tell if the recent increase has been significant, it may have ben an issue for some time. I do not need to hold onto the old details in the log file and would be happy to clear out the old content. Many thanks for any input.

1 answer

1 accepted

2 votes
Answer accepted
Andy Heinzer Atlassian Team Jan 29, 2018

In my experience with MS SQL, this can tend to happen pretty easily when you have the recovery model for the database set to use FULL.  This logs every SQL transaction that happens.   Since you have indicated that you do not care for the current SQL log info, then I would recommend changing this recovery model to Simple instead.   Microsoft has a number of documents that explain exactly how to do that: View or Change the Recovery Model of a Database (SQL Server)

I personally prefer to do this through the SQL Server management studio application, but that document points out a number of different ways you can achieve that change.  

That doc also recommends that after you change this recovery model to simple, to:

  • Discontinue any scheduled jobs for backing up the transaction log.

  • Ensure periodic database backups are scheduled. Backing up your database is essential both to protect your data and to truncate the inactive portion of the transaction log.

It's possible that you might not have been creating backups of this database previously and just relying upon the transaction log as a means to roll back changes.  Without this comprehensive log, backups become even more important to have now.

Many thanks for your help Andrew. It would appear that was the case. We have now taken a back up of the db and shrunk the log file as below. Regards

ALTER DATABASE AtlassianJira SET RECOVERY SIMPLE;

GO

 

CHECKPOINT;

GO

 

DBCC SHRINKFILE ('AtlassianJira_log', 250);

GO

 

ALTER DATABASE AtlassianJira SET RECOVERY FULL;

GO

Many Thanks Folks,

it solved our issue.

Suggest an answer

Log in or Sign up to answer
Community showcase
Posted in Jira

Demo Den Ep. 7: New Jira Cloud Reports

Learn how to use two new reports for next-gen projects in Jira Cloud:  Cumulative flow diagram and Sprint burndown chart. Ivan Teong, Product Manager, Jira Software, demos the Cumulative ...

334 views 1 3
Join discussion

Community Events

Connect with like-minded Atlassian users at free events near you!

Find an event

Connect with like-minded Atlassian users at free events near you!

Unfortunately there are no Community Events near you at the moment.

Host an event

You're one step closer to meeting fellow Atlassian users at your local event. Learn more about Community Events

Events near you