SQL Server Log File is HUGE!

hi guys this is Kevin Hill I'm with Dallas DBAs in the Dallas Fort Worth area my twitter handle is at Kevin three and F feel free to follow me I would love to chat with you if you have any questions about this when you're done what I'm going for today is to answer the most popular forum question regarding sequel server asked across all the different support forums on the internet my log file is huge my data file is small help how do I fix it what happened and how do I make it not happen again usually it's not that long-winded it's usually I've got this 78 gig or 500 gig log file and a 40 make data file and I don't know what to do or why so without any further delay let's just dive right into it the first thing I'm going to show you is a sample database I've created small data big log you can see that it is a 40 Meg data file it's a simple database with one table we'll look at it a minute and it's got a 10 gig log file just because there's a 10 gig log file that doesn't all that tells you is the physical file size on the drive is huge compared to this there could be legitimate reasons for that not many but it's possible so when you see this is going on more than likely you've gotten an error or an application either you've got an alert that you've got you're running low on disk space or you've filled up a hard drive or an application to stopped being able to work with your particular database that it's complaining about that this log file is attached to so one of the first things you want to do is go over here to your sequel server management studio and this methodology of troubleshooting the tools may look a little different this goes all the way back to at least sequel server 7 in 1998 when I first started so we've got this this data to this database here if we go to the properties of it we're basically gonna see a GUI version of the exact same thing we just saw here's the log file it's ten gigs 10201 makes is roughly ten gigs and we have a 41 gig or I'm sorry 41 Meg data file we knew this already but at least it's confirmed what we were seeing oh one more thing in here something very important to look at in the options tab this database is in full recovery model that basically means this one can be restored to any point in time recovery models were a completely different conversation but it's important in this context if you're in simple you're not able to back up your log files if you're in full you are there's a lot of stuff around that that I won't get into today so let's cancel out of this like great so we know we have a big log file and we know we have a small data file let's look in here the reports that are built into management studio it looks a little different if you're on the ancient sequel 7 or sequel 2000 it's it's a graphical view that as soon as you click on the database and go to view and task pad you'll see a similar it's a bar chart instead of a pie chart go to reports standard reports disk usage and it brings up pretty pictures of your data the data file is on your left you can see that it's come almost completely full of data I've just got the one table in here there's no indexes or anything but my 40 Meg's this is here our 41 they're full of data fine my 10 gig log file over here on the right is used 100% it is full of log data what is being logged is the short version inserts updates and deletes select statements are just queries on the data they are not logged they're not in here so you can see this is actually full of data as opposed to being an empty log file that just got really big one day and everything else is fine and just needs to be smaller in the process of making this happen you'll see a bunch of log file Auto growths here where I was growing by a hundred Meg's at a time and yes it took a long time to get this done at 100 Meg's to get up to 10 gigs but I needed to make sure that you saw this alright so we know we have a log file that's full what do we do about it we already saw that we're in full recovery model so the first answer we need to take a backup of this what's going on here is I was running this query I've just got a table that has some number column and it's got a get date and it's got an ID and I'd previously populated with some nano 1.3 million record what have you with random numbers between 100 so I just say I was doing updates for air 420 then set it to 42 and then set it back so basically I was updating the same rows to do it between back and forth between two different values all of those updates every single one of them is logged in the transaction log and it just keeps appending and appending and appending Auto growing when it needs to and eventually it filled up to a capacity that I had set on it and I forgot to show you that in the files on this log file the one that's huge the maximum file size right there I had limited to 10 gigs because I'd really didn't want to fill up my own hard drive and trash my machine so that's where that was set when it hit that cap the next time I tried to run it I was actually running the update I'm sorry the the Select count and an update the Select worked just fine the update statement generated a nine thousand two error which always means the transaction log is full that should be a giant flag to you the transaction log full means it needs to be backed up if we go to the properties of this database I deliberately skipped over this general tab you can see that I backed up the full database yesterday well on March 7th and the laut the log has never been backed up first step in troubleshooting our won the very first if you're in full recovery model and you see that you've never done log backup that's a problem if it's a database that receives actual data manipulation insert update delete statements and does not just read only this is going to cause you trouble at some point it may be years it may be hours this depends on how busy your database is so this is this there's if it's the data file that's full you get an 11:05 error here not a 9000 - I forget when they broke that out specifically I want to say single 2005 when you see 9000 - it's full you really have two choices you can run backup on it and I just happen to have a log backup plan I'm not gonna run that because I don't really want to backup a 10 gig log file and take the time in this video to do it but if I hit start job here it would run and when you go look at this report wherever I put that apparently I closed it when you go look here this whole thing will change for to whatever color unused is it's green some I've seen this shift around a little bit depending on the Edition and version of sequel server you're using or at least I think I have but read the key so it'll completely empty out but it will not shrink so what you would do let's pretend this is that I've done the backup and this is all green now you know normally we don't recommend shrinking things and database files for good reason about fragmentation and whatnot this is one of those situations where you absolutely need to do this so if I go to into shrink files and please don't shrink your whole database at once it's a bad idea I go to shrink files and I picked that log file if this was all green and I had emptied it through the backup my minimum would be zero here that I could shrink to it says this and it won't actually do it because it's full the free space is zero when it has space it will shrink down to a minimum I'm actually surprised it says that right now that that maybe I got you in management studio this is a most current version that's been released but you'll want to go to task shrink files after you've done a backup if you're one of the people that has one of these 500 gig log files that field you're whatever Drive and you can't expand it and you don't have room to back it up and that's one of the most common situations what you're gonna have to do is you're gonna have to hack at it and just hope you've got a full backup before you do anything I tell you or anybody else tells you to do on the internet take a full backup if your database save your job save heaven to update your resume in a hurry just just do it alright so let's pretend and we do have a full backup we know that if you have to go if you can't go the backup route go to go back to the options change the database to simple recovery model and hit OK and what that's gonna do I don't know if I'll show it right away but yep it chunked every transaction that in those update statements they're gone so because I'm and simple I can no longer recover to a point in time I can only go back to my full backup and any differentials I may have transaction logs are straight out I can't even run a tea log back up on this one now because Seco server won't allow it but I've emptied my file great my hard drive is still full what do I do go back where I told you over your tasks shrink files not database make sure you change to the log file here please and now it says my available space is 98 percent so I'm gonna shrink this down to 40 Meg's just so we can match and these are generally very quick they're not you don't always get exactly the amount of space back it's just a weird sequel server glitch almost or a feature sometimes you get it sometimes you won't I got 51 I said 41 but close enough for government work even though we don't work for the government as far as we know notice that I have now 51 Meg and a 41 Meg that's a whole lot better than 10 gigs or 500 gigs and when I go over here there's my data file there's my log file all is happy and good now if you really do need to have point in time recovery go back over here go to properties go to options set it back to full recovery model and run a full backup now that you're done with your maintenance you have one before now you have one after and you're you're now at a point that business can move forward and you can be confident than any future traction transactions will be fine so that's it if your log file is full and you'll the easy way to check that is during this graph there's a lot of other scripts that you can run and whatnot but if you're in a hurry and you got your manager screaming you know down your neck about the app is down get it emptied out preferably through a log backup secondary through changing it to simple shrink back to full and then get on with your life and you look like a hero so hopefully this answered your questions again put my contact information up here if it didn't go to Dallas TV acecomm there's contact information for me a slash blog if you want to read a bunch of stuff that I've written for junior DBAs accidental DBA a sysadmin people that don't know all the ins and outs of sequel I try to translate these things into a pretty common language away from sequel server up very often or and I would love it if you would follow me on Twitter makes me feel good and people follow me because I'm not insecure I just like to have people walking around asking me questions that I know the answer to everybody likes that so hope this helped you out have a wonderful

Channel: Kevin Hill

Views: 72,093

Keywords: SQL Server, log, 9002, database, error, drive full, crash, server

Id: Fh6e2xjFsv8

Length: 11min 17sec (677 seconds)

Published: Thu Mar 09 2017