Home / server / Questions / 280404
Accepted
EkoostikMartin
Asked: 2011-06-15 10:22:24 +0800 CST

SQL Server database with two log files

  • 772

SQL Server 2008 Enterprise

I have inherited a SQL database that has been configured to use two transaction log files. I'd like to just get rid of one of the log files, because it's really not best practice and just creates additional maintenance requirements. I have pulled a full backup and restored to a test server, and have attempted to just remove the 2nd log file using SSMS.

The "Remove" appeared to work, but when I went back into the database later the 2nd file had reappeared (at 1 MB in size). At this point, I was not able to remove it again at all, recieved an error from SSMS tool.

sql-server

2 Answers

  1. Best Answer

    Take a look at sys.database_files while in the context of the database in question. Specifically, look at the state_desc and drop_lsn columns. You may have to back up the database after dropping the file to get it to disappear completely from the system.

    • 3

  2. Do a SQL backup of the log file then remove the log file from the database. The backup you perform will depend on what recover model your database is in.

    You can use ALTER DATABASE to remove the log file once there is no active data in the log file.

    ALTER DATABASE MyDataBase
REMOVE FILE MyDataBaseLog

    Where MyDataBaseLog is the logical filename of your log file.

    You can get the logical name of your log file by using sp_helpdb:

    sp_helpdb MyDataBase

    The file information is in the 2nd resultset returned by sp_helpdb

    • 2