MS SQL DBLog file huge

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
LVL 1
John DarbyPMAsked:
Who is Participating?
 
Dustin SaundersDirector of OperationsCommented:
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
0
 
Dustin SaundersDirector of OperationsCommented:
Is your DB bulk logged, and if so, does it need to be?
0
 
John DarbyPMAuthor Commented:
I don't believe so
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
John DarbyPMAuthor Commented:
How do I set to minimal logging and reduce the size of the DBlog.ldf file?
0
 
Scott PletcherSenior DBACommented:
"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.
0
 
Jason clarkDBA FreelancerCommented:
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
0
 
John DarbyPMAuthor Commented:
Thank you for your help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.