Home Forums SQL Server 2008 SQL Server 2008 Administration Failed "ALTER DATABASE...REMOVE FILE" leaves sys.database_files missing a file RE: Failed "ALTER DATABASE...REMOVE FILE" leaves sys.database_files missing a file

  • July 15, 2014 at 3:07 am

    #1729359

    CaptainSlock (12/15/2011)

    The file "K:\sqldata\PROD_CRICKET\tempdev2.ndf" has been modified in the system catalog. The new path will be used the next time the database is started.

    Msg 5042, Level 16, State 1, Line 1

    The file 'tempdev2' cannot be removed because it is not empty.

    This message is clearly saying what has happened. The meta-data in the system tables is altered and the records related to the 'tempdev2.ndf' file has been removed. But because this file is in use by the SQL Engine itself, it can only be removed from the system after a restart of the SQL Server service.

    After you have restarted the service, the file is not in use anymore (e.g. locked) and can be removed from disk.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **