April 28, 2008 at 11:14 am
Hi
I have a few hopefully simple questions.
We have a server with a single database on it (not including the tempdb, master etc.)
It would seem the .ldf for the database is WAY too large, its sitting at about 31Gb right now on a database which is nowhere NEAR that size..
I ran EXEC sp_helpdb myDB and got the following info (i've pipe delimited it just to make it easy to read in the post - I can provide the raw output if required)
name|fileid|filename|filegroup|size
myDB_Data|1|E:\MSSQL\Data\myDB\myDB_Data.MDF|PRIMARY|344064 KB
myDB_Log |2|E:\MSSQL\Logs\myDB\myDB_Log.LDF||NULL|31633472 KB
So first off, I'm thinking 31gb for the .ldf when the database is only 336mb is just plain wrong. I've looked around on google and found some ways to shrink the ldf, but before I even contemplate that, I'd like to know that its the approach i should take, as well as if theres a way someone can tell me to interogate the db to find what it needs to be (hopefully its just a lot of spare space in the ldf) that'd be great.
Also, as part of the maintenance plan, transaction logs are being backed up nightly and are set to delete any transaction logs that are older than 1 week.. I checked the history and the job has never failed.. but we have transaction logs stretching back to july of last year, so about 27Gb of em!
What I'd like is the warm and fuzzy from someone that deleting these old transaction logs wont hurt the db, and if anyone knows a good place where i can read the blurb about transaction logs in general, that'd be great. A webpage or a book would be fine.. I need to get up to speed on this and a lot of other stuff asap.
My assumption is with transaction logs, is that I only need to keep the ones which are newer than my last full backup? As if I had to restore (which is a thought that terrifies me right now seeing as I dont have a good handle on it yet), I would backup from my fullback.. then the newer transaction logs.. so any transaction logs which are older than the backup are a useless waste of space? If thats wrong, someone please correct me.
Thanks for your help guys..
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 28, 2008 at 12:04 pm
Well - the quick answer to your question is - it's 31GB now because at some point in the past, it needed to be that size. From possibly forgetting to run a log backup for a while in the past to some really ugly update process that might have "blown up the DB" or the log in the past.
As to knowing the size it "needs" to be - that's an interesting question. I'd start with a multiple of the "biggest" transaction log backup you've taken (say - 3-4 times that number). perhaps shrink it to that size, and see if it stays stable.
On the other hand - if you're not running out of disk space on your logs drive - why shrink it? Keep in mind that the log backups don't back up the transaction log FILE, it backs up the committed transactions since the last time the backup was run. So - the file itself being 31GB shouldn't hurt anything, and may actually help (since there wouldn't be any "auto-grow" events firing off for the transaction logs).
As to retention - that's a "business decision". I would definitely hold on to quite a few of those files (say - a month?), just in case the "full" backup happens to be bad. Meaning - assuming you have all of the transaction log backups - you could restore a FULL backup from a tape taken a month ago, restore the logs from then until now...and restore all of the way until now. Just in case the tape holding your FULL backups goes bad, and doesn't tell anyone.....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 1:36 pm
Thanks for the input Matt, I'm going to paste some more questions about your answer in bold to save retyping..
Matt Miller (4/28/2008)
As to knowing the size it "needs" to be - that's an interesting question. I'd start with a multiple of the "biggest" transaction log backup you've taken (say - 3-4 times that number). perhaps shrink it to that size, and see if it stays stable. - What would be the risk in lowering this file to this size? Changing things like this gets me pretty nervous until I know what the heck im doing, what'd happen if my .ldf went horribly wrong, assuming that it was done when I had no users in the database
On the other hand - if you're not running out of disk space on your logs drive - why shrink it? Keep in mind that the log backups don't back up the transaction log FILE, it backs up the committed transactions since the last time the backup was run. So - the file itself being 31GB shouldn't hurt anything, and may actually help (since there wouldn't be any "auto-grow" events firing off for the transaction logs). - The file being 31Gb wont kill me, and I'm inclined to let it alone, so long as I clear the old transaction logs, my hard drive issues should go away.. the database should be going away anyway in the near'ish future (sometime within the year hopefully) as we move to a new application... I'm thinking I probably don't want the stress of worrying about mucking up the database 😀
As to retention - that's a "business decision". I would definitely hold on to quite a few of those files (say - a month?), just in case the "full" backup happens to be bad. Meaning - assuming you have all of the transaction log backups - you could restore a FULL backup from a tape taken a month ago, restore the logs from then until now...and restore all of the way until now. Just in case the tape holding your FULL backups goes bad, and doesn't tell anyone.....- If we have to go back more than a day (or 2 on the w/end) on these backups we've got a whole other issue.. but I see your point, and keeping even a months worth wont be an issue, but keeping them back from the beginning of time is just plain silly in my mind.. i dunno why it was set up like this. I think you did, but could you please confirm my assumption about how the transaction logs work?
Thanks!
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
April 28, 2008 at 2:08 pm
torpkev (4/28/2008)
Thanks for the input Matt, I'm going to paste some more questions about your answer in bold to save retyping..
Following your lead - comments inline
Matt Miller (4/28/2008)
Well - the quick answer to your question is - it's 31GB now because at some point in the past, it needed to be that size. From possibly forgetting to run a log backup for a while in the past to some really ugly update process that might have "blown up the DB" or the log in the past.As to knowing the size it "needs" to be - that's an interesting question. I'd start with a multiple of the "biggest" transaction log backup you've taken (say - 3-4 times that number). perhaps shrink it to that size, and see if it stays stable. - What would be the risk in lowering this file to this size? Changing things like this gets me pretty nervous until I know what the heck im doing, what'd happen if my .ldf went horribly wrong, assuming that it was done when I had no users in the database
Well - there's no particular "danger". first - it won't let you shrink to anything smaller than what is active, so it's not like you're going to have to guess right or face killing your database. Again - my first course of action would be to do nothing, assuming it's not cramping anything else; if I did need the space - then I would pick a good time of the day, and do it then (a slow period).
The only downside to picking a size that is too small, is that the log would have to grow again. This can be a pain, so I try to avoid that, since it will usually happen when your server is really busy, and since this will really slow things down while it's growing the file - don't let that happen.
(snip)
As to retention - that's a "business decision". I would definitely hold on to quite a few of those files (say - a month?), just in case the "full" backup happens to be bad. Meaning - assuming you have all of the transaction log backups - you could restore a FULL backup from a tape taken a month ago, restore the logs from then until now...and restore all of the way until now. Just in case the tape holding your FULL backups goes bad, and doesn't tell anyone.....- If we have to go back more than a day (or 2 on the w/end) on these backups we've got a whole other issue.. but I see your point, and keeping even a months worth wont be an issue, but keeping them back from the beginning of time is just plain silly in my mind.. i dunno why it was set up like this. I think you did, but could you please confirm my assumption about how the transaction logs work?
Thanks!
Well - simply put - the transaction log keeps tracked of what modifications are being made to your data/schema, before/while they happen. Even when you're not using explicit transactions, anything actually making a change to the database (insert/update/delete's, adding/dropping/mod'ing objects, security changes, etc...) gets logged to the T-Log as it is happening. The idea is - anything bad happens, you can ROLL BACK/undo whatever was started, so you can get back to a stable state. Once the action is confirmed as "good" then the transaction (either explicit or implicit) is COMMITTED, and the changes are committed/made permanent in the database.
Now - depending on your Recovery model for that particular database, the transaction history gets retained for different amounts of time. In your case - you're using FULL recovery, meaning the transactions are kept until a LOG BACKUP occurs. During each log backup, all of the transactions that have been committed are backed up, and then "truncated" (meaning - the space is freed up inside of the T-log file, to be reused). Notice I mentioned all activity that was committed, so anything running for a long period of time that isn't committed won't be "backed up" until that transaction finishes.
So the combination of the FULL backup and the transaction log backups are complementary. The full represents a snapshot/compilation of all of the activity from the dawn of time until backup time on this particular database; the log backup represents the detail of the activity on the file for a specific time frame. So by combining a GOOD full backup and transaction log backups (assuming that you do regular LOG backups during the day, like say - every hour or 15 minutes for example) - you can restore pretty much up to the minute if something bad should happen. Or - you can "survive" a bad full backup (by starting with an older good full backup, and playing forward the transactions).
So no - I wouldn't keep transaction log backups from the "dawn of time". They'll take too long to replay should you need something. On the other hand - having some cushion is always a good thing, so a few weeks or months might be desirable.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 28, 2008 at 2:13 pm
Thanks again Matt, that explanation really helps (and is something I'll keep printed out here so I dont forget).
I appreciate the help and the time spent on it
--------------------------
I long for a job where my databases dont have any pesky users accessing them 🙂
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply