How to setup Multiple tempdbs?

Hi,
We have got a huge SQL server 2000 box and we want to setup Multiple
tempdbs for a multiprocessor machine, but do not know how.
Anyone that can help please?
Thanks
"Jnadile" <Jnadile@.discussions.microsoft.com> wrote in message
news:9E5C5454-8C17-4CC5-B00B-4FD7FCD08A35@.microsoft.com...
> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
>
You can not set up multiple tempdbs.
You can however add FILES to the existing one.
Check out the alter database command.

> Anyone that can help please?
> Thanks
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||Hi
"Jnadile" wrote:

> Hi,
> We have got a huge SQL server 2000 box and we want to setup Multiple
> tempdbs for a multiprocessor machine, but do not know how.
> Anyone that can help please?
> Thanks
You can only have one tempdb per instance, but it is beneficial to have
multiple files for the tempdb that match the number of (logical) processes,
as this will help to reduce contention. For SQL 2000 you will also need to
set the trace flag 1118 and implement hotfix 702 see
http://www.sqlsecurity.com/Tools/FreeTools/tabid/65/Default.aspx
To increase the number of files use the ALTER DATABASE command
http://msdn2.microsoft.com/en-us/library/aa275464(SQL.80).aspx
HTH
John
|||"John Bell" wrote:

> Hi
> "Jnadile" wrote:
>
> You can only have one tempdb per instance, but it is beneficial to have
> multiple files for the tempdb that match the number of (logical) processes,
Typo processes should be processors ie. CPUs!
It is also advised that if you have detected blocking on tempdb, then to
increase the number of files gradually up to the recommended number and stop
at the minumum number where it does not block.
John