473,734 Members | 2,789 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Logging to second disk -- what happens when it crashes?

Currently we have SQL Server running on a single RAID5 array.
The data and the logs are all written to this array. We don't
have huge volume of activity, but it is growing. Most activity
is from our website that interacts a lot with the database.

We have read that it is a good idea to separate the data files
from the log files, having each on separate disks (or in the
case of RAID, separate arrays). So, we are considering adding
a separate SCSI hard drive, or maybe a RAID 1 system, and have
sql server log to that instead.

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?

Also, what kind of throughput would we need to be doing to see
any kind of benefit to making this change? It is easier to
justify the expense of another disk or RAID setup if it is
actually going to matter. ;-)

Thanks all!

Thomas
Jul 20 '05 #1
3 1713

"Thomas" <th***********@ data.iatn.net> wrote in message
news:a9******** *************** ***@posting.goo gle.com...
Currently we have SQL Server running on a single RAID5 array.
The data and the logs are all written to this array. We don't
have huge volume of activity, but it is growing. Most activity
is from our website that interacts a lot with the database.

We have read that it is a good idea to separate the data files
from the log files, having each on separate disks (or in the
case of RAID, separate arrays). So, we are considering adding
a separate SCSI hard drive, or maybe a RAID 1 system, and have
sql server log to that instead.
RAID 1 at the least. Don't use a single drive in this case.

My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?
It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and
it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.


Also, what kind of throughput would we need to be doing to see
any kind of benefit to making this change? It is easier to
justify the expense of another disk or RAID setup if it is
actually going to matter. ;-)

Thanks all!

Thomas

Jul 20 '05 #2
"Greg D. Moore \(Strider\)" <mo************ ****@greenms.co m> wrote in message news:<kS******* ************@tw ister.nyroc.rr. com>...
"Thomas" <th***********@ data.iatn.net> wrote in message
news:a9******** *************** ***@posting.goo gle.com...
My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?


It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and
it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.


Let's imagine that this new RAID 1 for the logging goes down hard,
controller malfunction or what have you, and it will take a few days
to be repaired. In the meantime our data drive is working fine, and
we want to move the logging back to that array. You mentioned that
SQL Server would "stop running." If it isn't running, how would we
be able to update its configuration to change the location of the
logfiles?

Thanks for your feedback and advice!

Regards,
Thomas
Jul 20 '05 #3

"Thomas" <th***********@ data.iatn.net> wrote in message
news:4f******** *************** ***@posting.goo gle.com...
"Greg D. Moore \(Strider\)" <mo************ ****@greenms.co m> wrote in message news:<kS******* ************@tw ister.nyroc.rr. com>...
"Thomas" <th***********@ data.iatn.net> wrote in message
news:a9******** *************** ***@posting.goo gle.com...
My question is, what happens if that secondary drive or array
completely fails. Let's say it melts. Assuming the OS doesn't
crash (Windows 2003 Server), what will SQL Server 2000 do when
the drive stops working and it is trying to write its data
logging to it? Will it continue to function and write alert
events? Will it shutdown? If it shuts down, how do we alter its
configuration to tell it to use another drive for the logs?


It will stop running. It can't write anything. Just the same as if you
lost your data drive.

At that point you pull out your disaster recovery plan and go from here.

We've had a few cases where either the log or data disks went off-line and it was just a matter of bringing them back online through the RAID
controller interface. SQL 2000 picked up where it left off.


Let's imagine that this new RAID 1 for the logging goes down hard,
controller malfunction or what have you, and it will take a few days
to be repaired. In the meantime our data drive is working fine, and
we want to move the logging back to that array. You mentioned that
SQL Server would "stop running." If it isn't running, how would we
be able to update its configuration to change the location of the
logfiles?


Well, first of all, if you lose your log device, you have to do a restore
from backup in any case. At that point simply restore the backup to a
different device using the MOVE option.

