January 21, 2010 at 8:16 am
Good morning, everybody!
sql 2005 sp3
1. i'm planning to move a tempdb to a dedicated drive
2. split 1000 mb data file into 4x256 mb files
3. leave 1000 mb log file unchanged
4. 4 datafiles will be placed on T: drive and 1 logfile will be placed on L: drive
I understand that I need to change location by running:
use master
go
Alter database tempdb modify file (name = tempdev, filename = 'T:\Sqldata\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'L:\Sqldata\templog.ldf')
go
I have a few questions:
1. How do I specify a new tempdb datafile size?
Like I said, it's 1000 mb now and I need it to be 256 mb
is this the right script:
Alter database tempdb modify file (name = tempdev, size = 56 , filename = 'T:\Sqldata\tempdb.mdf')
2. Do I add 3 more files after file location has been specified and sql restarted or before???
January 21, 2010 at 8:35 am
I have a few questions:
1. How do I specify a new tempdb datafile size?
You can make use of size parameter in the create\alter database command .
For example
create database <dbname> add file
on primary (name ='logical filename',filename ='location',size = 100MB)
for modifying file
alter database <dbname> modify file(name ='logical filename', size = Requiredsize)
Like I said, it's 1000 mb now and I need it to be 256 mb
is this the right script:
Alter database tempdb modify file (name = tempdev, size = 56 , filename = 'T:\Sqldata\tempdb.mdf')
Alter database tempdb modify file (name = tempdev, size = 56 , filename = 'T:\Sqldata\tempdb.mdf',size = 256MB,filegrowth =0)
You can make use of other parameters for growth of file i.e filegrowth,maxsize
IF filegrowth is 0 you are disabling autogrowth.
2. Do I add 3 more files after file location has been specified and sql restarted or before???
For adding new tempdb files sql server restart is not required.
January 21, 2010 at 8:51 am
Thank you....
So basicaly when I'm specifying the new location for a tempdb datafile
I can specify a new size for that file at the same time in the same script and then restart sql.
am i right?
January 21, 2010 at 8:56 am
SD1999 (1/21/2010)
Thank you....So basicaly when I'm specifying the new location for a tempdb datafile
I can specify a new size for that file at the same time in the same script and then restart sql.
am i right?
Yes you can specify new location and size at same time.
If allocated space is more than the file size that you are specifying then it will thrown you an error.
In that scenario you have to shrink the file to the desired size and cap it, so that it will not further grow.
January 21, 2010 at 9:09 am
but this is my point.....
current allocated size is 1000 mb
and i want to split it to 4 256 mb
i will create 3 more data files later on with 256 mb each
but how do i decrease current file from 1000 to 256 while specifying a new location?
pls be patient with me :), i think i'm missing something here
January 21, 2010 at 9:12 am
SD1999 (1/21/2010)
current allocated size is 1000 mb
and i want to split it to 4 256 mb
i will create 3 more data files later on with 256 mb each
but how do i decrease current file from 1000 to 256 while specifying a new location?
pls be patient with me :), i think i'm missing something here
that's ok
Please shrink the file i.e you can make use of this command
use tempdb; go; dbcc shrinkfile(1,256)
Once this is done, please verify the size of the file by running sp_helpdb tempdb
Once it is 256 MB cap the file so that it will not grow further.
You can create other 3 files by specifying size.
January 21, 2010 at 9:21 am
it didn't shrink...
probably because long ago when I installed sql I specified 'initial size' tempdb data file 1000 mb
January 21, 2010 at 11:19 am
i found solution.....
http://support.microsoft.com/kb/307487
the only way to change 'initial size' for tempdb is
to shut down sql server
connect via cmd using sqlservr -c -f
Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file
restart sql
January 21, 2010 at 11:34 am
SD1999 (1/21/2010)
i found solution.....http://support.microsoft.com/kb/307487
the only way to change 'initial size' for tempdb is
to shut down sql server
connect via cmd using sqlservr -c -f
Connect to SQL Server with Query Analyzer, and then run the following Transact-SQL commands:
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'tempdev', SIZE = target_size_in_MB)
--Desired target size for the data file
ALTER DATABASE tempdb MODIFY FILE
(NAME = 'templog', SIZE = target_size_in_MB)
--Desired target size for the log file
restart sql
thanks for letting me know
Also I don't think so it will allocate intially 1000MB, check the space used once there is ample free space in, then shrink the file as desired.
No idea how much initally will it allocate, still searching on this.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply