Removing .NDF files from a SQL Mirror setup

  • As a test I've migrated data from one file group to another on my principle and been able to successfully "remove" the file and then the filegroup. However when selecting from sys.master_files the file still remains although physically it has gone, the file is marked as OFFLINE, this is the same on the principle and mirror.

    I can only seem to find scraps of information online but usually all information relates to adding a file/filegroup which I've also tested, I've then tested removing my newly adding file/filegroup and the same status appears in sys.master_files for the recently added file.

    Does anyone know how do you get rid of them from being listed under sys.master_files or more importantly know why they're listed as OFFLINE?

  • If you fail over and fail back, does it get updated?

    Sometimes the metadata update in master does not complete. On a non-mirrored database, I usually take the database offline and then back online and it updates. Have not seen the issue with a mirrored database before, but I suspect that failing it over and back will have the same effect of allowing the metadata update to complete.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • I'm afraid that also doesn't remove these files from view in sys.master_files - still listed as OFFLINE, I've also done a restart of the SQL service on both boxes (since they're test) but they remain in the system catalogues.

  • run a transaction log backup and re check

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • genius, that's the ticket.

  • you're welcome 😎

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Great idea, Perry!

    Additionally, you should be backing up the log regularly anyway. the database is in full recovery and log backups are required to mark the inactive portion of the log as reusable. Otherwise, your log file can fill up and keep expanding until it runs out of disk space and then the database will be unusable.


    My blog: SQL Soldier[/url]
    SQL Server Best Practices:
    SQL Server Best Practices
    Twitter: @SQLSoldier
    My book: Pro SQL Server 2008 Mirroring[/url]
    Microsoft Certified Master: SQL Server, Data Platform MVP
    Database Engineer at BlueMountain Capital Management[/url]

  • Indeed, since it's a test box we don't tend to bother however in our live we have a robust backup strategy. Best to find out this way around than have an issue in live though!

    Ta,

    Rik

  • Robert Davis (9/4/2012)


    Great idea, Perry!

    😉

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

Viewing 9 posts - 1 through 8 (of 8 total)

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