SQL Server Log File is HUGE!

2 ways to resolve a SQL Server log file full error, with mildly amusing commentary :)
If you are getting this, you may have other issues...ping me

[ThePimpLoving]

6 years later and this video is still helpful, great job, appreciate it. Thank you

[robdevoer]

I have 'enjoyed' fixing crawling servers with full drives after log file explosions a few times before so know the solution. Unsure why I decided to watch your video but I found myself blown away by the pleasant way that you explained the problem, its reasons and the solution while smoothly touching on the tools to analyse and the settings that were involved. Thanks, I enjoyed myself.

[dcbc]

Great video Kevin. I've had to do this a few times in the past when our backup solution was over-committed with other jobs and I couldn't get timely transaction log backups. My trans logs had eventually grown to their max causing the application to halt. Glad that I handled it correctly as you demonstrated.

[kiranmadhavraokulkarni]

Same here one of my junior suffered the incident at night on parellel server he tried to shrink the 3 tb log space and he failed to retrieve the space so he called me and applied same trick and cleared within a second Thanks
Kevin hill for this Trick to resolve log space issue

[fisnik]

this saved my life, I had 875 GB Log, and when I was shrinking, the file was just getting larger ( I dont know yet why ?), when I changed the DB Type to Simple, and shrinked again it worked as expected.

Thanks a lot :)

[jazevangelio]

This saved my life today! I was looking and reading solutions that won't work but you explained it well and good!

[MrL]

Thank you for this, a great help. We had a 90GB log file. Being a MySQL guy, I did not know of this Microsoft SQL feature.

[uditrg]

I am a fan of your sense of humor; not to mention your knowledge.

[Kurnacopia]

Kevin, thank you very much for this video! this happened to us yesterday at work and your video explained exactly what to do in that case.

[ginaperalta]

I did that and log file is full again. What’s the configuration for avoiding this to happen again?

[luigivelez]

The best video, I was looking on internet for the answer to do this and finally get the answer through your video.
Definitely I am subscribing to your channel.

[jheydasch]

Great video; really appreciate you sharing! It provided some clarification on a gigantic log file I’m working with.

[manojdheerasinghe]

Hi Kevin,
Nicely done. The explanation is very clear.
Could you please explain why we should avoid shrinking the data file and what the disadvantages of shrinking the log file are, particularly in terms of recovery or any other potential issues?

[dbhaffey]

Thank you Kevin Hill! You saved my sanity today. Now to figure out why this DB bloated the way it did!

[sagarkashid]

Hello Sir, you changed recovery model and fixed the issue but same senario data base is part OF always on it is not possible. How to Handel this senario

[BransenDaniels]

Save me time digging into this for a customer. Very well explained, you have earn a sub from me!

[ein]

Good Evening, thank you for the great video.

Right now we are about to increase our server disk capacity, but of cource if we want to do that we need to temporary shut down the server and restore the backup file from the main server to the temporary one.

But unfortunately in our temporary server with capacity of 150 GB Free Space is not enough when doing a restore. Even though the size of the backup file from the main server is 3 GB.

There i notice the log file is more than 160 GB. O_O

I was more depressed when i google how to shrink the log file and all the result say "Don't shrink the log file". But then i see your video, it has a pretty long duration of 10 minutes just for explaining how to shrink a log file. But the 10 minutes is SO MUCH INFORMATIVE and very easy to understand. You also give me a courage to do a shrink file since you said : this is a condition where you absolutely have to to shrink log file.

I do all you said, Backup the log file. Check if the used log become green (Unused). and try the shrink thingy. It's done an absolutely magical thing to turn 160 GB into 4, 5 GB.

After that i do another full backup with the result of the same 3 GB size from the main server and try to restore the database again in our temporary server.
When i open the backup windows, the SIZE OF THE LOG STILL MORE THAN 160 GB. I was devastated at this moment of time. Then I just curiously i press the OK button to see if the error warning will be shown again.
BUT WHAT SHOW IS NOT EVEN AN ERROR WARNING. But the process with the text of 0%. I was shocked, my face turn into a stupid face. Then the text change from 0% into 20%, my face change into a grin. AND LASTLY IT'S BECOME 100%. ITS SUCCESS ! ITS SUCCESS !!!! DAMN MAN !!! YOUR AMAZING MY MAN !!!


Sorry for the long comment, the summary of the comment is i finally solve my problem thanks to your video. And want to ask when i restore database from a backup. the log size is still showing the last size before shrink is done. But the restore process is still success with the restored file size is the same size as the file AFTER shrink is done. Is this a BUG ? I'm using SQL Server 2008 R2

[MrSparkefrostie]

Hmmm, wondering if I can create a new ldf file, link it, limit the old file and the start backing up the ldf then when the ldf is old enough to delete it or something similar, should avoid having 2 copies of the ldf

[CEO-Love-fc]

Good morning Kevin I am interested in becoming a SQL DBA do you have any programs/ schools you can recommend?
I would greatly appreciate it. Thank you for reviewing my question and providing feedback!

[brentlawrence]

Thank You. I have Googled everything and this was a simple and very effective method. Kudos to you Mr. Hill