SQL Server Log File is HUGE!
Vložit
- čas přidán 8. 03. 2017
- 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
More stuff for new and accidental DBAs: www.dallasdbas.com/blog
Need an on-call DBA? dallasdbas.com/pocket-dba/
Start learning SQL Server administration: app.pluralsight.com/profile/a... - Věda a technologie
6 years later and this video is still helpful, great job, appreciate it. Thank you
Appreciate the comment...still my most viewed video ever :)
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.
Can you say what your backup solution is? Being overcommitted sounds like a disaster waiting to happen!
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.
Thanks for the kind words! I try to be gentle with non-DBAs...and that is who sees this issue most often
This saved my life today! I was looking and reading solutions that won't work but you explained it well and good!
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.
That's fantastic! So glad I could help :)
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.
Great video; really appreciate you sharing! It provided some clarification on a gigantic log file I’m working with.
You saved my day on a production critical situation. Thanks so much !!
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.
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, 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 :)
I am a fan of your sense of humor; not to mention your knowledge.
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
Glad it helped!
Thank you, it worked! ... the confidence you need in a stressful scenario!!
Glad it helped!
Thank you Kevin Hill! You saved my sanity today. Now to figure out why this DB bloated the way it did!
Most likely candidates: Long period of time with no log backups, and index maintenance.
You explained this concept very well! Thanks!!
Absolutely best video explaining this! Thank you.
Very well explained, thanks Kevin.
You saved me big time! But have to update my resume. Thank you from the Philippines! :D
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
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...
This video is a life saver .
Channel subscribed Boss .
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.
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
You explained it so clearly. Thank you!
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
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
Thank you. This gave me some relief.
Thanks a lot Kevin. This really help me.
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?
Thanks Kevin, this solved my problem
Thank you for this, it was super helpful.
Thank you Kevin HIll!
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?
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?
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
Thanks for you reply I try and let you know.
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.
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.
Great video! Very helpful!
Mannnn you’re the best, saved my life 💪🏻🙏🏿
That's me...saving lives one log file at a time
thank you but what about virtual log files? ( problem after shrinking? )
VLFs - too many is a performance killer. Unrelated to this video, but did you have a specific question about them?
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?
Backup log [your database] to disk = '\\yournetworkserver\sharename'
SQL Server understands UNC paths, as long as the permissions are set appropriately
Obviously change things and add a file name :)
Thank you sir, you have clear my problem of last 2 days.
If u have any video related Unalloted Space of SQL. Kindly share.
This is a life saver
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
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.
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.
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
@@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.
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
Thanks man, great video!
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
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)
This saved me today! thank you!
Thanks simple and clear ! not a time waster.
when mdf is emptied and we are left with the data from ldf?
MDF (Data file) is not emptied unless you delete all of your data.
Thank YOU MEN you were a godsend
Thanks a lot Kevin. ^_^ Saves my day! wohoooo! :D
Glad I could help :)
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?
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.
@@Kevin3NF Does shrinking the log file have the potential to cause problems?
@@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 :)
@@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.
@@kellyrazor8799 Probably a large INSERT, UPDATE or Delete. Also maybe index maintenance operations
Excellent explanation good stuff
Thanks so much! Now how do i stop it from eating my whole drive in the future?
Without any other info, regular log backups is normally the key. My default is every 15 minutes.
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
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.
@@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
@@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
@@jeu7863 Right-click the database, then Tasks>>Shrink>>Files. Change the FileType to Log and look at "Currently allocated space" and "Available free space".
These are production servers, is any impact to production while doing this task. Please confirm the same
If your issue is lack of backups, then there will be resource utilization while a backup runs...disk, cpu, etc.
Thank u Kevin, great stuff
Very welcome
Thank you for this it was very helpful
This was awesome! Thanks tons!
Glad you liked it. Let me know if there are other SQL Beginner things I should video :)
thank you you saved my whole life
I did that and log file is full again. What’s the configuration for avoiding this to happen again?
Hey did you get any updates on? because I am also having the same issue to figure out.
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?
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?
Error log or transaction log?
@@Kevin3NF Error log
@@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
you rock my world man :) Thanks
Help Kevin my drive is full and i cannot enter simple mode. The logs are backing hard
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.
do you replay to comments as I have a question?
If the question is generic enough for the CZcams audience to benefit from, yes :)
well explained Kevin.
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
In FULL recovery - set up regular log backups. Otherwise, SIMPLE recovery if losing the ability to restore to a point-in-time is OK.
@@Kevin3NF thank you so much.. i actually take backups that do not require point-in-time.. you clarified a lot for me
Thank you.. you are AWESOME!!!!
thank you for the great video
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 ?
My first thought would be the "Change Tracking" feature, which is different from "Change Data Capture"
@@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.
I have no SQL experience but this was awesome and effective.
interesting video, Thank you!!!
You are the best, Thank you
Thanks, very helpful!
thanks Kevin.. I am also from SQL7 era..
Hi Kevin. Can I apply this following approach for SSISDB?
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!
@@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.
@@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.
@@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.
@@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 :)
How come you aren't taking on new clients?
That changes tomorrow :)
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.
Feel free to ask here, or ping me on Twitter @Kevin3NF. I like to keep these public so everyone can benefit
that's helped me a lot, thank you
Glad it helped!
Great tutorial!
Thank you!
Amaizing, thanks a lot partner.
Regards.
Brilliantly explained
Thanks!
Many Thanks It Was Very Helpful
Glad it helped
Thank you for the great video teacher
Glad you liked it!
What should you do if you have a SIMPLE recovery model already ? Availiable free space is 1%, thus you can't shrink it.
Its possible you have an open transaction sitting there or a replicated transaction that isn't getting to its destination
@@Kevin3NF Is it possible to check somehow, or to perform a shrink operation without stopping production DB ?
@@johnk005297 dallasdbas.com/dbcc-opentran-simplified/
ربنا يباركلك ياعم الحاج
Thank you sir.
its a good video and very helpfull
Awesome video. Exactly what I needed to figure out how I was losing gigs of drive space daily. Thanks!
Glad it helped!
my Sql server hard disk is full and constantly growing. How do I fix it and prevent it from constantly growing?
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.
log files
Are you backing them up?
yes, full DB backup and I also have transaction log backup.
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);
Thank you!
Many Thanks
I ran the Shrink and it didn't seem to do anything
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
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
Thank you 🙏
Great video
Thanks!
Thank you Sir.
Regards
Javed khan
India
yes it's a good idea, thanks bro
You're welcome!
Great video, thanks so much
Glad it helped
thanks
Welcome
really helpful for me
Awesome!
you should upload much regular . the way you describe things get easier .
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 :)
i show but i mean about server and dba related.
save my job
It might be my headphones, but please invest in a 'De-Ess' for your voice.
Its been 5 years...I have long since upped my game on editing and audio.
English is my second language and I understood him pretty well. I didn't hear any problems with his voice.