Log file Raid 1 VS Raid5 on EMC symmetrix DMX

Hi! Ever since I worked with sql server I have been placing sql log files on
Raid 1, which is the recomendation I read everywhere (from books, internet
etc).
Now, we are going to use EMC symmetrix DMX and folks here want to save some
money by configuring RAID 5 for both datafile and log file. Their
justification is, EMC DMX is totally different architecture and its fast, so
there won't be much difference between Raid5 and Raid1 for Sql server.
Is this true? Is anyone using EMC for Sql log file with Raid 5
configuration? Is there any whitepaper or artical that specifically talks
Sql log file on EMC.
I appreciate your help.my inclination is not to believe it. I have encountered
situations where high-end storage systems have absolutely
horrible performance because some one advise RAID5 without
actually running tests to prove it
i strongly recommend that you get IOmeter, originally
developed at Intel.
http://sourceforge.net/projects/iometer/
test the RAID configuration with the full size of the
expect data and log file using random read/writes
i would also advise testing to see if a large cache on the
storage controller has any value over a small cache,
>--Original Message--
>Hi! Ever since I worked with sql server I have been
placing sql log files on
>Raid 1, which is the recomendation I read everywhere
(from books, internet
>etc).
>Now, we are going to use EMC symmetrix DMX and folks here
want to save some
>money by configuring RAID 5 for both datafile and log
file. Their
>justification is, EMC DMX is totally different
architecture and its fast, so
>there won't be much difference between Raid5 and Raid1
for Sql server.
>Is this true? Is anyone using EMC for Sql log file with
Raid 5
>configuration? Is there any whitepaper or artical that
specifically talks
>Sql log file on EMC.
>I appreciate your help.
>
>.
>|||Do you mean they want to put the log file and the data on the same Raid 5 or
just 2 different raid 5's. If it's the first then I would make them prove
to you that it can handle the load at your peak transaction rates and not to
forget during backups. If you have a low enough transaction rate and a fast
enough disk subsystem you may not notice but if your going to an EMC then
you most likely have a fair amount of transactions. If they will be on 2
different array's and nothing else is on the array with the logs that is a
different story. But Raid 5 is still worse on writes than a Raid 1 and
won't save any money since a Raid 1 is usually smaller than a similiar Raid
5.
--
Andrew J. Kelly
SQL Server MVP
"james" <kush@.brandes.com> wrote in message
news:%23nS1c2fUDHA.3308@.tk2msftngp13.phx.gbl...
> Hi! Ever since I worked with sql server I have been placing sql log files
on
> Raid 1, which is the recomendation I read everywhere (from books, internet
> etc).
> Now, we are going to use EMC symmetrix DMX and folks here want to save
some
> money by configuring RAID 5 for both datafile and log file. Their
> justification is, EMC DMX is totally different architecture and its fast,
so
> there won't be much difference between Raid5 and Raid1 for Sql server.
> Is this true? Is anyone using EMC for Sql log file with Raid 5
> configuration? Is there any whitepaper or artical that specifically talks
> Sql log file on EMC.
> I appreciate your help.
>
>|||How can you say Raid 5 is cheaper than Raid 1 since you need at least 3
drives for Raid 5 and only 2 for a Raid 1. Most controllers do both with
equal ease. I must be missing your point on that one? As for the second
point I basically agree with you in that the larger SANs abstract the actual
disk configurations and have tons of cache to soften the blow so to speak.
But it is still a good idea to to separate the logs from the data and tempdb
when possible. On a busy system it still can bottleneck on the channel or
bus if too much is placed together.
--
Andrew J. Kelly
SQL Server MVP
"Kevin" <ReplyTo@.Newsgroups.only> wrote in message
news:uhUrRpgUDHA.2184@.TK2MSFTNGP10.phx.gbl...
> raid 5 is significantly cheaper than raid 1, even if you only use 3
physical
> disks per array.
> Our experience has been that the very large, very intelligent caching
> engines on enterprise SAN disk devices can make the need for RAID 1 for
> write performance completely moot because nothing gets written through to
> disk synchronously anyway.
> --
> ----
> The views expressed here are my own
> and not of my employer.
> ----
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:O$j$umgUDHA.392@.TK2MSFTNGP11.phx.gbl...
> > Do you mean they want to put the log file and the data on the same Raid
5
> or
> > just 2 different raid 5's. If it's the first then I would make them
prove
> > to you that it can handle the load at your peak transaction rates and
not
> to
> > forget during backups. If you have a low enough transaction rate and a
> fast
> > enough disk subsystem you may not notice but if your going to an EMC
then
> > you most likely have a fair amount of transactions. If they will be on
2
> > different array's and nothing else is on the array with the logs that is
a
> > different story. But Raid 5 is still worse on writes than a Raid 1 and
> > won't save any money since a Raid 1 is usually smaller than a similiar
> Raid
> > 5.
> >
> > --
> >
> > Andrew J. Kelly
> > SQL Server MVP
> >
> >
> > "james" <kush@.brandes.com> wrote in message
> > news:%23nS1c2fUDHA.3308@.tk2msftngp13.phx.gbl...
> > > Hi! Ever since I worked with sql server I have been placing sql log
> files
> > on
> > > Raid 1, which is the recomendation I read everywhere (from books,
> internet
> > > etc).
> > > Now, we are going to use EMC symmetrix DMX and folks here want to save
> > some
> > > money by configuring RAID 5 for both datafile and log file. Their
> > > justification is, EMC DMX is totally different architecture and its
> fast,
> > so
> > > there won't be much difference between Raid5 and Raid1 for Sql server.
> > > Is this true? Is anyone using EMC for Sql log file with Raid 5
> > > configuration? Is there any whitepaper or artical that specifically
> talks
> > > Sql log file on EMC.
> > > I appreciate your help.
> > >
> > >
> > >
> >
> >
>|||Thanks Andrew, Actually its going to be on two different Raid. And they
thought since it will have 16 GB of cache (in EMC), As far as sql server
goes it won't have to concern about Raid configuration underneath. Which
kind of make sense, But still wanted to throw my concern out there and
hoping to find out any White Paper from Microsoft or EMC or any artical
which may explain it in more detail.
thanks again.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:O$j$umgUDHA.392@.TK2MSFTNGP11.phx.gbl...
> Do you mean they want to put the log file and the data on the same Raid 5
or
> just 2 different raid 5's. If it's the first then I would make them prove
> to you that it can handle the load at your peak transaction rates and not
to
> forget during backups. If you have a low enough transaction rate and a
fast
> enough disk subsystem you may not notice but if your going to an EMC then
> you most likely have a fair amount of transactions. If they will be on 2
> different array's and nothing else is on the array with the logs that is a
> different story. But Raid 5 is still worse on writes than a Raid 1 and
> won't save any money since a Raid 1 is usually smaller than a similiar
Raid
> 5.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "james" <kush@.brandes.com> wrote in message
> news:%23nS1c2fUDHA.3308@.tk2msftngp13.phx.gbl...
> > Hi! Ever since I worked with sql server I have been placing sql log
files
> on
> > Raid 1, which is the recomendation I read everywhere (from books,
internet
> > etc).
> > Now, we are going to use EMC symmetrix DMX and folks here want to save
> some
> > money by configuring RAID 5 for both datafile and log file. Their
> > justification is, EMC DMX is totally different architecture and its
fast,
> so
> > there won't be much difference between Raid5 and Raid1 for Sql server.
> > Is this true? Is anyone using EMC for Sql log file with Raid 5
> > configuration? Is there any whitepaper or artical that specifically
talks
> > Sql log file on EMC.
> > I appreciate your help.
> >
> >
> >
>

Log File placement

Hi All,
We are using RAID5 for the data files and RAID 1 for the Log file.My assumption is that as long as you put only one Log file on the Log drive,you get the maximum performance as Sql Server does a Sequential Read/Write,there by gaining the performance,as compared to Random Read/Write.

My question here is that we are planning to put some dump file on the same drive where the log files are placed(RAID1).
Is this going to drag down the performance?Basically if I put any other file other than the one Log file then would it become a Random Read??Re: Hi All We are using RAID5 for the data files and RAID 1 for the Log file.My assumption is that as long as you put only one Log file on the Log drive,you get the maximum performance as Sql Server does a Sequential Read/Write,there by gaining the performance,as compared to Random Read/Write. My question here is that we are planning to put some dump file on the same drive where the log files are placed(RAID1).
Is this going to drag down the performance?Basically if I put any other file other than the one Log file then would it become a Random Read??

Q1 We are planning to put some dump file on the same drive where the log files are placed(RAID1). Is this going to drag down the performance?
A1 Whenever you actively dump to the RAID 1 volume, active transactions would be expected to be forced to share [disk IO resources] with the dump process(es) via the DB log files which (you stated) also reside there.

Q2 If I put any other file other than the one Log file then would it become a Random Read??
A2 As long as a given 'resident file' is not being subject to any IO operations (R/W)? it shouldn't force any shareing [of disk IO resources] with the DB log files which also reside there.

