TempDB

Question

TempDB

Viewing 15 posts - 16 through 30 (of 30 total)

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

Paul White SSC Guru Points: 150467 More actions · Answer 1

v_kozachuk (9/29/2011)
Thanks for the question.
But ,i think, the question must be more precise - "How many data filegroups are allowed for tempDB data"
The article on technet (http://technet.microsoft.com/en-us/library/cc966545.aspx) explains us
tempdb supports only one data filegroup and one log filegroup.

There's no such thing as a log file group. From Files and Filegroups Architecture in Books Online:

Log files are never part of a filegroup. Log space is managed separately from data space.

Still not convinced? Write a CREATE DATABASE statement that places log files in a file group 😛

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Britt Cluff SSCertifiable Points: 5083 More actions · Answer 2

Good question. You can have multiple files, but only 1 filegroup. Thanks.

http://brittcluff.blogspot.com/

v_kozachuk SSCrazy Points: 2070 More actions · Answer 3

SQL Kiwi (9/29/2011)
v_kozachuk (9/29/2011)
Thanks for the question.
But ,i think, the question must be more precise - "How many data filegroups are allowed for tempDB data"
The article on technet (http://technet.microsoft.com/en-us/library/cc966545.aspx) explains us
tempdb supports only one data filegroup and one log filegroup.
There's no such thing as a log file group. From Files and Filegroups Architecture in Books Online:
Log files are never part of a filegroup. Log space is managed separately from data space.
Still not convinced? Write a CREATE DATABASE statement that places log files in a file group 😛

This is not new information for me. But i thought what the question with a trick 🙂

And then i've found the article with statement - "tempdb supports only one data filegroup and one log filegroup"

Best Regards,

Vadym Kozachuk

SanDroid SSChampion Points: 10068 More actions · Answer 4

v_kozachuk (9/29/2011)
SQL Kiwi (9/29/2011)
v_kozachuk (9/29/2011)
Thanks for the question.
But ,i think, the question must be more precise - "How many data filegroups are allowed for tempDB data"
The article on technet (http://technet.microsoft.com/en-us/library/cc966545.aspx) explains us
tempdb supports only one data filegroup and one log filegroup.
There's no such thing as a log file group. From Files and Filegroups Architecture in Books Online:
Log files are never part of a filegroup. Log space is managed separately from data space.
Still not convinced? Write a CREATE DATABASE statement that places log files in a file group 😛
This is not new information for me. But i thought what the question with a trick 🙂
And then i've found the article with statement - "tempdb supports only one data filegroup and one log filegroup"
Best Regards,
Vadym Kozachuk

You have to be carefull with the wording of a KB article. They are not edited by a technical writing professional. They usualy come from information submited by support technicians. The statement is partialy true since all log files share the log file group in a sense. But there is no create database statement that can put a log file in a differant group.

SanDroid SSChampion Points: 10068 More actions · Answer 5

Great question. More like this would be nice. 😎

I learned about this one before when someone asked me to help upgrade a SQL server from 6.5 to 7.0. Seems that the upgrade UI gave you an option to do this for the tempdb and model databases.

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

:hehe:

OzYbOi d(-_-)b SSCarpal Tunnel Points: 4054 More actions · Answer 6

good question - tks for the feedback on the log file[group] too.

No to belabor this point but if you go into the Database properties and look at the Files section, it is quite apparent that there is no Log Filegroup. Under "Filegroup" for your log file it lists, "Not Applicable" showing that there is no file group association.

Cheers

Paul White SSC Guru Points: 150467 More actions · Answer 7

OzYbOi d(-_-)b (9/29/2011)
No to belabor this point but if you go into the Database properties and look at the Files section, it is quite apparent that there is no Log Filegroup. Under "Filegroup" for your log file it lists, "Not Applicable" showing that there is no file group association.

Thanks - you saved me the job of posting a screenshot of that 🙂

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Cliff Jones SSChampion Points: 10547 More actions · Answer 8

I agree, this is a good question. Might even use this for my next interview.

happiestgirl SSC-Addicted Points: 440 More actions · Answer 9

SQL Kiwi (9/29/2011)
v_kozachuk (9/29/2011)
Thanks for the question.
But ,i think, the question must be more precise - "How many data filegroups are allowed for tempDB data"
The article on technet (http://technet.microsoft.com/en-us/library/cc966545.aspx) explains us
tempdb supports only one data filegroup and one log filegroup.
There's no such thing as a log file group. From Files and Filegroups Architecture in Books Online:
Log files are never part of a filegroup. Log space is managed separately from data space.
Still not convinced? Write a CREATE DATABASE statement that places log files in a file group 😛

convinced or not, this MS url states differently:

http://technet.microsoft.com/en-us/library/cc966545.aspx

Only one file group in tempdb is allowed for data and one file group for logs.

Paul White SSC Guru Points: 150467 More actions · Answer 10

happiestgirl (9/29/2011)
convinced or not, this MS url states differently:
http://technet.microsoft.com/en-us/library/cc966545.aspx
Only one file group in tempdb is allowed for data and one file group for logs.

It's just as wrong as it was earlier. Read back a few posts.

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi

Trey Staker SSCarpal Tunnel Points: 4796 More actions · Answer 11

Thanks for the question. Straight forward with something to teach. Easy if you know it, informational if you don't. Please submit more questions like this.

---------------------------------------------------------------------
Use Full Links:
KB Article from Microsoft on how to ask a question on a Forum

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Revenant

SSC-Forever

Points: 42467

September 29, 2011 at 12:40 pm

#1388581

Really neat question -- thanks!

Ken SSCoach Points: 17191 More actions · Answer 13

Ken

SSCoach

Points: 17191

September 29, 2011 at 1:06 pm

#1388599

Nice question about the basics. Thanks!

TomThomson SSC Guru Points: 104773 More actions · Answer 14

Good question.

It suggests another question: why on earth would anyone ever want more than one filegroup for tempdb?

Tom

Iulian -207023 SSCertifiable Points: 7639 More actions · Answer 15

Nice question; I should have read the "data" word after tempDB 🙂

this way I would not have selected 2 :Whistling:

Thank you,

Iulian