January 31, 2011 at 6:50 am
I am running SQL 2008 Enterprise Edition with SP1.
I was splitting a table partition this weekend and needed additional log space. Now that the maintenance is complete, I'm trying to remove the logs that were added but am receiving an error:
When I query the sys.database_files catalog view, the name of the file exists:
select * from sys.database_files
where name like '%log%'
Results:
File ID type_descdata_space_idname physical_name
2 LOG 0SDW_Log F:\Logs\SDW_Log.ldf
216 LOG 0log4 I:\log4.ldf
218 LOG 0Log5 N:\Data\Log5.ldf
I ran a dbcc shrinkfile to empty the file:
dbcc shrinkfile (216, emptyfile)
Then, when I issue the alter database statement, I receive the error:
alter database ss_sapphiredatawarehouse
remove file log4
One or more files listed in the statement could not be found or could not be initialized.
Does anyone have any ideas on how to remove this file? Or how
January 31, 2011 at 7:00 am
You need to specify the logical name and not the physical name of the file.
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 31, 2011 at 7:01 am
That is the logical name. The physical name would be I:\log4.ldf
January 31, 2011 at 7:07 am
Sorry.Didnt notice it.Seems like there is no fix for this.
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=144747&whichpage=2
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
January 31, 2011 at 7:15 am
Thanks.
January 31, 2011 at 8:31 am
I found a very long post that had specifics on how to correct this problem. It is a bug, but there is a workaround. Basically there are two files in the sys.sysaltfiles table with two different fileid numbers. The fileid that the system is trying to locate is a file that I created in error and then removed. The table will apparently re-use these id numbers. So, here is the fix:
"The goal is to get SQL to reuse the "phantom" fileID. In your case, the "phantom" file is actually a data file in the Primary filegroup as noted by the "1" in the GroupID col.
I would recommend considering the following - if it's possible in your environment:
1. Add a data file to primary filegroup
2. Check sys.sysaltfiles and check to see if the FileID has been re-used.
3. Remove data file
4. Backup Log
5. Then, try removing your log file
6. Backup the log again. -- I added this step since the file was place off-line and will be removed when the TLog is backed up.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply