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

@Kevin3NF

Жыл бұрын

Appreciate the comment...still my most viewed video ever :)

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.

@Kevin3NF

Ай бұрын

Thanks for the kind words! I try to be gentle with non-DBAs...and that is who sees this issue most often

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.

@Kevin3NF

4 жыл бұрын

Can you say what your backup solution is? Being overcommitted sounds like a disaster waiting to happen!

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

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

@Kevin3NF

Жыл бұрын

Glad it helped!

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.

@Kevin3NF

6 жыл бұрын

That's fantastic! So glad I could help :)

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 :)

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.

You saved my day on a production critical situation. Thanks so much !!

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

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

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.

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

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

@Kevin3NF

3 жыл бұрын

Glad it helped!

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

@Kevin3NF

2 жыл бұрын

Most likely candidates: Long period of time with no log backups, and index maintenance.

Very well explained, thanks Kevin.

Thank you, it worked! ... the confidence you need in a stressful scenario!!

@Kevin3NF

3 жыл бұрын

Glad it helped!

You explained this concept very well! Thanks!!

Absolutely best video explaining this! Thank you.

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

@Kevin3NF

5 жыл бұрын

Repeated shrinking of log files is a problem, not just one time due to unusual scenarios. Your other issue is hard to determine specifics, other than maybe the Full had the 160GB size listed as part of the database meta data...

You saved me big time! But have to update my resume. Thank you from the Philippines! :D

You explained it so clearly. Thank you!

Thank you. This gave me some relief.

Thank you Kevin HIll!

This video is a life saver . Channel subscribed Boss .

Thanks a lot Kevin. This really help me.

Great video! Very helpful!

Thanks Kevin, this solved my problem

Thank you for this, it was super helpful.

Thanks man, great video!

This is a life saver

This saved me today! thank you!

Excellent explanation good stuff

Thank you sir, you have clear my problem of last 2 days.

@sanketkhamkar

4 жыл бұрын

If u have any video related Unalloted Space of SQL. Kindly share.

Thanks simple and clear ! not a time waster.

Thank u Kevin, great stuff

@Kevin3NF

3 жыл бұрын

Very welcome

Thank YOU MEN you were a godsend

Mannnn you’re the best, saved my life 💪🏻🙏🏿

@Kevin3NF

2 жыл бұрын

That's me...saving lives one log file at a time

This was awesome! Thanks tons!

@Kevin3NF

6 жыл бұрын

Glad you liked it. Let me know if there are other SQL Beginner things I should video :)

well explained Kevin.

Thanks, very helpful!

thank you you saved my whole life

Thank you for this it was very helpful

thank you for the great video

You are the best, Thank you

you rock my world man :) Thanks

Great tutorial!

@Kevin3NF

2 жыл бұрын

Thank you!

interesting video, Thank you!!!

Thank you sir.

Brilliantly explained

@Kevin3NF

Жыл бұрын

Thanks!

Thank you.. you are AWESOME!!!!

Thanks a lot Kevin. ^_^ Saves my day! wohoooo! :D

@Kevin3NF

5 жыл бұрын

Glad I could help :)

ربنا يباركلك ياعم الحاج

Thank you!

Many Thanks

Thank you for the great video teacher

@Kevin3NF

Жыл бұрын

Glad you liked it!

Thank you 🙏

that's helped me a lot, thank you

@Kevin3NF

2 жыл бұрын

Glad it helped!

Amaizing, thanks a lot partner. Regards.

Many Thanks It Was Very Helpful

@Kevin3NF

8 ай бұрын

Glad it helped

its a good video and very helpfull

thanks Kevin.. I am also from SQL7 era..

I have no SQL experience but this was awesome and effective.

Great video

@Kevin3NF

2 жыл бұрын

Thanks!

hello, i'm new at this. it's my first time using SQL Server and the first time trying to open a .sql file that i downloaded on the internet. the file is 119 gb. i tried to open it with sqlcmd-utility: sqlcmd -S ServerName\InstanceName -i C:\Users\Shawder\Desktop\file.sql when i press enter, it doesn't work. does anyone know how to opening it?

Awesome video. Exactly what I needed to figure out how I was losing gigs of drive space daily. Thanks!

@Kevin3NF

2 жыл бұрын

Glad it helped!

Kevin, you are more legendary than the pyramids well, i just want to know if there is a prevention measures we can adopt to prevent this from happening in the first place,. thanks a lot in advance

@Kevin3NF

5 жыл бұрын

In FULL recovery - set up regular log backups. Otherwise, SIMPLE recovery if losing the ability to restore to a point-in-time is OK.

@akrooma

5 жыл бұрын

@@Kevin3NF thank you so much.. i actually take backups that do not require point-in-time.. you clarified a lot for me

Hi, if after changing to simple recv mode, can I get back the last log files which were cleared as a result of changing the mode from Full to Simple. What if the cleared log files were not yet being committed to the database.

@Kevin3NF

4 жыл бұрын

Nope. Once the transaction in the log file has been committed when in Simple, it is gone. Not that exact moment, but in a matter of seconds. You can only get back what you backed up and you cannot back up a t-log in Simple. docs.microsoft.com/en-us/sql/relational-databases/logs/database-checkpoints-sql-server

Thank you Sir. Regards Javed khan India

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

@Kevin3NF

10 ай бұрын

You CAN create a new .ldf file, if you cannot get a backup right away. Its a whole *thing* to get rid of the file later. When you run a LOG backup, it will do both files

yes it's a good idea, thanks bro

@Kevin3NF

Жыл бұрын

You're welcome!

Great video Kevin. One question… There’s no space left to back up the file locally as it’s 875 GB on a 1 TB drive. Without resorting to changing the system to ‘Simple’ recovery model. I’ve mounted a network drive on the server and would like to back up there instead. How can I make SQL see the network drive that I’ve mounted for this backup?

@Kevin3NF

2 жыл бұрын

Backup log [your database] to disk = '\\yournetworkserver\sharename' SQL Server understands UNC paths, as long as the permissions are set appropriately

@Kevin3NF

2 жыл бұрын

Obviously change things and add a file name :)

Very informative video. Question- I backed up the t-log (250ish gb) it shows 99% unused now, but when releasing unused space it only went down to 230gb, any thoughts?

@Kevin3NF

2 жыл бұрын

Log file shrinking is one of the least intuitive things in SQL Server. Most likely you have active VLFs at the beginning and end of the .LDF file. Shrink can only remove the inactive VLFs at the end, not the ones in the middle. Shoot me an email...

You video is helpfull but I need to know if before shrink db log files I must do the backup of my db ? but the step I return is change recovery to simple...shrink log files...then change again in recovery full isn't it?

@Kevin3NF

6 жыл бұрын

If you cannot backup the huge log file then 1- Change to Simple 2- Shrink log file 3 - Change back to Full 4 - Take a full backup to reset the backup chain

@titefrancktshingambnguz6592

6 жыл бұрын

Thanks for you reply I try and let you know.

Thanks for the great video! I've had this occur once per week for the last two weeks. How would I prevent this from reoccurring? I had just had backed up my log file (confirmed via db properties) but it didn't shrink the logs. Only shrank once I used the shrink command. I am installing Ola's script to standardize this.

@Kevin3NF

3 жыл бұрын

None of the backup processes Shrink the log...that is a different command in T-SQL. Ideally, you are backing up the Transaction Logs "frequently" so that space inside the .LDF file is emptied and re-used by new transactions. My default for log backups in prod is every 15 minutes

@DanishAnton

3 жыл бұрын

@@Kevin3NF For some reason, I always had the notion the backup process shrinks the logs. It should be emptied / reused rather than space shrunk. Took me a while to realize that. Thanks for your help.

@Kevin3NF

3 жыл бұрын

You are not alone in that thinking :) MS does not make anything clear about database maintenance. I tried to help that here: app.pluralsight.com/library/courses/getting-started-sql-server-maintenance/table-of-contents

Great video. I have a sql server 2012 db primary data file size 200GB and Transaction Db 400GB. When I take a full .bak it shows 200GB only. When I going to restore the transaction log will be restored to it's original 400GB or it will be only restore to 200GB primary mdf and no transaction log? Second how can I reduce or skip this huge transaction log file? Recently I just only import data from this large database to the same duplicate database which I created using the same schema. This import option didn't include transaction log. Is that a way to avoid from transaction file and is this reliable way? thanks.

@Kevin3NF

4 жыл бұрын

Whatever the size and used space of the MDF/NDF/LDF files are backed up, that is what will be restored. Make sure you back up both DATABASE and LOG.

thank you but what about virtual log files? ( problem after shrinking? )

@Kevin3NF

4 жыл бұрын

VLFs - too many is a performance killer. Unrelated to this video, but did you have a specific question about them?

thanks

@Kevin3NF

Жыл бұрын

Welcome

when mdf is emptied and we are left with the data from ldf?

@Kevin3NF

4 жыл бұрын

MDF (Data file) is not emptied unless you delete all of your data.

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

@tvlog3607

4 ай бұрын

Hey did you get any updates on? because I am also having the same issue to figure out.

@Kevin3NF

Ай бұрын

If your log file is filling up quickly AND you are backing it up regularly, is it too small? Or part of an Availability Group?

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

@Kevin3NF

Ай бұрын

In that case you will have to do a LOT of CHECKPOINTS, LOG backups and SHRINKFILE attempts. AGs keep a lot of log file segments "active" and SHRINKFILE only removes ones after the last active one (per .ldf file)

