Empty/Remove Log File - Not Working

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Empty/Remove Log File - Not Working

Author

Topic

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-01-20 : 13:37:02

My usual means for removing secondary log files is failing with the message of: "cannot be removed because it is not empty." Here is my script:

dbcc shrinkfile(MyDB_log2, EmptyFile);

alter database MyDB
remove file MyDB_log2;

I have tried using DBCC CHECKDB, DBCC UPDATEUSAGE and dbcc shrinkfile(MyDB_log3, 0) as a prelude to the above script. They each report no problems but the actual remove file still fails.

The primary log file is 1024MB in size with 12MB used. The target secondary file is 320MB in size with 106 used. There is plenty of room for the data migration to take place. Given the sizes involved I could live with the wasted space but, being a DBA, I just can't let it go. Any insights from the pantheon...? All suggestions welcomed.

Additional info:
SQL 2008R2 Enterprise
Simple recovery mode
Tried changing to Full and performing a log backup; No help.

No amount of belief makes something a fact. -James Randi

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-20 : 14:12:26

I've run into this before and had to do several EmptyFile commands paired with backup log before I finally was able to remove it.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-01-20 : 14:19:47

I see you edited your original post to say it's SIMPLE recovery model. I would try filling up both logs, committing the transaction and then re-try the steps you did.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2015-01-23 : 14:20:41

You don't need to shrink the file - just need to make sure the VLF's in the second file are no longer in use - then remove the file.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources