adding datafiles to tempdb (dynamic script)

  • 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

  • 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
  • Pls Check this Script

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

    Thank You,

    Best Regards,

    SQLBuddy

  • 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

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply