Home » Topic » how to remove secondary transaction log file?

how to remove secondary transaction log file?

Lincoln Burrows ~ Modified: November 27th, 2015 ~ ~ 1 Minute Reading

Home Forums how to remove secondary transaction log file?

Viewing 7 posts - 1 through 7 (of 7 total)
  • Author
    Posts
  • #919 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    I am looking to delete the secondary transaction log file. But when I try to delete the file by ALTER DATABASE REMOVE FILE command it throws me the following error
    Msg 5042, Level 16, State 1, Line 1
    The file ‘mydatabase_Log_2’ cannot be removed because it is not empty.
    How can I delete the secondary transaction log file? Please help

    #925 Score: 0
    Stephen West
    Moderator
    4 pts

    Try this

    SELECT o.name AS TableOrIndex
     FROM sysfiles f
    JOIN dbo.sysfilegroups s
     ON f.groupid = s.groupid
    JOIN dbo.sysindexes i
     ON i.groupid = s.groupid
    JOIN dbo.sysobjects o
     ON i.id = object_id(o.name)
     AND i.indid in (0, 1)
    WHERE f.name = 'My_Logical_File_Name'
    UNION
    SELECT i.name
     FROM sysindexes i join sysfilegroups f ON i.groupid = f.groupid
     JOIN sysfiles l ON f.groupid = l.groupid
    WHERE l.name = 'My_Logical_File_Name'
    #961 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    Thanks Stephen for providing the script, I tried the script, but found no object listed. It didn’t work for me, I am still facing the same problem

    #965 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    Use the DBCC SHRINKFILE with the EMPTYFILE argument command

    dbcc ShrinkFile ('logical_file_name', EmptyFile)

    #1711 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    I have tried it but it doesn’t work for me. Is there any other way ?

    #1989 Score: 0
    Andrew Jackson
    Moderator
    1 pt

    You can also follow the below steps:

    1. Connect to the instance of SQL Server Database Engine & expand the instance.
    2. Expand Databases > right-click the database from which to delete the file, and then click Properties.
    3. Select the Files page.
    4. In the Database files grid, select the file to delete and then click Remove.
    5.Click OK.

    #2067 Score: 0
    Lincoln Burrows
    Moderator
    16 pts

    Thanks Andrew, this was really helpful. I had been struggling with this problem for a long time and couldn’t figure it out. Your solution fixed it

Viewing 7 posts - 1 through 7 (of 7 total)
  • You must be logged in to reply to this topic.