Skip to main content
deleted 26 characters in body
Source Link
Aaron Bertrand
  • 181.5k
  • 28
  • 402
  • 619

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx:

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at:

Source Link
Hannah Vernon
  • 70.5k
  • 22
  • 177
  • 321

To be certain you are configuring tempdb file sizes as intended, you should use T-SQL instead of the SSMS user interface.

To resize files, you can use something like this:

ALTER DATABASE tempdb
MODIFY FILE (NAME=tempdev, SIZE=1GB, GROWTH=1GB);

You'd need to execute this once for each logical file in tempdb. NAME=tempdev_2, NAME=tempdev_3 etc.

I'd suggest using a T-SQL query to obtain information about the file sizes, as in:

SELECT DatabaseName = d.name
    , DBFileName = mf.name
    , DBInitialSize = mf.size
    , Growth = mf.growth
    , GrowthIsPercentage = mf.is_percent_growth
    , FileType = mf.type_desc
FROM master.sys.master_files mf
    INNER JOIN master.sys.databases d ON mf.database_id = d.database_id
WHERE d.name = 'tempdb'
ORDER BY d.name, mf.name;

As mentioned in the comments, you should certainly look into the possibility of using Trace Flag 1117 and 1118, if you are concerned about tempdb performance; ostensibly this is the root-cause of your questions about tempdb.

Aaron Bertrand has a great article about using the SSMS Interface vs T-SQL at http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/14/bad-habits-to-kick-using-the-visual-designers.aspx