Microsoft Sql Error 5042
Contents |
by FeedBurner SQLServer-DBA.com Links Recent Posts How can I tell if hyperthreading is on Windows 2012 How to schedule Powershell Script with Task Scheduler Lots of NULL values can cause trouble ALTER INDEX REBUILD the file cannot be removed because it is not empty. (microsoft sql server error 5042) clears sys.dm_db_index_usage_stats 7 mistakes DBAs make on CV or resume 5 ways globalisation
There Is Insufficient Space In The Filegroup To Complete The Emptyfile Operation.
has impacted IT Is a LOG BACKUP allowed on a SIMPLE RECOVERY database How to turn AUTO_SHRINK off 7 Essential filegroup cannot be removed because it is not empty Steps to Recover a Corrupt SQL Server Database DBA vacancy at Travelers - UK based Powered by TypePad Msg 5042 - The file cannot be removed because it is not empty and dbcc cannot be removed because it is not empty powershell shrinkfile emptyfile 06 February,2013 by Jack Vamvas To remove a secondary sql transaction log file is accomplished by the ALTER DATABASE REMOVE FILE command . You may have needed to add a second transaction log file to deal with a very large transaction . Now the transaction is finished , you’re looking to delete the secondary transaction log file. USE [mydatabase] GO ALTER DATABASE [mydatabase] REMOVE FILE [mydatabase_Log_2]
Dbcc Shrinkfile Emptyfile
GO But under certain circumstances this error message may appear Msg 5042, Level 16, State 1, Line 1 The file 'mydatabase_Log_2' cannot be removed because it is not empty. To fix this error use the DBCC SHRINKFILE with the EMPTYFILE argument command dbcc ShrinkFile (mydatabase_Log_2, EmptyFile) The EMPTYFILE argument moves data from the chosen file to another file in the same filegroup.Once completed , rerun the ALTER DATABASE command Read More SQL Server - Inside the sql transaction logs with fn_dblog and current transaction_id SQL Server – Read sql transaction Logs with ::fn_dblog SQL Server – How to remove a sql transaction log file on a database SQL Server – Transaction Log files sequential Author: Jack Vamvas (http://www.sqlserver-dba.com) Share: Posted by Jack Vamvas at 6:44 AM in Transaction Logs | Permalink | Comments (0) | TrackBack (0) Searches: dbcc emptyfile, dbcc shrinkfile, dbcc shrinkfile emptyfile, delete transaction log file, remove secondary transaction log file, remove transaction log, remove transaction log, shrink emptyfile Verify your Comment Previewing your Comment Posted by: | This is only a preview. Your comment has not yet been posted. Your comment could not be posted. Error type: Your comment has been posted.
up Get Your Own Blog If you would like to blog on SQLServerCentral.com then send cannot move all contents of file to other places to complete the emptyfile operation. 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 on SQL Server Central Feeds Subscribe to this blog Archives for this blog June 2016 January 2016 October 2015 May 2015 January 2015 June 2014 April 2014 March 2014 January 2014 September 2013 June http://www.sqlserver-dba.com/2013/02/msg-5042-the-file-cannot-be-removed-because-it-is-not-empty-and-dbcc-shrinkfile-emptyfile.html 2013 May 2013 April 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 2008 October 2008 September 2008 August 2008 July 2008 June 2008 April 2008 March http://www.sqlservercentral.com/blogs/jeffrey_yao/2009/08/10/error-5042-cannot-remove-a-file-because-it-is-not-empty/ 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 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 50
up Recent PostsRecent Posts Popular TopicsPopular Topics Home Search Members Calendar Who's On Home » SQL Server 7,2000 » Administration » Cannot delete data file http://www.sqlservercentral.com/Forums/Topic294266-5-1.aspx Cannot delete 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 https://dacosta9.wordpress.com/2011/03/04/the-file-mydatabase_log2-cannot-be-removed-because-it-is-not-empty-server-msg-5042-level-16-state-2-line-1/ 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 cannot be file 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 cannot be removed 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 dropped wit
16, State 2, Line 1 Posted on March 4, 2011 by john.dacosta When doing some re-indexing on a server, I had to create an additional log file so that the disk drive would not fill up and stop the database / re-index operation. Subsequent to doing my re-index, no matter how many times I attempted to do a dbcc shrinkfile with the emptyfile option, the file would not delete. The file ‘Database_Log_File' cannot be removed because it is not empty.
USE [myDatabase]
GO
-- EMPTY TRANSACTION LOG FILE
DBCC SHRINKFILE(myDatabase_LOG2,EMPTYFILE)
GO
-- TO CLEAR OUT TRANSACTION LOG FILE -- PERFORM A TRANSACTION LOG BACKUP - Standard SQL Backup
BACKUP LOG [myDatabase] TO DISK = '\\myBackupShare\myDatabase_yyyymmdd.trn',INIT, PASSWORD= 'myPasword';
-- TO CLEAR OUT TRANSACTION LOG FILE -- PERFORM A TRANSACTION LOG BACKUP - Red Gate
DECLARE @exitcode int
DECLARE @sqlerrorcode int
EXEC MASTER..sqlbackup N'-SQL "BACKUP LOG [myDatabase]
TO DISK = ''\\myBackupShare\''
WITH NAME = '''', DESCRIPTION = '''',
INIT, PASSWORD = ''myPasword''
, MAXDATABLOCK = 65536,KEYSIZE = 256, COMPRESSION = 1, THREADS = 1"'
, @exitcode OUTPUT, @sqlerrorcode OUTPUT
GO
-- REMOVE TRANSACTION LOG FILE
ALTER DATABASE [myDatabase] REMOVE FILE [myDatabase_LOG2]
GO
Like this:Like Loading... Related Posted in administration, UncategorizedTagged backup database administration databases dba dbcc red gate shrinkfile SQL SERVER transaction log Post navigation ←Automatically Save Email Attachments following a naming conventionSSIS Change Text File Column Delimiter in Text Editor→ One thought on “The file ‘myDatabase_LOG2' cannot be removed because it is not empty. Server: Msg 5042, Level 16, State 2, Line 1” gfikes says: July 31, 2012 at 3:10 pm I had the same issue for the same reason. I had the right steps but the wrong order. Thanks for posting! Reply Leave a Reply Cancel reply Enter your comment here... Fill in your details below or click an icon to log in: Email (required) (Address never made public) Name (required