March 10, 2008 at 6:10 am
Hello,
I have a question I am sure alot of you wizards will find easy.
I have a transaction log file that is 5gb, the database is only slightly smaller than that. If I look in the DB properties, I see that the maximum file size growth is set to unrestricted, and is allocated just under 5Gb.
Now I am running out of space on a regular basis, my questions are, if I decided to set the maximum file size growth to, say 300mb, what would be the consequences of my actions? Or would it just shrink to that after the next full DB backup? I know its good practice to set such a restriction, I'm just wondering what would happen as its so big at the moment. And should I do it out of hours? I am also wondering what would happen if 300mb is not large enough an it hits that limit?
I hope I have been clear enough, and thanks for any advice.
Kind Regards,
D.
March 10, 2008 at 7:10 am
You need to do a couple of things.
1) Backup your transaction log, this will free up room within the log (not the physical size!)
2) shrink the log using dbcc shrinkfile or through the GUI.
This will sort out your space issue. Going forward you need to decide on a backup strategy for your logs (to keep them small). If you take nightly full backups and can afford to lose a days worth of data then put your database in simple recovery mode.
March 10, 2008 at 7:40 am
I tend to agree with what's listed above, but I think you need to understand how SQL works. All changes (new, updates, deletes) to data are written to the transaction log. This causes it to grow. A transaction log backup (not a full backup) removes this data and allows space in the log to be re-used. This is important for production systems (meaning you need to keep the backups) and it's different than how most applications log stuff. SQL has an error log that logs stuff admins need, but that's different than the transaction log.
Your transaction log should be sized to handle the load of transactions between log backups. You can backup every day, week, hour, minute, you choose. Just size the log to meet those needs.
Some people set logs to not grow, but if you need the space, activity stops in the database until you free up space or add more. That's why you should have an idea of how big your log should be. Running a backup over your chosen interval will give you an idea of how large it should be.
March 11, 2008 at 7:32 am
When you look at the database properties, what is the recovery mode? If it is simple, then this is a different problem. Let's first cover that.
Simple - Recovery only possible to last backup.
Bulk Logged - Recovery to last Log or DB backup (but smaller logs than full (sometimes))
Full - Point in time recovery, but Largest logs
Now If it is simple, then I suggest that you add some disk.
I don't encourage bulk logged mode, it doesn't give you much over simple in such a small DB.
I am going to assume that it is FULL (which the other answers also assumed).
The suggestions of doing log backups, and then shrinking the log file are a start. However, if you log continues to grow to this size, then you really shouldn't bother shrinking it. Doing log backups more often will help this situation.
You may also want to track your log backups size. This gives you a quick and easy way to see when you DB is changing the most. If it all occurs at one time of day, you can schedule your backups around that issue.
I will end with this; 10G of DB is not much. Most PC's can easily handle this. I really think at this point, you should think about just adding disk. Also read the BOL about Backups and Recovery. If there is any area a DBA should be very comfortable it is with backup and most important is RECOVERY! You need to understand this so when you need to do it under pressure you are very calm and know exactly what to do.
March 11, 2008 at 7:41 am
I agree with above posts.
To answer your initial question however - if you restrict the size, and your log backup strategy isn't aggressive enough, then you run the risk of causing the DB to stop. Meaning - since all activity is essentially logged - if you run out of space in the log, then activity stops until more space is given to that file.
----------------------------------------------------------------------------------
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?
March 11, 2008 at 10:04 am
Hello,
Firstly, thank you for you advice and your time. Bob, my backup is indeed full and I have configured a transaction log backup every 2 hours to see how large they are and a differential DB backup halfway though the day, the same time people go for lunch. With this I have just 2 questions...
Is it better to configure backups through the backup snap-in or through maintenance plans?
And is it better to add a new device and specify a folder rather than just adding a folder path while configuring the backup options?
Matt, thank you for getting back, your answer told me what I needed to know.
Kind Regards,
D.
March 12, 2008 at 5:33 am
Hello,
Any advice on my last couple of questions?
Regards,
D.
March 12, 2008 at 6:05 am
We do read these posts, and if anyone had something to say they would have done so.
From reading other posts, there are occational issues with various products and methods.
What's important is to get a backup (however you wish) and have a process in place to VERIFY that your backup process works on a regular basis. Ensure that you know how to restore when needed.
Remember you can do all the backups you want, but you only have to fail once at restoring for your job to become history.
March 12, 2008 at 9:16 am
Duran:
1. Is it better to configure backups through the backup snap-in or through maintenance plans?
Better is not really the question. It comes down to what you feel most comfortable with setting up and monitoring. If you're outside your comfort zone you have a better chance at not backing up properly.
2. Is it better to add a new device and specify a folder rather than just adding a folder path while configuring the backup options?
Again, this is a personal preference. I will tell you though that it is much easier doing point-in-time restores when all your backups (data and log backups) are in a single backup device versus separate files. Particularly if you're cutting log backups every 5 minutes and you need to restore a database to a point-in-time that is 8 hours after your last database backup.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 12, 2008 at 9:30 am
Timothy...
Is that a ferret holding a beer? Does it drink it, or bring it to you? 🙂
March 12, 2008 at 11:00 am
Yes, it is a ferret holding a beer. However his face has been blacked out to protect his identity so you were not supposed to know that. If that was the QotD you would have received 1 point!
The only problem is that he only fetches really lousy beer - domestic, rhymes with dud lite.
It's actually a photo I ran across on the internet a while back that I use for my Red Gate avatar and have posted on bulliten boards around our office during "show off your pets" week. Take that Mr. My Dog Wears a T-Shirt in accounting!
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
March 12, 2008 at 11:21 am
Thanks Timothy, thats great, I think I may alter the backups to go to a device instead.
Love the ferret, though I have always preffered Gophers, they bring back better beer. Maybe that's how they go there name? Gopher a beer anyone?
Regards,
D.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply