Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
state_desc = OFFLINE and drop_isn = a really long string of numbers. advice? –  EkoostikMartin Jun 15 '11 at 19:12
    
Ran a transaction log backup and the file disappeared from the system as I had hoped. Thanks for the help. –  EkoostikMartin Jun 15 '11 at 21:53
add comment

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

share|improve this answer
    
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? –  EkoostikMartin Jun 14 '11 at 22:06
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.