December 21, 2009 at 11:07 am
I have a database I inherited on our development server. At some point, someone decided to add a second log file to the database. Maybe this would make sense on a huge actively used system, but this is on a development server, with the database set to simple recovery.
I'm trying to organize all the databases, and I wanted to delete this extra unused log file. I think I have followed the instructions, but I keep getting an error message.
I followed the instructions here http://msdn.microsoft.com/en-us/library/ms175574(SQL.90).aspx
and here http://msdn.microsoft.com/en-us/library/ms190757(SQL.90).aspx
I tried doing this through Management Studio, then I scripted it out, as follows.
CREATE DATABASE [Sample_DB] ON PRIMARY
( NAME = N'Sample_DB', FILENAME = N'F:\SQL Data\Sample_DB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sample_DB_log', FILENAME = N'F:\SQL Data\Sample_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%),
( NAME = N'Sample_DB_LOG2', FILENAME = N'F:\SQL Data\Sample_DB_LOG2.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Sample_DB', @new_cmptlevel=90
GO
ALTER DATABASE [Sample_DB] SET RECOVERY SIMPLE
GO
USE [Sample_DB]
GO
/* This section is the code I tried to drop the second log file */
USE [SAMPLE_DB]
GO
DBCC SHRINKFILE (N'SAMPLE_DB_Log2' , EMPTYFILE)
GO
use master
go
/* This next line works in the sample, fails in my actual database */
ALTER DATABASE [SAMPLE_DB]
REMOVE FILE [SAMPLE_DB_Log]
GO
/* Drop the database now that I am done with it */
USE [master]
GO
DROP DATABASE [Sample_DB]
GO
When I try this in this sample, it works. When I test this in my actual database, it fails. I checked and the file is there, and I have security rights to it.
Msg 5009, Level 16, State 9, Line 1
One or more files listed in the statement could not be found or could not be initialized.
Am I missing something here? Anyone have any ideas?
December 21, 2009 at 11:38 am
One comment on the code.
USE [SAMPLE_DB]
GO
DBCC SHRINKFILE (N'SAMPLE_DB_Log2' , EMPTYFILE)
GO
use master
go
/* This next line works in the sample, fails in my actual database */
ALTER DATABASE [SAMPLE_DB]
REMOVE FILE [b]SAMPLE_DB_Log[/b]
GO
The file you refer to in the shrinkfile and the file you refer to in the alter database are different. Is that intentional?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 21, 2009 at 1:38 pm
GilaMonster (12/21/2009)
One comment on the code.
USE [SAMPLE_DB]GO
DBCC SHRINKFILE (N'SAMPLE_DB_Log2' , EMPTYFILE)
GO
use master
go
/* This next line works in the sample, fails in my actual database */
ALTER DATABASE [SAMPLE_DB]
REMOVE FILE [b]SAMPLE_DB_Log[/b]
GO
The file you refer to in the shrinkfile and the file you refer to in the alter database are different. Is that intentional?
That was a typo...
CREATE DATABASE [Sample_DB] ON PRIMARY
( NAME = N'Sample_DB', FILENAME = N'F:\SQL Data\Sample_DB.mdf' , SIZE = 3072KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'Sample_DB_log', FILENAME = N'F:\SQL Data\Sample_DB_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%),
( NAME = N'Sample_DB_LOG2', FILENAME = N'F:\SQL Data\Sample_DB_LOG2.ldf' , SIZE = 1024KB , FILEGROWTH = 1024KB )
GO
EXEC dbo.sp_dbcmptlevel @dbname=N'Sample_DB', @new_cmptlevel=90
GO
ALTER DATABASE [Sample_DB] SET RECOVERY SIMPLE
GO
USE [Sample_DB]
GO
/* This section is the code I tried to drop the second log file */
USE [SAMPLE_DB]
GO
DBCC SHRINKFILE (N'SAMPLE_DB_Log2' , EMPTYFILE)
GO
use master
go
/* This next line works in the sample, fails in my actual database */
ALTER DATABASE [SAMPLE_DB]
REMOVE FILE [SAMPLE_DB_Log2]
GO
/* Drop the database now that I am done with it */
USE [master]
GO
DROP DATABASE [Sample_DB]
GO
December 21, 2009 at 4:13 pm
I am not sure, but I don't think the EMPTYFILE option actually works with log files. You need to issue checkpoints until the VLF is located in the primary file. Once that is done, you should be able to just remove the secondary log file.
Use DBCC SQLPERF to find out where the active VLF is.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
December 21, 2009 at 6:26 pm
Thanks for the advice...in the end, it turned out to be one of those hair pulling experiences that I don't understand, but I learned and I moved on.
I used sp_helpdb to obtain a list of files, and confirmed the that logfile I was trying to remove really did exist.
I tried changing the size, changing the name, changing growth settings...and I kept getting messages telling me that the file did not exist.
Eventually I noticed that primary data file was listed as file 1, the log file as file 2 and the file I wanted to remove was file 4. I wondered what happened to file 3, and in a moment of either inspiration or desperation - take your pick - I created a new data file, and it showed up as file 3.
Suddenly, my script was able to resize, change the autogrowth, change the name, and then delete the log file. Then I deleted my temporary file, and I'm back to one data, one log.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply