I just read a newgroup question whether doing SHRINKFILE with the EMPTYFILE option for the primary log file somehow cause ill effects.
Shrinkfile for the ldf will not move any data (log records) or so. For an ldf file it is basically a preparation to tell the engine that you are about to remove this file (ALTER DATABASE … REMOVE FILE).
Now, the first (primary) log file is special and cannot be removed. So, what if we do an EMPTYFILE on the primary log file. Will we end up in some limbo-state? I did a test and performed EMPTYFILE on the primary file. Nothing bad happened. I then did EMPTYFILE on the other log file and removed that file successfully. So it seems that this should not cause any havoc. Just pretend you never did that EMPTYFILE operation against the primary log file.
It isn’t doable to create a repro script which show shrinking and removing nf log files.It will require some engagement for you. The reason is that we never know from what file and where the nect virtual log file comes from. So, if you are about to run below, be prepared to read up on DBCC LOGINFO and other command, understand what VLF is, perhaps some operation need to be done everal times before what we expect will happen… And as always, use at own risk.
--Drop and create database named x
SET NOCOUNT ON
USE master
IF DB_ID('x') IS NOT NULL DROP DATABASE x
GO
CREATE DATABASE [x] ON PRIMARY
( NAME = N'x', FILENAME = N'C:\DemoDatabases\DbFiles\a\x.mdf' , SIZE = 10MB, FILEGROWTH = 3MB )
LOG ON
( NAME = N'x_log', FILENAME = N'C:\DemoDatabases\DbFiles\a\x_log.ldf' , SIZE = 2MB , FILEGROWTH = 1MB)
,( NAME = N'x_log2', FILENAME = N'C:\DemoDatabases\DbFiles\a\x_log.ldf2' , SIZE = 2MB , FILEGROWTH = 1MB)
GO
--Get the database out of "auto-truncate" mode.
ALTER DATABASE x SET RECOVERY FULL
BACKUP DATABASE x TO DISK = 'nul'
--Fill up the log some
USE x
CREATE TABLE t(c1 INT IDENTITY, c2 CHAR(300) DEFAULT 'a')
GO
INSERT INTO t DEFAULT VALUES
DELETE FROM t
GO 2000
--Investigate log
DBCC SQLPERF(logspace)
DBCC LOGINFO
--Empty log
BACKUP LOG x TO DISK = 'nul'
--Investigate log
DBCC SQLPERF(logspace)
DBCC LOGINFO
--"Empty" primary log file
DBCC SHRINKFILE(2, EMPTYFILE)
--Investigate log
DBCC SQLPERF(logspace)
DBCC LOGINFO
--Fill up the log some
INSERT INTO t DEFAULT VALUES
DELETE FROM t
GO 2000
--Investigate log
DBCC SQLPERF(logspace)
DBCC LOGINFO
-- Do above several times and see
-- that 2 is still allocated from...
--Can we get rid of file 3?
BACKUP LOG x TO DISK = 'nul'
DBCC SHRINKFILE(3, EMPTYFILE)
--We might need to do above a few times
--until 3 is "clean" - no used VLFs
--Investigate log
DBCC SQLPERF(logspace)
DBCC LOGINFO
ALTER DATABASE x REMOVE FILE x_log2
--Might need to do some stuff to get rid of file physically
CHECKPOINT
BACKUP LOG x TO DISK = 'nul'
GO
SELECT * FROM sys.database_files