Log File Help!

I have a database that has a data file of size 50GB (500 million records). It's a lot of data. For speed, we have set up a 9 disc SCSI RAID 5 (4 drives striped, mirrored, and parity). Total space of 130GB. As I said, the data file consumes 50GB.

I needed to run a pretty vanila SQL INSERT/UPDATE on the data. 7 hours later, the query had failed due to a "log file full" error. The log file was at 80+GB and shortly dropped to 1MB by itself.

I'm running SQL Server 2000 SP3. Recovery model is simple and auto shrink is enabled. What could possibly consume 80GB of disc? That is way larger than the amount of data that I have!

What is the best solution? Get a 200GB IDE disc to house the log file?

I need a solution fast! This project is way over schedule; these 7 hour statement run times are killing me.

Thank you in advance!you will be better off breaking your operation into separate insert and then update. but i'd recommend to replace insert with bulk insert, and update with bcp...out that would result in the contents of the file with desired values, then truncate the table, and then bulk insert clean "updated" data. from what you describe it sounds like you are updating the majority of 500 mil records.|||SCSI RAID 5 (4 drives striped, mirrored, and parity)

I don't know how you set this up, but this "is not" RAID 5. What you should probably have done from a performance perspective was have your logs on RAID 1 or 10 and the data files on RAID 5.

In addition, it does sound like you need to break your operation up into smaller steps. If you want to post the script, we can probably give you some pretty fast pointers on how to make it run faster and not use as much log.|||Thanks for the feedback!

Last night, I ran out and bought a 250GB IDE disc, put the log file on that and reran. It finished in 5.5 hours and consumed 150GB of log (which shortly after shrunk down to 1MB). That's a damn lot of log space! My entire data file is only 50GB!

Here are is the exact SQL I ran:

INSERT INTO Domains (Domain)
SELECT DISTINCT Domain FROM Stages WHERE Domain NOT IN (SELECT Domain FROM Domains)

UPDATE Stages SET DomainID = (SELECT Domains.[ID] FROM Domains WHERE Domains.Domain = Stages.Domain)

Now for the more complex TSQL... Hopefully the 250GB will hold and everything will be complete in 48 hours...|||I don't suppose you've wrapped a BEGIN TRANSACTION around this whole thing have you? Just for fun, put a CHECKPOINT between each one of your statement. This shouldn't help keep the log small, but it will force it to write to disk after each statement. You will want to begin and commit each statement seperately also.|||Now, I didn't use transactions at all. I ran the exact SQL that I posted in a Query Analyzer window.

That piece is done. However, if you can suggest an optimization for the dedup process, I would be really grateful. I'm posting that under a separate thread: "Optimize Dedup TSQL?"

Log Cache Hit Ratio + Buffer Cache Hit Ratio

