The file ‘myDatabase_LOG2’ cannot be removed because it is not empty. Server: Msg 5042, Level 16, State 2, Line 1

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

Advertisements

One Reply to “The file ‘myDatabase_LOG2’ cannot be removed because it is not empty. Server: Msg 5042, Level 16, State 2, Line 1”

  1. I had the same issue for the same reason. I had the right steps but the wrong order. Thanks for posting!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s