We help IT Professionals succeed at work.

MS SQL DBLog file huge

John Darby
John Darby asked
on
Greetings, the *.ldf file has suddenly grown large. Should I force a commit or rollback? Not sure how to determine next step. The volume hosing the DB is nearing 0 bytes.

Thanks,
johnD
Comment
Watch Question

Dustin SaundersCo-Founder and Chief Architect
Top Expert 2016

Commented:
Is your DB bulk logged, and if so, does it need to be?

Author

Commented:
I don't believe so

Author

Commented:
How do I set to minimal logging and reduce the size of the DBlog.ldf file?
Co-Founder and Chief Architect
Top Expert 2016
Commented:
To shrink the log, you can right click on the database and go to Tasks>Shrink>File.  Be sure to change to the log file (attached 1&2).

You can set the recovery model to simple to reduce the overall size of the log file.  In most cases Simple should be fine, but if you have a requirement for bulk logs please consider that.

More info on shrinking the log file.
ee_shrink1.png
ee_shrink2.png
ee_shrink3.png
Scott PletcherSenior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
"Large" is nebulous.  But first, you need to check if there is an open transaction in the db.

USE your_db_name
DBCC OPENTRAN

If you get a response along the lines of:
No active open transactions.

You can proceed with a log shrink, being aware that the log may grown again if another large trans comes thru.
CHECKPOINT:
DBCC SHRINKFILE(2, <preferred_max_log_size_in_mb> )

DO NOT USE THE GUI TO DO A LOG SHRINK!  The gui can be flaky, and for log shrink especially, you can't risk any flakiness.  Without a working log, the db cannot be used,.

But if there is an open tran(s), you need to resolve those first.
Jason clarkDBA Freelancer
Commented:
You need to back up your log file and free up space and check If you set the recovery to simple from full, then shrink the log file then you can set it back to full recovery if you need to. read here for more options: https://support.microsoft.com/en-in/kb/317375

Author

Commented:
Thank you for your help!