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

[Deleted user]

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.

[Andy Heinzer · Accepted solution]

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.

[Deleted user]

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

[Charles Huggins]

Many Thanks Folks,

it solved our issue.