March 28, 2014 at 8:44 am
Also, I forgot to mention in my above post, in the future you should think about putting your log and data files on separate drives. Both files placed on the same drive can cause more performance issues.
March 28, 2014 at 8:48 am
I think we are a little bit stingy when it comes down to giving extra space to the log file. Lesson learned.
1 more question. When the log file gets filled, that means, we can't have any more transaction, does it put DB to read only mode or is it still in read and write mode?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 8:50 am
Come to think of it, I don't know how adding initial size to 1000 MB and set the growth to 1000 MB will help. The auto-growth of the log file is greater than 30GB and it is still getting filled.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 8:56 am
Here's a good article,
http://technet.microsoft.com/en-us/library/bb402876(v=sql.105).aspx
March 28, 2014 at 8:59 am
New Born DBA (3/28/2014)
I think we are a little bit stingy when it comes down to giving extra space to the log file. Lesson learned.1 more question. When the log file gets filled, that means, we can't have any more transaction, does it put DB to read only mode or is it still in read and write mode?
If you restrict your log file size then yes, you'll have problems. As long as file size is unrestricted or extremely large, you'll be fine. Autogrowths enable the data and log files to grow until the maximum size limit is reached.
After this, you may want to check your virtual log files. If there are large amounts, you might want to get that resolved,
http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/[/url]
March 28, 2014 at 9:00 am
New Born DBA (3/28/2014)
I think we are a little bit stingy when it comes down to giving extra space to the log file. Lesson learned.1 more question. When the log file gets filled, that means, we can't have any more transaction, does it put DB to read only mode or is it still in read and write mode?
I'll be honestly I'm stingy with the amount of Log space I give, especially if there are multiple databases on the same instance. It's a balance between giving a database enough resources for it to do what it needs while protecting the other databases on the instance. For this I always set Maxsize for data and logs to what I think they should be then adjust them from there.
For example if a log drive has 50 Gigs shared by 5 databases I'd set the Max Size for logs to maybe 8-10 Gigs on each. This way if someone runs a DML query that fills up the log file it'll die after 8-10 gigs instead of when the drive is full. It's bad enough to get alerts that a log file is full but it's even worse when a drive is full since this affects everything on that drive.
Just my thoughts 🙂
Sam
March 28, 2014 at 9:03 am
JoshDBGuy (3/28/2014)
Also, I forgot to mention in my above post, in the future you should think about putting your log and data files on separate drives. Both files placed on the same drive can cause more performance issues.
what kinds of issues we can run into?
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 9:13 am
New Born DBA (3/28/2014)
Come to think of it, I don't know how adding initial size to 1000 MB and set the growth to 1000 MB will help. The auto-growth of the log file is greater than 30GB and it is still getting filled.
Hold the phone... let's slow down a minutes according to the .png graphic you attached, your logfile growth is set to 10%, not 30GB. And, your log file was only 121MB.
You also said that the DBAs recently changed the Recovery Model to "simple" which means that it's impossible to backup the log files now.
As someone else stated, the initial setting for both the database and the log file are horrible and need to be changed.
All of that brings up some serious questions but the biggest question that I have is... why are YOU doing this type of stuff? The DBAs should be doing this for you.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 28, 2014 at 9:18 am
Jeff Moden (3/28/2014)
New Born DBA (3/28/2014)
Come to think of it, I don't know how adding initial size to 1000 MB and set the growth to 1000 MB will help. The auto-growth of the log file is greater than 30GB and it is still getting filled.Hold the phone... let's slow down a minutes according to the .png graphic you attached, your logfile growth is set to 10%, not 30GB. And, your log file was only 121MB.
You also said that the DBAs recently changed the Recovery Model to "simple" which means that it's impossible to backup the log files now.
As someone else stated, the initial setting for both the database and the log file are horrible and need to be changed.
All of that brings up some serious questions but the biggest question that I have is... why are YOU doing this type of stuff? The DBAs should be doing this for you.
I agree with you but what If I say we don't have any professional DBAs. I and one of my co-worker became DBA by just learning on our own and by reading different blogs.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 9:21 am
The initial size is 121 and it is also set to auto-grow by 10%, restricted growth to 2097152MB which is not 30GB (My bad)
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 9:22 am
New Born DBA (3/28/2014)
Come to think of it, I don't know how adding initial size to 1000 MB and set the growth to 1000 MB will help. The auto-growth of the log file is greater than 30GB and it is still getting filled.
Initial log file size is 121 MB and the autogrowth is 10 percent which currently equates to about 12 MB. Where do you see 30 GB?
March 28, 2014 at 9:24 am
New Born DBA (3/28/2014)
Please see the attachment.
That's actually two terabytes. That's your maximum file size, not the autogrowth amount. 10% is the amount your log file currently grows. If you want to do it through the GUI you can click the elipses and change the settings. If you're trying to learn how to do it yourself, I'd recommend changing the settings through the GUI and then clicking the "script" button and scripting it out to see how it looks through T-SQL code. You can then save that for your future use and reference.
March 28, 2014 at 9:35 am
New Born DBA (3/28/2014)
JoshDBGuy (3/28/2014)
Also, I forgot to mention in my above post, in the future you should think about putting your log and data files on separate drives. Both files placed on the same drive can cause more performance issues.what kinds of issues we can run into?
One issue is the potential of one log or data file filling up the entire drive preventing the other data and log files from growing. This of course could be resolved by setting maxsize, but this isn't the best in all situations.
Another issue is performance given a single drive can only read/write to a single point on the disk (spindle) at one time. So if you have a process that's reading between the data and log file concurrently the drive has to move it's head constantly between those two points on the drive. If they are on different drives it's not such a problem. With that in mind TempDB being on it's own drive as well is a good option too since it can have the same issue.
If you're using a RAID or SAN where the data is duplicated across multiple disks it may not be such a huge deal since theoretically the OS can read/write to two different points of a partition at the same time, but that may not always be the case depending on how the data is stored.
These are the two biggies I can think of, but there may be others.
March 28, 2014 at 9:36 am
JoshDBGuy (3/28/2014)
New Born DBA (3/28/2014)
Please see the attachment.That's actually two terabytes. That's your maximum file size, not the autogrowth amount. 10% is the amount your log file currently grows. If you want to do it through the GUI you can click the elipses and change the settings. If you're trying to learn how to do it yourself, I'd recommend changing the settings through the GUI and then clicking the "script" button and scripting it out to see how it looks through T-SQL code. You can then save that for your future use and reference.
I am going to have to learn a lot. I just started this DBA work 3 and a half months ago. Still al long long way to go.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
March 28, 2014 at 9:41 am
samalex (3/28/2014)
One issue is the potential of one log or data file filling up the entire drive preventing the other data and log files from growing.
We already created a partitioned and put all the other mdf and ldf files on separate drive.
"He who learns for the sake of haughtiness, dies ignorant. He who learns only to talk, rather than to act, dies a hyprocite. He who learns for the mere sake of debating, dies irreligious. He who learns only to accumulate wealth, dies an atheist. And he who learns for the sake of action, dies a mystic."[/i]
Viewing 15 posts - 16 through 30 (of 52 total)
You must be logged in to reply to this topic. Login to reply