Microsoft Sql Server Error 5042
Contents |
SERVER - Error Msg 5042, Level 16 – The File ‘tempdev02' Cannot be Removed Because it is Not Empty May 10, 2016Pinal DaveSQLNo CommentsAs a SQL Server DBA you would know that sometimes it is possible to make mistakes with the database. Few of the time there is insufficient space in the filegroup to complete the emptyfile operation. it can be corrected. Out of them, some are easy and some are difficult. I filegroup cannot be removed because it is not empty was running out of space on my VM and I started hunting for a bigger file on my machine. It was
The File Cannot Be Removed Because It Is Not Empty.
not difficult to find and recall that I added some files to the TempDB database while doing some testing. Here is the command to get details about the database file, which gave me Error Msg
Cannot Be Removed Because It Is Not Empty Powershell
5042. sp_helpdb tempdb When I tried to remove the file via the UI and got below error.Also tried via T-SQL below USE [tempdb] GO ALTER DATABASE [tempdb] REMOVE FILE [tempdev02] GO And got the same errorMsg 5042, Level 16, State 1, Line 8 The file ‘tempdev02' cannot be removed because it is not empty.This was not a production environment so it was easy for me to restart, but looking at the dbcc shrinkfile emptyfile message, it looks like it would work, if I make the file empty. So I ran below command USE [tempdb] GO DBCC SHRINKFILE (N'tempdev02', EMPTYFILE) GO It was successful and then I was able to remove the file.Reference: Pinal Dave (http://blog.sqlauthority.com) Tags: SQL Error Messages, SQL Server, SQL TempDB212Related Articles SQL SERVER - Fix : Error : Login failed for user ‘UserName'. The user is not associated with a trusted SQL Server connection November 2, 2008Pinal Dave SQL SERVER - 2016 - Check Value as JSON With ISJSON() February 26, 2016Pinal Dave SQL SERVER - TempDB in RAM for Performance April 10, 2011Pinal DaveLeave a Reply Cancel reply Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com . Nupur Dave is a social media enthusiast and and an independent consultant. PDF Downloads SQL Coding Standards SQL FAQ DownloadDownload SQL SERVER 2016 (FREE)Exclusive Newsletter SQL Interview Q & ASearch © 2016 All rights reserved. SQLAuthority.com
Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » Administration » Cannot delete data file Cannot delete
Cannot Move All Contents Of File To Other Places To Complete The Emptyfile Operation.
data file Rate Topic Display Mode Topic Options Author Message Kedar PotdarKedar Potdar Posted Thursday, July 13, 2006 3:35 PM Forum Newbie Group: General Forum Members Last Login: Monday, November 26, 2007 2:19 PM Points: 8, Visits: 3 I have a database with 8 data files. I ran a DBCC Shrinkfile with emptyfile option on one file (90GB size) which ran successfully. But when I try to delete the file http://blog.sqlauthority.com/2016/05/10/sql-server-error-msg-5042-level-16-file-tempdev02-cannot-removed-not-empty/ now, it throws the following error:Error 5042: The file 'data8' cannot be removed because it is not empty.I tried the same thing with other smaller data files and I could easily delete those files. Even when I try to shrink the file, the file size remains same. Please help!! I need to urgently get the database size reduced.Thanks,Kedar Post #294266 Dinesh AsankaDinesh Asanka Posted Friday, July 14, 2006 12:10 AM SSC http://www.sqlservercentral.com/Forums/Topic294266-5-1.aspx Eights! Group: General Forum Members Last Login: Thursday, October 1, 2015 2:28 AM Points: 912, Visits: 223 Try after deatching the DB My Blog: http://dineshasanka.spaces.live.com/ Post #294319 John Mitchell-245523John Mitchell-245523 Posted Friday, July 14, 2006 7:03 AM SSCertifiable Group: General Forum Members Last Login: Today @ 8:40 AM Points: 6,907, Visits: 14,400 KedarRun this script, after inserting your logical file name in both places (the logical file name is what you see under File Name in the Data Files tab of the Properties box of the database). If it returns any items, then you need to move the tables/indexes to a different filegroup before proceeding.JohnSELECT o.name AS TableOrIndexFROM sysfiles fJOIN dbo.sysfilegroups sON f.groupid = s.groupidJOIN dbo.sysindexes iON i.groupid = s.groupidJOIN dbo.sysobjects oON i.id = object_id(o.name)AND i.indid in (0, 1)WHERE f.name = 'My_Logical_File_Name'UNIONSELECT i.nameFROM sysindexes i join sysfilegroups f ON i.groupid = f.groupidJOIN sysfiles l ON f.groupid = l.groupidWHERE l.name = 'My_Logical_File_Name' Post #294427 baseLogiKbaseLogiK Posted Monday, December 14, 2009 5:57 AM SSC Rookie Group: General Forum Members Last Login: Monday, December 14, 2009 5:54 AM Points: 25, Visits: 15 I had the same problem and tried the aforementioned query graciously provided by John Mitchell-245523, but found no objects listed. I ended up running DBCC SHRINKFILE('logical_file_name', EMPTYFILE). After that, the file drop
Server 2016 SQL Server 2014 SQL Server 2012 SQL Server 2008 AdministrationBackup and Recovery Cloud High Availability Performance Tuning PowerShell Security Storage Virtualization DevelopmentASP.NET Entity Framework T-SQL Visual Studio Business IntelligencePower BI SQL Server Analysis Services http://sqlmag.com/blog/curious-case-un-droppable-transaction-log-file SQL Server Integration Services SQL Server Reporting Services InfoCenters Advertisement Home > Blogs > SQL Server Questions Answered > The Curious Case of: the un-droppable transaction log file SQL Server Questions Answered The Curious http://serverfault.com/questions/349848/filegroup-cannot-be-removed-because-it-is-not-empty-error-with-no-files Case of: the un-droppable transaction log file Jul 17, 2011 by Paul S. Randal in SQL Server Questions Answered RSS EMAIL Tweet Comments 0 Question: I had to add a second log file to my cannot be database because the log file ran out of space and I was unable to perform a transaction log backup. Now I can’t drop the second log file. Can you help? Answer: Adding a second transaction log file temporarily is quite acceptable when the log file runs out of space. If the log cannot be cleared (usually by a transaction log backup) then the alternatives really come down to adding another cannot be removed log file or switching to the simple recovery model (and breaking the log backup chain). Once the crisis has passed, the second log file should be removed again. In this case, when the ALTER DATABASE was issued to drop the second log file, the error returned was: Msg 5042, Level 16, State 2, Line 1 The file 'DBMaint2008_log2' cannot be removed because it is not empty. Looking at log_reuse_wait_desc in sys.databases to see why the log cannot be cleared shows NOTHING. So how can this be? Well, it could be that the currently active VLF (Virtual Log File) happens to be in the second file – there always has to be at least one active VLF. Looking at DBCC LOGINFO to see the VLF status shows the following: FileId FileSize StartOffset FSeqNo Status Parity CreateLSN ------ -------- ----------- ------ ------ ------ --------- 2 1245184 8192 25 2 64 0 3 1245184 8192 0 0 0 0 Ah – this is the problem! The log files each only have a single VLF, and the entire log must always have at least two VLFs. This means the second log file cannot be dropped as that would leave the entire log with only a single VLF
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Filegroup cannot be removed because it is not empty error with no files up vote 2 down vote favorite When I try to remove 'MYFILEGROUP', I get the error that the filegroup cannot be removed because it is not empty. I already removed all tables, indexes and deleted all files associated with the group. To verify I ran the scripts below: sp_helpfilegroup returns 0 for the filecount on 'MYFILEGROUP'. select o.name, s.groupname from sysobjects o join sysindexes i on o.id = i.id join sysfilegroups s on i.groupid = s.groupid where groupname = 'MYFILEGROUP' returns no rows ... I have some more information. When I run dbcc checkfilegroup('MYFILEGROUP') The result lists all of the primary keys with a warning stating: Cannot process rowset ID 72057597605511168 of object "TableName" (ID 2071414), index "PK_TableName" (ID 1), because it resides on filegroup "PRIMARY" (ID 1), which was not checked. Is this expected behavior or does it indicate a problem with the system tables? If it is a problem then how would I fix it? I removed every foriegn key, index and constraint from the database leaving only the tables. It still errors when I try to remove the empty "MYFILEGROUP". sql-server sql-server-2008-r2 groups share|improve this question edited Jan 14 '12 at 17:33 asked Jan 14 '12 at 3:08 Mindy 6228 add a comment| 3 Answers 3 active oldest votes up vote 2 down vote accepted is it possible MYFILEGROUP has a parition object on it? If this is the case then the compatability views your using will not return any results (sql server 2000 did not have partition objects!). The messag