Thanks in advance for any info.
SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
RAID 5.
What does one do when the Buffer Cache is at (and stays at) 99% and the Log
Cache is at 99%? Faster processors, more memory, change the RAID config?
Note: this is not daily activity on the box, but a monthly process that has
gone from 18 hours to 28 hours {and counting} with no changes.
Thanks,
MorganIf you're talking about the hit ratios, those are both
excellent numbers. Anything over 95% is optimal. When
those numbers start to drop, in general more memory is
the key, but be sure to check into it more closely before
purchasing hardware.
>--Original Message--
>Thanks in advance for any info.
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I
think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays
at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change
the RAID config?
>Note: this is not daily activity on the box, but a
monthly process that has
>gone from 18 hours to 28 hours {and counting} with no
changes.
>
>Thanks,
>Morgan
>
>.
>|||Morgan,
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
99% cache hit ratios are optimal.
This means that the server is practically always getting data from the
caches, rather than going to the disk subsystem.
It sounds more like the work needs to be done in the monthly process, as far
as optimizing it goes. Your hardware seems to be performing optimally.
When did it go from 18 to 28 hours? Suddenly? Over time? I would start
suspecting one of the following:
That perhaps either:
A.) The data set has grown
B.) Someone changed one of the queries
C.) Someone dropped a key index
D.) One or more of the above. :-)
James Hokes|||In addition to the other comments the log can be a bottle neck if on a Raid
5 and especially with the data. The Log cache ration doesn't mean much for
writes so you may want to check your disk queues on the Raid that houses the
log file anyway and make sure it has no issues. TempDB can be another place
to look. Usually month end type process does a lot of activity that uses
temp tables and you can have bottlenecks there as well. Essentially you
need to monitor the cpu and disk counters while this process is happening to
see where the bottleneck is coming from. If the data cache ratio is 99%
chances are more memory won't help much. If your procedures are optimized
then it will boil down to disk or cpu. But how sure are you that they are
that optimized?
--
Andrew J. Kelly
SQL Server MVP
"Morgan" <mfears@.spamcop.net> wrote in message
news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> Thanks in advance for any info.
> SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
RAM.
> RAID 5.
> What does one do when the Buffer Cache is at (and stays at) 99% and the
Log
> Cache is at 99%? Faster processors, more memory, change the RAID config?
> Note: this is not daily activity on the box, but a monthly process that
has
> gone from 18 hours to 28 hours {and counting} with no changes.
>
> Thanks,
> Morgan
>|||On Fri, 26 Dec 2003 13:42:08 -0500, "Morgan" wrote:
>SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB RAM.
>RAID 5.
>What does one do when the Buffer Cache is at (and stays at) 99% and the Log
>Cache is at 99%? Faster processors, more memory, change the RAID config?
>Note: this is not daily activity on the box, but a monthly process that has
>gone from 18 hours to 28 hours {and counting} with no changes.
In addition to what James, James and Andrew said, look at your
statistics. As your database grows over time, the distribution of values
across columns can change substantially. Out of date stats can
contribute to bad query plans, leading to long runs.
Are you able to pinpoint which part(s) of your monthly process chews the
most time?
cheers,
Ross
--
Ross McKay, WebAware Pty Ltd
"The lawn could stand another mowing; funny, I don't even care"
- Elvis Costello|||Thanks to everyone for their insight.
I was somewhat under the impression that if they're both max'd out, then
it's time to start looking at HW. I know for a fact the procedures need
"help", but it's an inherited process that for the first time this month,
took much longer than usual, which is why I knee-jerked my post. To the best
of my knowledge, none of the underlying objects has changed, but upon
further review (below), several tables are missing indicies where they are
required. I also saw quite a bit of parallelism happening via SP_WHO, and
have begun to question the multi-proc machine, which for better or worse, is
most likely fine.
I ran Profiler off-and-on (didn't want to make it any worse than it was),
and found a single procedure that appeared to be somewhat of a bottleneck,
lots of extensive reads for a single query, minimal (single, actually)
writes. Fortunately, a predecessor had the forethought to not use temp
tables, however, the tables in use are not properly indexed. Upon review of
the primary table in the afore mentioned procedure, it turns out there are
no indexes or unique constraints; along with a truncate table statement
against the table at the start of the procedure. I know the cost of a
properly placed index will fix the amount of reads against the suspect table
I saw in Profiler. Top all this off with cursors galore (note: not the
author, but the maintainer), and you can easily appreciate the mess I'm
dealing with. I'm reading this as the beginning of the end for the current
processes... ;)
Thanks again,
Morgan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:uOYObWMzDHA.2408@.tk2msftngp13.phx.gbl...
> In addition to the other comments the log can be a bottle neck if on a
Raid
> 5 and especially with the data. The Log cache ration doesn't mean much for
> writes so you may want to check your disk queues on the Raid that houses
the
> log file anyway and make sure it has no issues. TempDB can be another
place
> to look. Usually month end type process does a lot of activity that uses
> temp tables and you can have bottlenecks there as well. Essentially you
> need to monitor the cpu and disk counters while this process is happening
to
> see where the bottleneck is coming from. If the data cache ratio is 99%
> chances are more memory won't help much. If your procedures are optimized
> then it will boil down to disk or cpu. But how sure are you that they are
> that optimized?
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Morgan" <mfears@.spamcop.net> wrote in message
> news:OJWUJA%23yDHA.2540@.tk2msftngp13.phx.gbl...
> > Thanks in advance for any info.
> >
> > SQL Server Standard/ Windows 2000 AS, Dual Proc (1.4's I think), 1.5GB
> RAM.
> > RAID 5.
> >
> > What does one do when the Buffer Cache is at (and stays at) 99% and the
> Log
> > Cache is at 99%? Faster processors, more memory, change the RAID config?
> > Note: this is not daily activity on the box, but a monthly process that
> has
> > gone from 18 hours to 28 hours {and counting} with no changes.
> >
> >
> >
> > Thanks,
> > Morgan
> >
> >
>

Log Backups - Why bother with every ten minutes?

Most of my clients employ some reasonable RAID setups - mirroring at
least. Let's say this is the minimum configuration:
C: - OS
D: Data drives - (2 Mirrored drives - seperate controller)
E: Log Drives - (2 Mirrored drives - seperate controller)
Assume full backups happen every night at midnight, and take 15
minutes.
Why would I make transaction log backups any more frequently than
nightly? There are many respectable people that recommend backups of
transaction logs hourly, or even every ten minutes. Why?
The way I see it is if both the data drives fail, we can still do a log
backup after the failure and perform a complete recovery using last
nights backups. If both log drives fail, we simply get a new backup,
and move the log file until we replace the log drives.
If we backup the log every ten minutes, we introduce a huge recovery
headache by needing to restore evey log backup until the point of
failure - why would I want to do that?
Thanks in advance for your opinions.
- Sean
The worse-case scenario is that you lose your log. Since you cannot backup
the database without the log, your only recourse is to restore from your
last good database backup and then apply log backups. The frequency of your
log backups depends on how much data loss is acceptable in the unlikely
event both log drives are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124673132.337738.144600@.g43g2000cwa.googlegr oups.com...
> Most of my clients employ some reasonable RAID setups - mirroring at
> least. Let's say this is the minimum configuration:
> C: - OS
> D: Data drives - (2 Mirrored drives - seperate controller)
> E: Log Drives - (2 Mirrored drives - seperate controller)
> Assume full backups happen every night at midnight, and take 15
> minutes.
> Why would I make transaction log backups any more frequently than
> nightly? There are many respectable people that recommend backups of
> transaction logs hourly, or even every ten minutes. Why?
> The way I see it is if both the data drives fail, we can still do a log
> backup after the failure and perform a complete recovery using last
> nights backups. If both log drives fail, we simply get a new backup,
> and move the log file until we replace the log drives.
> If we backup the log every ten minutes, we introduce a huge recovery
> headache by needing to restore evey log backup until the point of
> failure - why would I want to do that?
> Thanks in advance for your opinions.
> - Sean
>
|||Thanks Dan - so you are saying the SQL Server will not allow you to get
a full backup if the log drives fail? Wow. I didn't know that.
Even so - if the log drives fail - we haven't actually lost data yet,
right? Can't we add a new log file to keep going? Can we alter the
database and set it to Simple recovery to get a backup?
- Sean
|||> Even so - if the log drives fail - we haven't actually lost data yet,
> right?
Data modifications are written to the log before the data files. The log
buffer is flushed during commit and this is what guarantees that your data
changes are permanent.

> Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
Even if you could just add a new log, it would be a bad idea to do so. When
you lose your log, committed data may be lost and both logical and physical
database integrity would be at best questionable. Processing against a
potentially corrupt database can make matters worse. A database backup is
pretty much useless without the log.
There are undocumented and unsupported techniques to rebuild the transaction
log. This is kind of thing is done should be done as last resort (i.e. no
database backup available) and under the direction of Microsoft PSS. Of
course, you'll never find yourself in this nasty situation as long as you
have a sound recovery strategy.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124677496.824240.113320@.g14g2000cwa.googlegr oups.com...
> Thanks Dan - so you are saying the SQL Server will not allow you to get
> a full backup if the log drives fail? Wow. I didn't know that.
> Even so - if the log drives fail - we haven't actually lost data yet,
> right? Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
> - Sean
>
|||On 21 Aug 2005 18:12:12 -0700, SeanNerd wrote:
(snip)
>Why would I make transaction log backups any more frequently than
>nightly?
Hi Sean,
Because the log file keeps growing until you take a log backup. After
that, space will be reused. Depending on the volatility of your data,
this might be irrelevant, or it might cause your log file to grow to a
multiple of the size of the data file.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

Log Backups - Why bother with every ten minutes?

Most of my clients employ some reasonable RAID setups - mirroring at
least. Let's say this is the minimum configuration:
C: - OS
D: Data drives - (2 Mirrored drives - seperate controller)
E: Log Drives - (2 Mirrored drives - seperate controller)
Assume full backups happen every night at midnight, and take 15
minutes.
Why would I make transaction log backups any more frequently than
nightly? There are many respectable people that recommend backups of
transaction logs hourly, or even every ten minutes. Why?
The way I see it is if both the data drives fail, we can still do a log
backup after the failure and perform a complete recovery using last
nights backups. If both log drives fail, we simply get a new backup,
and move the log file until we replace the log drives.
If we backup the log every ten minutes, we introduce a huge recovery
headache by needing to restore evey log backup until the point of
failure - why would I want to do that?
Thanks in advance for your opinions.
- SeanThe worse-case scenario is that you lose your log. Since you cannot backup
the database without the log, your only recourse is to restore from your
last good database backup and then apply log backups. The frequency of your
log backups depends on how much data loss is acceptable in the unlikely
event both log drives are lost.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124673132.337738.144600@.g43g2000cwa.googlegroups.com...
> Most of my clients employ some reasonable RAID setups - mirroring at
> least. Let's say this is the minimum configuration:
> C: - OS
> D: Data drives - (2 Mirrored drives - seperate controller)
> E: Log Drives - (2 Mirrored drives - seperate controller)
> Assume full backups happen every night at midnight, and take 15
> minutes.
> Why would I make transaction log backups any more frequently than
> nightly? There are many respectable people that recommend backups of
> transaction logs hourly, or even every ten minutes. Why?
> The way I see it is if both the data drives fail, we can still do a log
> backup after the failure and perform a complete recovery using last
> nights backups. If both log drives fail, we simply get a new backup,
> and move the log file until we replace the log drives.
> If we backup the log every ten minutes, we introduce a huge recovery
> headache by needing to restore evey log backup until the point of
> failure - why would I want to do that?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks Dan - so you are saying the SQL Server will not allow you to get
a full backup if the log drives fail? Wow. I didn't know that.
Even so - if the log drives fail - we haven't actually lost data yet,
right? Can't we add a new log file to keep going? Can we alter the
database and set it to Simple recovery to get a backup?
- Sean|||> Even so - if the log drives fail - we haven't actually lost data yet,
> right?
Data modifications are written to the log before the data files. The log
buffer is flushed during commit and this is what guarantees that your data
changes are permanent.

> Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
Even if you could just add a new log, it would be a bad idea to do so. When
you lose your log, committed data may be lost and both logical and physical
database integrity would be at best questionable. Processing against a
potentially corrupt database can make matters worse. A database backup is
pretty much useless without the log.
There are undocumented and unsupported techniques to rebuild the transaction
log. This is kind of thing is done should be done as last resort (i.e. no
database backup available) and under the direction of Microsoft PSS. Of
course, you'll never find yourself in this nasty situation as long as you
have a sound recovery strategy.
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124677496.824240.113320@.g14g2000cwa.googlegroups.com...
> Thanks Dan - so you are saying the SQL Server will not allow you to get
> a full backup if the log drives fail? Wow. I didn't know that.
> Even so - if the log drives fail - we haven't actually lost data yet,
> right? Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
> - Sean
>|||On 21 Aug 2005 18:12:12 -0700, SeanNerd wrote:
(snip)
>Why would I make transaction log backups any more frequently than
>nightly?
Hi Sean,
Because the log file keeps growing until you take a log backup. After
that, space will be reused. Depending on the volatility of your data,
this might be irrelevant, or it might cause your log file to grow to a
multiple of the size of the data file.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Log Backups - Why bother with every ten minutes?

Most of my clients employ some reasonable RAID setups - mirroring at
least. Let's say this is the minimum configuration:
C: - OS
D: Data drives - (2 Mirrored drives - seperate controller)
E: Log Drives - (2 Mirrored drives - seperate controller)
Assume full backups happen every night at midnight, and take 15
minutes.
Why would I make transaction log backups any more frequently than
nightly? There are many respectable people that recommend backups of
transaction logs hourly, or even every ten minutes. Why?
The way I see it is if both the data drives fail, we can still do a log
backup after the failure and perform a complete recovery using last
nights backups. If both log drives fail, we simply get a new backup,
and move the log file until we replace the log drives.
If we backup the log every ten minutes, we introduce a huge recovery
headache by needing to restore evey log backup until the point of
failure - why would I want to do that?
Thanks in advance for your opinions.
- SeanThe worse-case scenario is that you lose your log. Since you cannot backup
the database without the log, your only recourse is to restore from your
last good database backup and then apply log backups. The frequency of your
log backups depends on how much data loss is acceptable in the unlikely
event both log drives are lost.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124673132.337738.144600@.g43g2000cwa.googlegroups.com...
> Most of my clients employ some reasonable RAID setups - mirroring at
> least. Let's say this is the minimum configuration:
> C: - OS
> D: Data drives - (2 Mirrored drives - seperate controller)
> E: Log Drives - (2 Mirrored drives - seperate controller)
> Assume full backups happen every night at midnight, and take 15
> minutes.
> Why would I make transaction log backups any more frequently than
> nightly? There are many respectable people that recommend backups of
> transaction logs hourly, or even every ten minutes. Why?
> The way I see it is if both the data drives fail, we can still do a log
> backup after the failure and perform a complete recovery using last
> nights backups. If both log drives fail, we simply get a new backup,
> and move the log file until we replace the log drives.
> If we backup the log every ten minutes, we introduce a huge recovery
> headache by needing to restore evey log backup until the point of
> failure - why would I want to do that?
> Thanks in advance for your opinions.
> - Sean
>|||Thanks Dan - so you are saying the SQL Server will not allow you to get
a full backup if the log drives fail? Wow. I didn't know that.
Even so - if the log drives fail - we haven't actually lost data yet,
right? Can't we add a new log file to keep going? Can we alter the
database and set it to Simple recovery to get a backup?
- Sean|||> Even so - if the log drives fail - we haven't actually lost data yet,
> right?
Data modifications are written to the log before the data files. The log
buffer is flushed during commit and this is what guarantees that your data
changes are permanent.
> Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
Even if you could just add a new log, it would be a bad idea to do so. When
you lose your log, committed data may be lost and both logical and physical
database integrity would be at best questionable. Processing against a
potentially corrupt database can make matters worse. A database backup is
pretty much useless without the log.
There are undocumented and unsupported techniques to rebuild the transaction
log. This is kind of thing is done should be done as last resort (i.e. no
database backup available) and under the direction of Microsoft PSS. Of
course, you'll never find yourself in this nasty situation as long as you
have a sound recovery strategy.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SeanNerd" <sean@.coolbean.com> wrote in message
news:1124677496.824240.113320@.g14g2000cwa.googlegroups.com...
> Thanks Dan - so you are saying the SQL Server will not allow you to get
> a full backup if the log drives fail? Wow. I didn't know that.
> Even so - if the log drives fail - we haven't actually lost data yet,
> right? Can't we add a new log file to keep going? Can we alter the
> database and set it to Simple recovery to get a backup?
> - Sean
>|||On 21 Aug 2005 18:12:12 -0700, SeanNerd wrote:
(snip)
>Why would I make transaction log backups any more frequently than
>nightly?
Hi Sean,
Because the log file keeps growing until you take a log backup. After
that, space will be reused. Depending on the volatility of your data,
this might be irrelevant, or it might cause your log file to grow to a
multiple of the size of the data file.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Log and Data on the same RAID?

Maybe my question is too simple because I dont know much of RAID,
but I try:
What is probably "better": One RAID of 4 Disks, where datafile and logfile a
re
placed on, or Two RAIDs of each 2 Disks, where one RAID contains the datafil
e
and the other RAID contains the logfile?
JoachimAs all questions related to computers, the correct response is 'It depends'.
A mirror RAID drive would be good for the log file, a mirrored drive for the
datafile is less than optimal. If you only had 5 drives, you could mirror
the drive for the log and create a RAID 5 for the datafiles.
If have to work with only 4 drives, additional informational needs would
include: type of database (OLTP/OLAP), Number of users, amount of traffic,
concurrency of traffic, average size of queries, amount of INSERT/UPDATE
traffic, etc.
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||It depends on a lot of things but the biggest of which is how many reads to
writes you do. If you have mostly read only operations you may get better
read results by having a 4 disk Raid 10. If you have a lot of writes you may
need to separate the log file from the data files. If you only have 4 disks
you have little choice but to use two Raid 1's.
Andrew J. Kelly SQL MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||I would add to that, IF the RAID array controller is suitable for
write caching (battery backed memory on the controller and other wise
certified for that sort of thing), then the write penalty for RAID5 is
somewhat minimized.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Aug 2006 10:25:44 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:

>As all questions related to computers, the correct response is 'It depends'
.
>A mirror RAID drive would be good for the log file, a mirrored drive for th
e
>datafile is less than optimal. If you only had 5 drives, you could mirror
>the drive for the log and create a RAID 5 for the datafiles.
>If have to work with only 4 drives, additional informational needs would
>include: type of database (OLTP/OLAP), Number of users, amount of traffic,
>concurrency of traffic, average size of queries, amount of INSERT/UPDATE
>traffic, etc.

Log and Data on the same RAID?

Maybe my question is too simple because I dont know much of RAID,
but I try:
What is probably "better": One RAID of 4 Disks, where datafile and logfile are
placed on, or Two RAIDs of each 2 Disks, where one RAID contains the datafile
and the other RAID contains the logfile?
JoachimAs all questions related to computers, the correct response is 'It depends'.
A mirror RAID drive would be good for the log file, a mirrored drive for the
datafile is less than optimal. If you only had 5 drives, you could mirror
the drive for the log and create a RAID 5 for the datafiles.
If have to work with only 4 drives, additional informational needs would
include: type of database (OLTP/OLAP), Number of users, amount of traffic,
concurrency of traffic, average size of queries, amount of INSERT/UPDATE
traffic, etc.
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||It depends on a lot of things but the biggest of which is how many reads to
writes you do. If you have mostly read only operations you may get better
read results by having a 4 disk Raid 10. If you have a lot of writes you may
need to separate the log file from the data files. If you only have 4 disks
you have little choice but to use two Raid 1's.
--
Andrew J. Kelly SQL MVP
"Joachim Hofmann" <speicher@.freenet.de> wrote in message
news:4l8o3aFpta8U1@.individual.net...
> Maybe my question is too simple because I dont know much of RAID,
> but I try:
> What is probably "better": One RAID of 4 Disks, where datafile and logfile
> are
> placed on, or Two RAIDs of each 2 Disks, where one RAID contains the
> datafile
> and the other RAID contains the logfile?
> Joachim|||I would add to that, IF the RAID array controller is suitable for
write caching (battery backed memory on the controller and other wise
certified for that sort of thing), then the write penalty for RAID5 is
somewhat minimized.
Roy Harvey
Beacon Falls, CT
On Fri, 25 Aug 2006 10:25:44 -0700, "Arnie Rowland" <arnie@.1568.com>
wrote:
>As all questions related to computers, the correct response is 'It depends'.
>A mirror RAID drive would be good for the log file, a mirrored drive for the
>datafile is less than optimal. If you only had 5 drives, you could mirror
>the drive for the log and create a RAID 5 for the datafiles.
>If have to work with only 4 drives, additional informational needs would
>include: type of database (OLTP/OLAP), Number of users, amount of traffic,
>concurrency of traffic, average size of queries, amount of INSERT/UPDATE
>traffic, etc.