Ideal C: for SQL Server

Question

Ideal C: for SQL Server

Anamika

SSCertifiable

Points: 6202
More actions
February 9, 2012 at 10:12 am

#391523

Hi,
I have a DB Server with 64 GB RAM. the HDD is partitioned as C: 26GB and D:108GB. I have SAN with 1TB (clustered). many times C drive is going out of space. my temp DB is getting full, and causing bad performance in queries.
what could be the ideal size C:
is there a possibility to add more HDD to C drive?
how can i avoid tempDB filling up?
thanks,
ami

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

SkyBVI SSCoach Points: 15128 More actions · Answer 1

Anamika (2/9/2012)
Hi,
I have a DB Server with 64 GB RAM. the HDD is partitioned as C: 26GB and D:108GB. I have SAN with 1TB (clustered). many times C drive is going out of space. my temp DB is getting full, and causing bad performance in queries.
what could be the ideal size C:
is there a possibility to add more HDD to C drive?
how can i avoid tempDB filling up?
thanks,
ami

There is no ideal size of C: , it all depends....

Add more HDD to C drive...i don't think so ( not sure)

Temp db filling up....you have to in itilaize your tempdb according to your requiremnts

like many open queries running, import/ export data...

Also, You can put up autogrowth at a defined %

and monitor it .

i would allocate a differnet drive for tempdb.

Regards,

Skybvi

Regards
Sushant Kumar
MCTS,MCP

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 2

+1 for allocating a different drive for TempDB. C:\ is usually where your OS, installs, and Swapfile go. If tempdb starts getting hammered and memory drives the swapfile they'll start competing for spindle time.

D:\ probably isn't better but at least it has enough space.

Fighting with TempDB when it's limited on space can make it a real hassle to do large data manipulation. You'll probably have to break whatever process you're trying to run down into smaller chunks to handle it without setting up the TempDB elsewhere.

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

LutzM SSC Guru Points: 107049 More actions · Answer 3

Depending on the layout of your SAN it might be an option to move tempdb to a LUN based on more than one spindle dedicated to tempdb only. Or maybe they even have a few SSDs being part of the SAN.

However, the first check should be targeted at the reason why tempdb is filling up. You might want to use a server side trace to capture the process hammering tempdb.

As a side note: If you're paying for MS support for SQL server you might run into a situation where you actually need the support but can't provide the data they may ask for: a full memory dump. This dump (as to my best knowledge) has to go to C: drive. With 64GB RAM and only 26GB on C: (minus space already allocated to OS and applications) I can't see a way how you'd be able to provide the requested information...

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

This script[/url] tracks tempdb usage per active session.

You could schedule it with SQL Server Agent and record the results to a table.

Find the culprit and fix it.

-- Gianluca Sartori

Anamika SSCertifiable Points: 6202 More actions · Answer 5

hi,

thanks for your inputs.

i think i messed up with some of my questions.

tempDB is not stored in C:

but it is getting filled up very soon and the space left for it to grow is very less.

my question is is there any connectivity between C: is not having enough space, and because of this the tempDB is growing high?

though i shrink the tempDB it grows again to 58 GB.

and lot of calculations are happening to make it grow. but it should automatically clear it out isn't it?

thanks,

ami

spaghettidba SSC Guru Points: 105732 More actions · Answer 6

Anamika (2/9/2012)
tempDB is not stored in C:

Fine. Good to hear that.

my question is is there any connectivity between C: is not having enough space, and because of this the tempDB is growing high?

No, AFAIK.

though i shrink the tempDB it grows again to 58 GB.

This means it needs to be this size at least.

and lot of calculations are happening to make it grow. but it should automatically clear it out isn't it?

Nope. Internal objects are not deallocated automatically. You have to run DBCC FREESYSTEMCACHE('ALL') to deallocate them. Be warned that running that command also flushes the plan cache.

I suggest that you try to find the offending query (the one that allocates lots of space in tempdb) and fix it, or it will never be any smaller than that.

-- Gianluca Sartori

Anamika SSCertifiable Points: 6202 More actions · Answer 7

good points Sartori,

thanks,

in this case will i be able to find the cause by opentran? how is there any other to way to find out why the tempDB is growing?

but we need enough space in C: always am I right?

What is the reason for it?

thanks,

ami

spaghettidba SSC Guru Points: 105732 More actions · Answer 8

Anamika (2/10/2012)
in this case will i be able to find the cause by opentran? how is there any other to way to find out why the tempDB is growing?

Schedule the query that I suggested previously and capture the output to a table.

but we need enough space in C: always am I right?
What is the reason for it?

The OS needs disk space for its operations and for the virtual memory.

-- Gianluca Sartori