Post reply

Tempdb file removal is incomplete

  • September 13, 2019 at 4:04 pm

    #3679867

    Hello experts,

    I tried to run a command like the following for a few tempdb ndf files that I want to remove:

    ALTER DATABASE tempdb

    REMOVE FILE tempdev2;

    GO

    However, even though the ndf files no longer show in the Database Properties GUI, they are still in the file system. And I cannot delete them because they are in use.

    Also, if I use a t-sql query, I see them listed as files for tempdb with this query:

    SELECT name FROM sys.database_files

    But not with this query (which I think makes sense if this next query lists only mdf and ldf):

    SELECT

    db.name AS DBName,

    type_desc AS FileType,

    Physical_Name AS Location

    FROM

    sys.master_files mf

    INNER JOIN

    sys.databases db ON db.database_id = mf.database_id

    Does anyone know how I can clean up the mess I made?

    Thanks for any help,

    webrunner

    -------------------
    A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
    Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html

  • September 13, 2019 at 6:53 pm

    #3679915

    You can't remove tempdb files while SQL is running.  Those are in effect system files.  When you recycle the SQL Server instance, your changes will be put into effect then.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

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

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