Moving and increasing the size of tempdb

Last Post 26 Sep 2007 05:00 AM by oshaw. 4 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
oshaw
New Member
New Member

--
25 Sep 2007 06:56 AM

Because of performance issues, analysis and expert advice, I will move tempdb to it's own array. I will issue the alter command:

USE tempdb
GO
EXEC sp_helpfile -- To get the tempdb logical and physical file names
USE master
GO
--
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'LETTER:SQLData\tempdb.mdf',
SIZE = 4000MB,
MAXSIZE = 5000MB,
FILEGROWTH = 25%)
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = ' LETTER:SQLData\templog.ldf')
GO

I am doing this to change the default size of tempdb thinking that when I restart the server these new settings will be in effect. Please tell me if this will suffice to change the default settings. If no, what else needs to be done.

Thank You
oshaw
New Member
New Member

--
26 Sep 2007 05:00 AM
Thank you both very much! I will heed you advice by changing the percentage to an actual amount and split the one data file into 4 1GB files. I will also study the suggested document - Again - Thank You!
oshaw
New Member
New Member

--
27 Sep 2007 05:52 AM
FYI - I made the change last night and after three hours of our normal heavy morning traffic, there have been NO "connection" problems. (quite unusual). I went from a tempdb of less than 10% the size of the largest database to a tempdb of 40% the size of the largest database. Also, I moved tempdb off from the same array as the databases. Tempdb was split from one to four files.

OLU and GunneyK - You have been very helpful. Thank You very much!!
TRACEYSQL
New Member
New Member

--
24 Feb 2008 07:06 AM
When you create the four tempdb files of 1 gig each.

Is it recommended to have a max size set? i was always under impression not to set a max size on tempdb.

Thanks
TRACEYSQL
New Member
New Member

--
25 Feb 2008 12:00 PM
Thanks we did fill up tempdb 100 gig one time then brought us down to a crash.
So i will take this advice and change my tempdb to the 4 and put in a high max space 50 gig ...bit eccessive but better than a crash.
You are not authorized to post a reply.

Acceptable Use Policy