April 6, 2012 at 4:09 pm
Hi everyone,
Given the setup below, what would happen if the tempdb on the C:\ drive fills up? Will SQL Server crash or does it continue processing by using the tempdb file on D:\?
* Main Tempdb file on C:\ with max size of 20GB.
* Second Tempdb file on D:\ with unlimited max size.
Thanks in advance for your help.
April 6, 2012 at 4:12 pm
That's not a very good way to handle you tempdb files, but in this example sql server will still run, as long as there's someplace to expand the tempdb. Assuming you're not filling up the C drive entirely, just the tempdb on the C drive.
Where is the templog.ldf located?
April 6, 2012 at 4:14 pm
benjamin.reyes (4/6/2012)
That's not a very good way to handle you tempdb files, but in this example sql server will still run, as long as there's someplace to expand the tempdb.Where is the tempdb.ldf located?
kinda what i was thinking about spliting the tempdb files across drives. i dont like splitting db files accross drives (now MDF and LDF files on seperate drives is a good practice as it improves the io performance of the database by not fighting for io to the same disk for reads and writes.)
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 7, 2012 at 4:36 am
shahgols (4/6/2012)
Hi everyone,Given the setup below, what would happen if the tempdb on the C:\ drive fills up? Will SQL Server crash or does it continue processing by using the tempdb file on D:\?
* Main Tempdb file on C:\ with max size of 20GB.
* Second Tempdb file on D:\ with unlimited max size.
Thanks in advance for your help.
It wont crash but it will inform you that pages cannot be allocated for the operation and will likely rollback the op.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 9, 2012 at 11:24 pm
Thanks everyone for your help.
In my new position, there is a Win 2003 server with a C:\ drive partition of 50GB. The tempdb files are on that drive. The other drives are the data drive, the index drive, and the log drive. I am kind of hesitant to move the tempdb entirely to any of those drives, but I need to make sure that the tempdb has enough space to grow, since it filled up the C:\ drive recently. What do you suggest I do?
April 10, 2012 at 12:51 am
Are the remaining drives separate or are they merely logical drives on the same physical disk(s) as the C drive?
What is the current total size and free space of the remaining drives?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 17, 2012 at 3:00 pm
Hi Perry,
Yes, the other drives are separate physical drives, the data on their own set of drives, the index on their own, and the logs on their own.
There is plenty of space on all 3, I can create a new tempdb file on either one. But I suppose it wouldn't be wise to create it on the log drive since logs are written sequentially and I don't want to hurt the performance of that operation by adding tempdb there. Index is a good option, since it does mostly reads and doesn't have to work with as much data as the data drive? I'm not sure.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply