- Remove From My Forums
-
Question
-
Hi When im droping a log file i got this exception
Drop failed for LogFile ‘Test_log6’. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The file ‘Test_log6’ cannot be removed because it is not empty. (Microsoft SQL Server, Error: 5042)
By Sanz. — If you find this post helpful then please «Vote as Helpful» and «Mark As Answer».
Answers
-
Hello,
If your database do have the recovery mode = full, you have to run log backups to release the space in the log file.
Or you have to change the recovery mode to «simple»:
USE [master] GO ALTER DATABASE [YourDatabaseName] SET RECOVERY SIMPLE WITH NO_WAIT GO
Olaf Helper
* cogito ergo sum * errare humanum est * quote erat demonstrandum *
Wenn ich denke, ist das ein Fehler und das beweise ich täglich
Blog
Xing-
Marked as answer by
Tuesday, March 20, 2012 11:25 AM
-
Marked as answer by
I was trying to delete a data file but I encountered the error below.
Error: 5042 — The file ‘MyFileName’ cannot be removed because it is not empty.
I executed the script below to empty the file.
DBCC SHRINKFILE(First_Data_File,EMPTYFILE )
After the file has been emptied, I was able to remove the data file.
ALTER DATABASE DBNAME REMOVE FILE First_File;
EMPTYFILE Migrates all data from the specified file to other files in the same filegroup. In other words, EmptyFile will migrate the data from the specified file to other files in the same filegroup. Emptyfile assures you that no new data will be added to the file.The file can be removed by using the ALTER DATABASE statement.
With SQL Server 2008 SP1, I’ve removed the only table that had a Filestream associated with it but each time I attempt to do the following:
alter database ConsumerMarketingStore remove file CMS_JobInstanceFiles
alter database ConsumerMarketingStore remove filegroup JobInstanceFiles
I get the following exception:
Msg 5042, Level 16, State 10, Line 2
The file ‘CMS_JobInstanceFiles’ cannot be removed because it is not empty.
Msg 5042, Level 16, State 11, Line 3
The filegroup ‘JobInstanceFiles’ cannot be removed because it is not empty.
How in the world do I get rid of the Filestream file and filegroup? Thanks!
asked Feb 15, 2010 at 23:11
James AlexanderJames Alexander
6,13210 gold badges42 silver badges56 bronze badges
1
Make sure the table you dropped is in fact the only table that’s using that filestream file:
select *
from ConsumerMarketingStore.sys.tables t
join ConsumerMarketingStore.sys.data_spaces ds
on t.filestream_data_space_id = ds.data_space_id
and ds.name = 'JobInstanceFiles'
The result of the above query should be empty. If you had other tables with Filestream columns and say you dropped the columns, the table will still use the Filestream file. The way to get rid of this usage is to set table’s Filestream filegroup to NULL:
alter table t1 set (filestream_on = "NULL")
answered Feb 16, 2010 at 18:22
3
In addition to removing the FileStream columns and FileStream attribute from the table, I need to set the database recovery mode to Simple.
ALTER DATABASE [MyDatabase] SET RECOVERY Simple
GO
EXEC SP_FILESTREAM_FORCE_GARBAGE_COLLECTION
ALTER DATABASE [MyDatabase] REMOVE FILE [MyFile]
GO
ALTER DATABASE [MyDatabase] REMOVE FILEGROUP [MyFileGroup]
GO
ALTER DATABASE [MyDatabase] SET RECOVERY FULL
GO
answered Dec 30, 2019 at 12:40
KyeKye
5,93910 gold badges49 silver badges85 bronze badges
You have to run DBCC SHRINKFILE (CMS_JobInstanceFiles, EMPTYFILE)
This will flag the file as «empty» and allow it to be dropped.
Of course, ALTER DATABASE does not mention this, but DBCC SHRINKFILE does… obvious, eh?
answered Feb 16, 2010 at 4:53
gbngbn
423k82 gold badges586 silver badges677 bronze badges
3
After you drop the table, the garbage collector takes a while to clean up the data. That could be the reason for this happening. You can force garbage collection by issuing a CHECKPOINT.
You can verify whether the FILESTREAM data is cleaned up by going to the Filestream data container. If the FILESTREAM data is deleted an ALTER DATABASE dbname REMOVE FILE will usually succeed. Once it is done, you can issue ALTER DATABASE dbname REMOVE FILEGROUP.
GDP
8,1096 gold badges45 silver badges82 bronze badges
answered Feb 17, 2010 at 17:17
1
@inam — my situation was a bit different; I created a second filegroup and moved a clustered index to it. Thereafter I deleted the clustered index in the secondary file group. After that I tried to delete the filegroup, but I kept getting the error, the file group is not empty.
I saw some other posts that indicated to move the data to another file within the same filegroup. The problem here was I only had one file in the file group, and I wanted to delete the filegroup and file in it.
The workaround I came up with is as follows.
I copied all the records from table1 into table2 (select * into table2 from table1)
I then deleted table1; thereafter I was able to delete the filegroup2 and the
data file in it.
Last I renamed table2 to table1.
answered Sep 15, 2017 at 17:08
WRITE FOR US
- Remove From My Forums
-
Question
-
Hi When im droping a log file i got this exception
Drop failed for LogFile ‘Test_log6’. (Microsoft.SqlServer.Smo)
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
The file ‘Test_log6’ cannot be removed because it is not empty. (Microsoft SQL Server, Error: 5042)
By Sanz. — If you find this post helpful then please «Vote as Helpful» and «Mark As Answer».
Answers
-
I think this question is related to SQL server and this forum is related to Lync, if this question is related to SQL please post your question here
http://social.technet.microsoft.com/Forums/en/category/sqlserver
If answer is helpful, please hit the green arrow on the left, or mark as answer. Salahuddin | Blogs:http://salahuddinkhatri.wordpress.com | MCITP Microsoft Lync
-
Proposed as answer by
Saturday, March 17, 2012 8:44 PM
-
Marked as answer by
Sharon.Shen
Tuesday, March 20, 2012 6:30 AM
-
Proposed as answer by