2

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.

2 Answers 2

3

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.

2
  • state_desc = OFFLINE and drop_isn = a really long string of numbers. advice? Jun 15, 2011 at 19:12
  • Ran a transaction log backup and the file disappeared from the system as I had hoped. Thanks for the help. Jun 15, 2011 at 21:53
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

1
  • The "sp_helpdb" command showed only one log file. I also checked my SAN partition and the physical file appears to be gone as well. I went ahead and completely rebooted the server, but in SSMS it still thinks I have two log files. What is going on here? Jun 14, 2011 at 22:06

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .