Post reply

Temp DB DQL server isnt seeing all data files

  • April 2, 2013 at 10:35 am

    #292028

    Our Temp DB isn't seeing all all the data files we have a total of 8. SQL server is only seeing 2 after restart last night.

    Any help would be appreciated.

    Thanks

  • April 2, 2013 at 12:03 pm

    #1602919

    Update we have removed all the extra files but sys.master_files still shows 8

    Is this only for master o show whats current is to recycle SQL server?

    We need to add them back but they say in use

  • April 5, 2013 at 3:42 am

    #1603840

    did u restrt the sql server after deletion?

  • April 8, 2013 at 9:08 am

    #1604458

    D-SQL (4/2/2013)

    Our Temp DB isn't seeing all all the data files we have a total of 8. SQL server is only seeing 2 after restart last night.

    Any help would be appreciated.

    Thanks

    I guess you are using a code similar like this, right?

    ALTER DATABASE tempdb

    MODIFY FILE (name=tempdev,size=512MB) ;

    GO

    ALTER DATABASE tempdb

    ADD FILE (name=tempdev2,size=512MB,filename='D:\data\tempdev2.ndf') ;

    GO

    ALTER DATABASE tempdb

    ADD FILE (name=tempdev3,size=512MB,filename='D:\data\tempdev3.ndf') ;

    GO

    ALTER DATABASE tempdb

    ADD FILE (name=tempdev4,size=512MB,filename='D:\data\tempdev4.ndf') ;

    For adding files, you should not have to reboot MS-SQL. So be sure you are doing something similar to the code above.

    For reducing number of tempdb files, I believe you have to reboot.

    My personal preference, even if not needed, is always reboot when working with tempdb (adding or removing temdpb files)

    Now, more important is "why" you are adding more tmdpb files, do you really need those? I strongly suggest you to read these two links:

    Concurrency enhancements for the tempdb database

    Tempdb should always have one data file per processor core[/url]

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

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