TempDb on RAID 0 ?

[talenx]

Hello
This might be an odd question but... does anyone know if SQL server will crash or mark production database suspect if tempdb goes down?

Ok, well here is my issue I have an issue with space on a SAN box. The EDW applications use the tempdb ALOT!! Issue here is we are running out of space for tempdb( btw is on it's own disk subsystem.) anyways currently the LUN is configured as a RAID 10 yet we are losing half of to the mirror. i would love to reclaim that space by changing that LUN to a RAID 0 config... now I know your gonna say... (REDUNDACY!!, REDUNDACY!!,REDUNDACY!!
But that’s some really expensive temp space... not to mention its doesn't matter since it gets rebuilt every time SQL server service restarts.

So back to the question. Has anyone ran or heard of tempdb on a RAID 0 config and losing a drive (hence tempdb going down) what the reporcusions are?
now I understand I can move the tempdb elsewhere in the meantime but what happens to production... does it stop? Does SQL server attemped to perform everything in memory ( causing SQL to slow down dramatically until tempdb is backup?)

Just wondering if this might be an ok, yet not a best practices move.
Thanks
TalenX

 

[mrdenny]

If tempdb goes away the SQL Server will stop. SQL can't run without a tempdb. Why not change the LUN from RAID 10 to RAID 5?

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[talenx]

Hi MrDenny,
Because how frequent the tempdb is using RAID 5 posed a fairly large performance hit. Granted it does give my space back... but with a hitch.

Ok, so SQL server will not function if the tempdb becomes suspect is offline in the middle of a transaction? do you happen to know if SQL server service stops? Or if it continues to process transactions that do not require tempdb action. And when a transaction does come along that does need tempdb, will it then puke on it's self and stop the SQL service? or does SQL server continue to run but not process any processes that requires tempdb.. Secondly if the tempdb does go down will it cause any other database to come up suspect?
I have found a couple KB notes

http://support.microsoft.com/kb/q288809/

Yet that when SQL server is started with a tempdb issue… I wonder what would happen if the tempdb goes belly in production? I have read a few articles concerning tempdb residing on a RAID 0 and found the results where very successfully..Then again there is always the issue of up time; if a drive were to fail I would have a hot spare available On site to slap into that fiber tray.


One of these days I'll get my head around SQL server...

Thanks
TalenX

 

[mrdenny]

I would assume that the SQL Service would stop in it's tracks, or at the least core dump. I would assume that it wouldn't accept any new transactions at all.

I don't think that if tempdb died any other database will go suspect. They should have all of there role back info in there local log, so those transactions should be able to roll back safely.

The Q article you mentioned tells how to start the SQL Server in a minimal mode, which means that you are telling it to start without tempdb, and with out loading up a large portion of the SQL code.

Probably your best way to find out will be to setup a server with tempdb on it's own lun, then have the SAN take away the LUN.

You will also have to deal with how the OS reacts to having the LUN go away. I know that we had a server that we removed a LUN from and we didn't go and rescan the SCSI buss right away to tell Windows that the LUN was gone, and about 20 minutes later the server tried to write to the drive, and the drive was just gone. Windows freaked out and blue screened on us.

How much space do you really need for your tempdb database? an you get away with a RAID 1 array instead of a RAID 10 array?

It sounds like it's time for management to hand over some cash to purchase another couple for shelves for the SAN.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[SQLBill]

Real-life scenerio:

I had TEMPDB on a separate 73 GB drive in my SAN. The drive failed one day. SQL Server died immediately and I couldn't do anything until I got the new drive installed and reloaded.

-SQLBill

Posting advice: FAQ481-4875

 

[talenx]

MrDenny,SQLBill,
Well... I have to painfully confirm that what SQLBill stated is indeed true.
I tested this scenario at home over the weekend. I create a new instance of SQL server on a mid grade server (because I didn't have a SAN at home I had to improvise...) I had an external USB HDD that I had attached to the server and placed tempdb on that drive.
Began testing on the tempdb by running loops of a series large select distinct statements ( to insure that the tempdb would be used.) once I confirm that tempdb was being written to i pulled the USB cable from the server.... here is the funny part. SQL server continue to run.. In fact the job showed that it was still active and processing.. The OS found out that a drive was missing and i had some transactions that were queuing up. I guess it didn't like that to much. Windows crashed (stopped responding) upon windows recycle. SQL server would not start until i reconnected the USB drive and restarted the SQL service.

RAID 1 or RAID 5 it is...

Thank you once again.

Thanks
TalenX