adding datafiles to tempdb (dynamic script)

• appalaraju_k-596051

  Old Hand

  Points: 383

  More actions

  January 11, 2011 at 4:34 am

  #232846

  Presently,I am working on ten production servers

  each server has four processors inorder to increase tempdb utilization i have created datafiles to tempdb using below command

  ALTER DATABASE tempdb

  ADD FILE (NAME = tempdev2, FILename='T:\LNSQLTSGT11\data\tempDB', SIZE = 256);

  ADD FILE (NAME = tempdev3, FILename='T:\LNSQLTSGT11\data\tempDB', SIZE = 256);

  using this script i need to change the location if i run on any other server so Is there any way to create make this query dynamic(for file name)

  your help will be appriciable

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  January 11, 2011 at 9:12 am

  #1271933

  This might help get you started...

  USE TempDb

  GO

  DECLARE @SQL NVARCHAR(MAX)

  , @name NVARCHAR(255)

  CREATE TABLE #temp (

  name NVARCHAR(255)

  , fileid SMALLINT

  , filename NCHAR(260)

  , filegroup NVARCHAR(255)

  , size NVARCHAR(15)

  , maxsize NVARCHAR(15)

  , growth NVARCHAR(15)

  , usage VARCHAR(9)

  )

  INSERT INTO #temp

  EXEC sp_helpfile

  SELECT @name = name FROM #temp WHERE fileid = 1

  SET @SQL = 'ALTER DATABASE tempdb'

  SET @SQL = @SQL + ' ADD FILE (NAME= ' + @name + '2, FILENAME=''T:\LNSQLTSGT11\data\tempDB'', SIZE= 256);'

  SET @SQL = @SQL + ' ADD FILE (NAME= ' + @name + '3, FILENAME=''T:\LNSQLTSGT11\data\tempDB'', SIZE= 256);'

  SELECT @SQL

  --EXEC (@SQL)

  _____________________________________________________________________
  - Nate

  @nate_hughes

• SQLBuddy123

  One Orange Chip

  Points: 28294

  More actions

  January 11, 2011 at 11:32 am

  #1272009

  Pls Check this Script

  http://adventuresinsql.com/2010/04/script-to-create-a-tempdb-file-per-processor/

  Thank You,

  Best Regards,

  SQLBuddy

• ksatishraju-658133

  Valued Member

  Points: 67

  More actions

  January 11, 2011 at 4:36 pm

  #1272136

  Hi,

  There is a small change in the script:

  SET @SQL = 'ALTER DATABASE tempdb'

  SET @SQL = @SQL + ' ADD FILE (NAME= ' + @name + '2, FILENAME=''T:\LNSQLTSGT11\data\tempDB'', SIZE= 256),'

  SET @SQL = @SQL + ' (NAME= ' + @name + '3, FILENAME=''T:\LNSQLTSGT11\data\tempDB'', SIZE= 256)'

  Regards,

  Satish