These are production servers, is any impact to production while doing this task. Please confirm the same

@Kevin3NF

Жыл бұрын

If your issue is lack of backups, then there will be resource utilization while a backup runs...disk, cpu, etc.

Great video, thanks so much

@Kevin3NF

2 жыл бұрын

Glad it helped

Thanks so much! Now how do i stop it from eating my whole drive in the future?

@Kevin3NF

3 жыл бұрын

Without any other info, regular log backups is normally the key. My default is every 15 minutes.

We have a similar situation - 13 GB data file, 80 GB log file. We are setup as Full, and we do Trans log backups. Couple of questions - do you have to set the db to Simple from Full before doing the Shrink? Will a shrink for a log file that size take a while and do you have to have any minimum amount of available disk space to perform the shrink?

@Kevin3NF

4 жыл бұрын

Hi Kelly...you do not need to change to Simple. Shrinking a log file is sometimes easy, and sometimes stupid. No extra disk space is needed. But sometimes you have to run multiple log backups and checkpoints manually to get the active portion of the log file to NOT be at the end of the file. Shrink will only chop off what comes after the active portion (active VLF). Data files are very different and should only be done in small chunks, followed by index rebuild/reorganize operations.

@kellyrazor8799

4 жыл бұрын

@@Kevin3NF Does shrinking the log file have the potential to cause problems?

@Kevin3NF

4 жыл бұрын

@@kellyrazor8799 Only if you do repeated, regular shrinks. Ideally, shrink it one time to the smallest possible size, set Autogrow to something appropriate for that database, and manually grow it to a reasonable size. When the log file has to autogrow, it also fills that new space with zeros, which takes time. Do this all in a maintenance window. NO AUTOSHRINK - EVER :)

@kellyrazor8799

4 жыл бұрын

@@Kevin3NF Thank you! I hope the shrink will be a rare thing to need to do. Now if we can identify why so many transactions caused our log file to grow to 9 GB in one instance - it is normally between 1-10mb for an hour of transactions, with an occasionally higher size.

@Kevin3NF

4 жыл бұрын

@@kellyrazor8799 Probably a large INSERT, UPDATE or Delete. Also maybe index maintenance operations

Hi Sir! We have a similar problem with the one in the video but we are encountering a big log file on the templog which is having (900GB) but our tempdb is having 1.3GB. Any recommendations on this? Thank you very much sir

@Kevin3NF

5 жыл бұрын

Most likely some maintenance operation such as index rebuilds or CheckDB of a very large DB. OR, a small number of rows with a massive number of updates all inside a single transaction.

What should you do if you have a SIMPLE recovery model already ? Availiable free space is 1%, thus you can't shrink it.

@Kevin3NF

3 жыл бұрын

Its possible you have an open transaction sitting there or a replicated transaction that isn't getting to its destination

@johnk005297

3 жыл бұрын

@@Kevin3NF Is it possible to check somehow, or to perform a shrink operation without stopping production DB ?

@Kevin3NF

3 жыл бұрын

@@johnk005297 dallasdbas.com/dbcc-opentran-simplified/

Hey Kevin. Will the database go offline during the switch to simple and then back to full? Just curious because of my running system in case it goes offline during this important maintenance. many thks

@Kevin3NF

5 жыл бұрын

no, it will not. This is a property of the database, not a status. You can test this by creating a test db and writing a t-sql loop to continuously select a record from any table, and then change the Recovery Model while that loop is running.

@jeu7863

5 жыл бұрын

@@Kevin3NF appreciate your help. the video helps me alot with my current database with log file of abiut 400Gb. i will try these steps. many thks again

@jeu7863

5 жыл бұрын

@@Kevin3NF I tried viewing the reports in the MS SQL Studio by right-click on the database > Reports > ... however an error message shows "Index (zero based) must be greater than or equal to zero and less than the size of the argument list". I cannot view any report so that I can verify my attempts to reduce the log-file. Any help will be appreciated. many thanks

@Kevin3NF

5 жыл бұрын

@@jeu7863 Right-click the database, then Tasks>>Shrink>>Files. Change the FileType to Log and look at "Currently allocated space" and "Available free space".

Hi Kevin. Can I apply this following approach for SSISDB?

@Kevin3NF

4 жыл бұрын

Any database that is in FULL Recovery mode NEEDS to have the log file backed up, so yes. SSISDB is not a system database, despite it being a Microsoft created structure. Same thing for the Replication distribution database. Even the system databases need their logs backed up if they are in FULL. Great question Eden!

@edenwong2833

4 жыл бұрын

@@Kevin3NF Thank you Mr Kevin for this video as it's very helpful. May I ask how should I maintain the SSISDB log because it has to be in FULL recovery model and in which this may result in causing storage issue in a run.

@Kevin3NF

4 жыл бұрын

@@edenwong2833 You need to make sure you are taking regular Transaction Log backups. Every 15-60 minutes is typical frequency. You can set up a maintenance plan to do this. Its all built into SQL Server already.

@edenwong2833

4 жыл бұрын

@@Kevin3NF Thank you for this important advice. In this case, I'll only need to do a full SSISDB database back up once a day right? On top of the frequent back up of the SSISDB log files.

@Kevin3NF

4 жыл бұрын

@@edenwong2833 Without knowing anything else, a daily full backup and periodic log backups is a viable strategy. Your company my have different standards relating to downtime and data loss tolerance, so I cannot give you any firm advice. Please check with your management team :)

Hello Kevin, Very helpful content. On SSMS I'm seeing some tables inside database with naming "table name$change log entry$437dbf0e-84ff-417a-965d-ed2bb9650.... These table size grows like anything. Can you please guide me what are these tables ?

@Kevin3NF

19 күн бұрын

My first thought would be the "Change Tracking" feature, which is different from "Change Data Capture"

@chiragrathod2252

19 күн бұрын

@@Kevin3NF thanks Kevin, Later we found there's application driven module which enabled change log setup from LS retail. We are planning to take export dump every month and truncate tables for archival and purging those data.

Help Kevin my drive is full and i cannot enter simple mode. The logs are backing hard

@Kevin3NF

Жыл бұрын

What error trying to go to Simple? What do you mean "backing hard"? Another option is to backup the log to 'NUL', which avoids the changing of the recovery model, but takes nearly the same amount of time as a backup to Disk.

really helpful for me

@Kevin3NF

6 жыл бұрын

Awesome!

@akankhaahmed

6 жыл бұрын

you should upload much regular . the way you describe things get easier .

@Kevin3NF

6 жыл бұрын

Click my name to see the other three or four I have done. Aside from those my channel is all Mountain Bike videos and Bible verses :)

@akankhaahmed

6 жыл бұрын

i show but i mean about server and dba related.

do you replay to comments as I have a question?

@Kevin3NF

3 жыл бұрын

If the question is generic enough for the KZread audience to benefit from, yes :)

How come you aren't taking on new clients?

@Kevin3NF

6 жыл бұрын

That changes tomorrow :)

Hi, I have an issue with my errorlog file which are generating at very high speed,aproximately 1mb/s.Can you please help me in this?

@Kevin3NF

4 жыл бұрын

Error log or transaction log?

@akifyusuf6817

4 жыл бұрын

@@Kevin3NF Error log

@Kevin3NF

4 жыл бұрын

@@akifyusuf6817 Please post a question in the MS forum: social.msdn.microsoft.com/Forums/en-US/home?forum=sqldatabaseengine You will be asked what sort of information is in the log. One possible item might be if you are logging successful logins as well as failed. The errorlog was not part of this video, but you can get help at the forums

Hello Kevin, the video was amazing and it clear some of my concepts but still i have some doubts could you please help me over them. Is there any way to communicate like an email and Thanks for sharing your knowledge.

@Kevin3NF

5 жыл бұрын

Feel free to ask here, or ping me on Twitter @Kevin3NF. I like to keep these public so everyone can benefit

I ran the Shrink and it didn't seem to do anything

@brittonwatson6225

3 ай бұрын

I figured it out I had a Replication that the system thought it was still running I had to clear that out before it would Shrink my database

@Kevin3NF

Ай бұрын

Yes, the FULL to SIMPLE to FULL won't work if there is a dependency on the log file such as the log reader agent or in an Availability Group situtation

my Sql server hard disk is full and constantly growing. How do I fix it and prevent it from constantly growing?

@Kevin3NF

6 жыл бұрын

Data files or log files on that drive? Are they growing or are there others items on the drive? If its .ldf files, please watch the video. Best bet for some free help is to post a question in the MS MDSN forums, with as much detail as you can give.

@judeighodaro5417

6 жыл бұрын

log files

@Kevin3NF

6 жыл бұрын

Are you backing them up?

@judeighodaro5417

6 жыл бұрын

yes, full DB backup and I also have transaction log backup.

@Kevin3NF

6 жыл бұрын

backups will not change the size of the files...if they are are not full, you can shrink them to regain space...but don't get into the habit of shrinking all the time...that causes disk level fragmentation. to see log file usage, Run: DBCC SQLPERF(LOGSPACE);

save my job

It might be my headphones, but please invest in a 'De-Ess' for your voice.

@Kevin3NF

Жыл бұрын

Its been 5 years...I have long since upped my game on editing and audio.

@sjkirk9764

Жыл бұрын

English is my second language and I understood him pretty well. I didn't hear any problems with his voice.