Error 5042 Sql
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 the file cannot be removed because it is not empty. (microsoft sql server error 5042) it can be corrected. Out of them, some are easy and some are difficult. I was the file cannot be removed because it is not empty sql server running out of space on my VM and I started hunting for a bigger file on my machine. It was not there is insufficient space in the filegroup to complete the emptyfile operation. 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 5042. sp_helpdb
Filegroup Cannot Be Removed Because It Is Not Empty
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 message, it sql server log file cannot be removed because it is not empty 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 - Scripting Tip: PowerShell to Identify Last Bootup Time February 22, 2016Pinal Dave SQL SERVER - Error: Property BackupDirectory is Not Available for Settings October 10, 2016Pinal Dave SQL SERVER - Case Sensitive Database and Database User - Fix: Error: 15151 - Cannot find the user , because it does not exist or you do not have permission. February 18, 2012Pinal 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 3700 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
Get Your Own Blog If you would like to blog on SQLServerCentral.com then send an email to webmaster@sqlservercentral.com. Contact the author Please log in or register to contact the author of this blog All Blogs All Bloggers cannot be removed because it is not empty powershell on SQL Server Central Feeds Subscribe to this blog Archives for this blog June 2016
Dbcc Shrinkfile Emptyfile
January 2016 October 2015 May 2015 January 2015 June 2014 April 2014 March 2014 January 2014 September 2013 June 2013 May 2013 April 2013
Cannot Move All Contents Of File To Other Places To Complete The Emptyfile Operation.
March 2013 February 2013 October 2012 September 2012 August 2012 July 2012 June 2012 November 2011 September 2011 August 2011 September 2010 August 2010 July 2010 May 2010 September 2009 August 2009 April 2009 January 2009 December 2008 November http://blog.sqlauthority.com/2016/05/10/sql-server-error-msg-5042-level-16-file-tempdev02-cannot-removed-not-empty/ 2008 October 2008 September 2008 August 2008 July 2008 June 2008 April 2008 March 2008 February 2008 January 2008 December 2007 November 2007 September 2005 DBA Philosophy Journey to trouble-shoot Error 5042 (cannot remove a file xxx because it is not empty) Posted on 10 August 2009 Comments Briefcase Print I was working on a table partition management task yesterday. The table is partitioned on a datetime column, and each partition has one and only one dedicated http://www.sqlservercentral.com/blogs/jeffrey_yao/2009/08/10/error-5042-cannot-remove-a-file-because-it-is-not-empty/ filegroup, and each filegroup is dedicated to one and only one partition and contains only one file in the filegroup. After I cleaned out the records in the left most partition and then merge it with the next right partition, the filegroup originally dedicated to the left-most partition can be dropped. Of course, I have to remove the file inside the filegroup first before I can drop the filegroup. However, when I tried to remove the file by running-- the file_data_20090801 is the only file in the filegroup fg_data_20090801, which was used by the left-most partitionALTER DATABASE MyDatabase REMOVE FILE file_data_20090801I get the following error: Msg 5042, Level 16, State 1, Line 1The file 'file_data_20090801' cannot be removed because it is not empty.I am pretty sure there is no record in that file and nothing on the filegroup either, but to make sure I am correct, I run the following script to checkUSE MyDatabase;SELECT * FROM sys.allocation_units aINNER JOIN sys.filegroups fgON fg.data_space_id = a.data_space_idAND fg.name = 'fg_data_20090801';I did not get anything back, so this confirms that there is nothing inside the filegrouup / file.The interesting thing is that this same table exists in different dbs on different servers, however, the error 5042 only occurs on a few servers. I was totally lost, so I resort to google to try to figure out whether there was anyone encountered the same error when doing the partiti
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums Answered by: Drop failed for LogFile Microsoft SQL https://social.msdn.microsoft.com/Forums/sqlserver/en-US/1405114b-d95f-44fc-8813-8c177ec4a2c7/drop-failed-for-logfile-microsoft-sql-server-error-5042?forum=sqlgetstarted Server, Error: 5042 SQL Server > Getting started with SQL Server http://stackoverflow.com/questions/2269610/sql-server-2008-unable-to-remove-a-filestream-file-and-filegroup Question 0 Sign in to vote 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 cannot be 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". Tuesday, March 20, 2012 7:29 AM Reply | Quote Answers 1 Sign in to vote Hello, If your database do have the recovery mode = full, you have to run log cannot be removed 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 san Sanz Tuesday, March 20, 2012 11:25 AM Tuesday, March 20, 2012 11:13 AM Reply | Quote Moderator All replies 0 Sign in to vote Do you try DROP you log?? May be SHRINK? In any case - write the exactly command you try to execute.www.sqlCMD.ru - all around MS SQL Server Tuesday, March 20, 2012 7:43 AM Reply | Quote 0 Sign in to vote Hello, As long as the log file of the database is in usage, you can't drop it. You have to clean it with SHRINKFILE + option EMPTYFILE.Olaf Helper * cogito ergo sum * errare humanum est * quote
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 Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server 2008: Unable to remove a FileStream File and Filegroup up vote 9 down vote favorite 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! sql-server-2008 filestream share|improve this question asked Feb 15 '10 at 23:11 James Alexander 2,69162745 Shouldn't this be on ServerFault.com? –mjv Feb 15 '10 at 23:33 add a comment| 3 Answers 3 active oldest votes up vote 10 down vote accepted 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") share|improve this answer edited Oct 15 '15 at 13:28 Shawn Melton 1,807726 answered Feb 16 '10 at 18:22 Pawel Marciniak 1,838815 You are an amazing man. After days of MS forums, talking to folk