Database TEMPDB with multiple data files.

  • I created the TEMPDB01 and TEMPDB02, using this script;

    (ALTER DATABASE tempdb

    ADD FILE

    (NAME = N'tempdb01',

    FILENAME = N'D:\SqlServer_TempDB\tempdb01.ndf',

    SIZE = 300MB,

    MAXSIZE = 10000MB,

    FILEGROWTH = 100MB) ,

    --ALTER DATABASE tempdb

    --ADD FILE

    (NAME = N'tempdb02',

    FILENAME = N'G:\SqlServer_TempDB\tempdb02.ndf',

    SIZE = 300MB,

    MAXSIZE = 10000MB,

    FILEGROWTH = 100MB)

    GO

    When I see in Properties Database - tempdb, that´s ok, the logical name (D:\tempdb01 and G:\tempdb02), but after stop and start the services MSSQLSERVER, show me this error in SQL SERVER Logs ( CREATE FILE encountered operating system error 21(error not found) while attempting to open or create the physical file 'E:\TempDB_SqlServer\tempdb1.ndf'.) This is very strange, because I´ m not using E:\TempDB_SQLServer\tempdb1.ndf.

  • Jose Ianuck (8/13/2013)


    I created the TEMPDB01 and TEMPDB02, using this script;

    (ALTER DATABASE tempdb

    ADD FILE

    (NAME = N'tempdb01',

    FILENAME = N'D:\SqlServer_TempDB\tempdb01.ndf',

    SIZE = 300MB,

    MAXSIZE = 10000MB,

    FILEGROWTH = 100MB) ,

    --ALTER DATABASE tempdb

    --ADD FILE

    (NAME = N'tempdb02',

    FILENAME = N'G:\SqlServer_TempDB\tempdb02.ndf',

    SIZE = 300MB,

    MAXSIZE = 10000MB,

    FILEGROWTH = 100MB)

    GO

    When I see in Properties Database - tempdb, that´s ok, the logical name (D:\tempdb01 and G:\tempdb02), but after stop and start the services MSSQLSERVER, show me this error in SQL SERVER Logs ( CREATE FILE encountered operating system error 21(error not found) while attempting to open or create the physical file 'E:\TempDB_SqlServer\tempdb1.ndf'.) This is very strange, because I´ m not using E:\TempDB_SQLServer\tempdb1.ndf.

    Run the following to check the tempdb definition.

    sp_helpdb @dbname='tempdb'

    I'm assuming the instance is running.

    If not you can get yourself up and running by following these steps:

    http://blogs.xtivia.com/home/-/blogs/how-to-start-sql-server-if-you-lost-tempdb-data-files-

Viewing 2 posts - 1 through 1 (of 1 total)

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