tempdb question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

tempdb question

Author

Topic

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-29 : 05:14:23

I want to create multiple tempdb files on our server, as we are having problems with it growing huge,
we have two dual core CPU's so after reading about it I find i can create one for each core (4 in total - yes?).

I will have to use alter database/modify file to reduce the size initially (I think), then i will have to add additional files (all the same size) up to the number required.
Has anyone got a sample script that I can use for reference to see how this is done,?
Thanks

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-04-29 : 05:47:01

quote:
Originally posted by Bill_C

I want to create multiple tempdb files on our server, as we are having problems with it growing huge,
we have two dual core CPU's so after reading about it I find i can create one for each core (4 in total - yes?).

I will have to use alter database/modify file to reduce the size initially (I think), then i will have to add additional files (all the same size) up to the number required.
Has anyone got a sample script that I can use for reference to see how this is done,?
Thanks

I think its not possible to allocate a cpu to datafile;
How ever you can have a secondary file placed on a separate hard disk.

you can use SSMS or the below given statement.


ALTER DATABASE tempdb
ADD FILE 
( 
NAME = tempdbdev2,
FILENAME = 'D:\tempdbdev2.ndf',
SIZE = 5MB,
MAXSIZE = 100MB,
FILEGROWTH = 5MB
)

Instead of adding a secondary file move the tempdb database to a separate storage location and also figure out the reason for the growing size of tempdb

Regards,
Ahmad Osama

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-29 : 06:29:21

Do I also have to add a log file (ldf) for each mdf as well?

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-04-29 : 06:39:57

quote:
Originally posted by Bill_C

Do I also have to add a log file (ldf) for each mdf as well?

there isn't any relation between data files and log files.
u can have more than 1 log files but i think that its not required.

Regards,
Ahmad Osama

DKG
Starting Member

3 Posts

Posted - 2009-04-29 : 06:43:19

Agreed with Ahmed, you should move tempdb to a differend stoage disk(dedicated) instead of adding files.

Bill_C
Constraint Violating Yak Guru

299 Posts

Posted - 2009-04-29 : 06:46:54

Thanks

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-29 : 18:39:47

Adding files according to no of processors will help in parallel processing. But your issue is related to growing huge. You need to figure out what is causing it. Always use index if you are dumping lots of records in Tempdb and filter out only records that has to go in Temp table or table variable. SQL server 2005 uses lot of tempdb. You need to have enough space for it.

Also to reduce I/O issues, Put it in RAID 10.

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-04-30 : 00:31:43

quote:
Originally posted by sodeep

Adding files according to no of processors will help in parallel processing. But your issue is related to growing huge. You need to figure out what is causing it. Always use index if you are dumping lots of records in Tempdb and filter out only records that has to go in Temp table or table variable. SQL server 2005 uses lot of tempdb. You need to have enough space for it.

Also to reduce I/O issues, Put it in RAID 10.

Is it possible to dedicate a data file to specific CPU; please elborate

Regards,
Ahmad Osama

ahmad.osama
Posting Yak Master

183 Posts

Posted - 2009-05-01 : 06:31:27

quote:
Originally posted by ahmad.osama

quote:
Originally posted by sodeep

Adding files according to no of processors will help in parallel processing. But your issue is related to growing huge. You need to figure out what is causing it. Always use index if you are dumping lots of records in Tempdb and filter out only records that has to go in Temp table or table variable. SQL server 2005 uses lot of tempdb. You need to have enough space for it.

Also to reduce I/O issues, Put it in RAID 10.

Is it possible to dedicate a data file to specific CPU; please elborate

Regards,
Ahmad Osama

hey am eager to know..pls explain

Regards,
Ahmad Osama

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources