September 21, 2014 at 12:18 pm
I've got a TempDB database approximately 40 GB (SQL 2008 EE). I've been instructed to add some data files to it. That process seems pretty straight forward - see script:
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf',
SIZE = 10240MB
);
-- add data files 2 - 4, 10 GB each.
ADD FILE
(
NAME = tempdev2,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
),
(
NAME = tempdev3,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb3.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
),
(
NAME = tempdev4,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb4.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
);
GO
A couple of questions come to mind modifying a database.
1. The original tempdb.mdf was 40 GB, with this script I would resize it down to 10GB and added 3 data files. What's the impact? Putting it another way - I just modified a database, placing content over 4 files. What has SQL done to make sure integrity is ok, content has not been corrupted.
2. After running the script, how do you evaluate tempdb is functioning properly?
Moving content around regardless of circumstance is not something to be wild about. Thanks
September 21, 2014 at 1:19 pm
bobba (9/21/2014)
I've got a TempDB database approximately 40 GB (SQL 2008 EE). I've been instructed to add some data files to it. That process seems pretty straight forward - see script:USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE
(
NAME = tempdev,
FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb.mdf',
SIZE = 10240MB
);
-- add data files 2 - 4, 10 GB each.
ADD FILE
(
NAME = tempdev2,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb2.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
),
(
NAME = tempdev3,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb3.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
),
(
NAME = tempdev4,
FILENAME = 'C:\Program Files\Microsoft SQLServer\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\tempdb4.ndf',
SIZE = 10240MB,
MAXSIZE = UNLIMITED,
FILEGROWTH = 10%
);
GO
A couple of questions come to mind modifying a database.
1. The original tempdb.mdf was 40 GB, with this script I would resize it down to 10GB and added 3 data files. What's the impact? Putting it another way - I just modified a database, placing content over 4 files. What has SQL done to make sure integrity is ok, content has not been corrupted.
2. After running the script, how do you evaluate tempdb is functioning properly?
Moving content around regardless of circumstance is not something to be wild about. Thanks
Quick thought, all the tempdb data files should be of the same size, otherwise this might be close to a pointless exercise unless there is no congestion and the only thing you are after is increasing the size which makes adding files kind of pointless. Look into using traceflag 1117 to force equal growth if auto-growth is enabled.
😎
September 22, 2014 at 6:30 am
That's good information - and I am going to look into it..
Can you answer my questions regarding the following:
1. The original tempdb.mdf was 40 GB, with this script I would resize it down to 10GB and added 3 data files. What's the impact? Putting it another way - I just modified a database, placing content over 4 files. What has SQL done to make sure integrity is ok, content has not been corrupted.
2. After running the script, how do you evaluate tempdb is functioning properly?
Anything on this?
September 22, 2014 at 6:35 am
jralston88 (9/22/2014)
What's the impact?
None.
Putting it another way - I just modified a database, placing content over 4 files.
No you didn't. You changed one file to have an initial size of 10GB (which will take affect on the next restart) and then added 3 empty files. That's all you've done, you haven't shrunk any files, you haven't placed any content in the other 3 files.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 29, 2014 at 9:21 am
How big is your TempDB LUN? Is it separate from your other databases/files? If it is (and it should be), why not just fill it out with these TempDB files and turn autogrowth off? Also, make sure instant file initialization is turned on, or else you'll be sitting around waiting for a long time while this runs. Picture 40gb worth of zeroes.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply