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 ms sql error 5042 mistakes with the database. Few of the time it can be corrected. error 5042 cluster Out of them, some are easy and some are difficult. I was running out of space on my VM and error code 5042 I started hunting for a bigger file on my machine. It was not difficult to find and recall that I added some files to the TempDB database while doing some testing.
There Is Insufficient Space In The Filegroup To Complete The Emptyfile Operation.
Here is the command to get details about the database file, which gave me Error Msg 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 the file cannot be removed because it is not empty sql server 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 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
up 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 filegroup cannot be removed because it is not empty All Bloggers on SQL Server Central Feeds Subscribe to this blog Archives for this blog June
Sql Server Log File Cannot Be Removed Because It Is Not Empty
2016 January 2016 October 2015 May 2015 January 2015 June 2014 April 2014 March 2014 January 2014 September 2013 June 2013 May 2013 April
Cannot Be Removed Because It Is Not Empty Powershell
2013 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 partition management. But
(Русский)ישראל (עברית)المملكة العربية السعودية (العربية)ไทย (ไทย)대한민국 (한국어)中华人民共和国 (中文)台灣 (中文)日本 (日本語) HomeLibraryLearnDownloadsTroubleshootingCommunityForums Ask a question Quick access Forums home Browse forums users FAQ Search related threads Remove From My Forums 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 Answered by: Drop failed for LogFile Microsoft SQL Server, Error: 5042 SQL Server > Getting started with SQL Server 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 cannot be 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". Tuesday, March 20, 2012 7:29 AM Reply | cannot be removed Quote Answers 1 Sign in to vote 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 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 Rep