Now, if your master DB and other system DBs are on the failed device, you
need to startup SQL server manually and specify on the command line where
they are.

MS has multiple KB articles on this.

Turn the question around... what happens if the RAID device with your DATA
fails? (ironically in some ways this can be EASIER to recover from assuming
you have good backups.)


Thanks for your feedback and advice!

Regards,
Thomas

Jul 20 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
1574
by: Frantisek Fuka | last post by:
I am using the standard "logging" module included with Python and it seems it doesn't correctly identify the filename (and thus module name) from where the logging method was called. In fact, no matter from which module I call the logging method, the resulting log says that the filename of the caller is: \tmp\python.2664\usr\lib\python2.3\logging\__init__.py (this seems to me like a reference to the logging module itself combined with...
6
5444
by: idesilva | last post by:
Hi, I have an application which sends/receives messages at a very high rate. This app needs to 'log' the contents of these messages. Since the msg rate is high, 'logging' each and every msg to a disk-file reduces the app's performace significantly. I want to device a 'logging' mechanism where the 'log' is actually buffered and it will be written to the disk periodically. The time of writing to disk should be controlled by the app rather...
6
10314
by: Burkhard Schultheis | last post by:
As I wrote last week, we have a problem with a DB2 V8 on Linux. Here is what is in db2diag.log during online backup: Starting a full database backup. 2004-04-01-02.33.54.760164 Instance:lzgneu Node:000 PID:1293(db2loggw (TELEMATX)) TID:1024 Appid:none data protection sqlpgwlp Probe:909 TailPage 0 does not match pagelsn 0023CEBF0FFB and firstlsn 0023CEBF8000
1
3450
by: DB2 Convert | last post by:
Hi, A few points to confirm as I am still new with DB2. 1. What will happen if archive logging is used, however there is no userexit or manual process to move those online archived logs to another directory. At this moment my LOGPRIMARY is set to 3. Will the logging hang? 2. Does LOGSECOND apply to circular logging only? If if use archive
2
1740
by: johnm | last post by:
As a refresher, a description of my problem is in the message body below. I could not get answers to all of the questions that were asked below in a previous thread, but here is what I found out: 1. Number of LOB's inserted per hour and average size 2. Number of LOB's updated per hour and average size 3. Number of LOB's deleted per hour and average size 4. Rough estimate of the amount of non-LOB data inserted, updated, and deleted per...
5
2072
by: Michelle Stone | last post by:
Hi everybody I am writing a simple asp.net application using form authentication. I store the list of all users and their passwords in an SQL Server database table. My client recently told me that he wants me to do something through which only one user can login using any given account name. I mean to say, for example, when a user called "abc" is online, another person shouldn't be
3
2067
by: Udi | last post by:
Hi All, I'm developping an application that has a very strong logging requirement - we should be able to log up to 5000 messages per second, most of them are reletively small -(16 - 30 Words) while few of them might get to 1Kb in size. The data is binary (numbers) that should be converted to text of course. I'm affraid of a "logging-bottleneck" in my app.
4
3427
by: lfhenry | last post by:
I am a newbie to HADR and Admin of DB2 (I am websphere guy). My question relates to DB2 logging. I've read that HADR does not allow Infinite logging (-1). I am expecting my Database to grow 8-10GB's a month without compression (about 260 Megs day). What logging options should i specify before i start my database. If i set archiving using LOGARCHMETH1 to a separate disk, what should the logprimary and logsecondary values be. I would like...
17
2751
by: Cramer | last post by:
I plan to implement an exception logging feature in an ASP.NET Web application that writes encountered exceptions to disk. The exception data will be stored as XML. I am planning on having each exception written to its own XML file. I plan to NOT append exceptions to a single XML file because I don't want for the exception logging component to have to deal with contention for access to the file. This is possible when, for example,...
0
8946
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8776
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9449
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9310
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9182
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8186
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
6031
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4809
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
2
2724
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.