December 30, 2012 at 4:34 pm
Hi,
while reading the excellent SQL Server 2008 Internals book I came across this column description in sys.database_files
is_name_reserved 1 = Dropped file name (name or physical_name) is reusable only after the
next log backup. When files are dropped from a database, the logical names
stay in a reserved state until the next log backup. This column is relevant
only under the full recovery model and the bulk-logged recovery model.
So I tried this out on a SQL2008 full recovery database with a new file, which I drop
alter database MTest
add file
(name= 'MTest2', filename = 'G:\SYSDATA\MSSQL\MSSQL$SERVERNAME\DATA\MTest2.ndf', size = 10MB)
select * from sys.database_files
alter database MTest
remove file MTest2
select * from sys.database_files
At no point did I take a t-log backup, an at no point does the column is_name_reserved get set to 1. I can also add the same filename back immediately with
alter database MTest
add file
(name= 'MTest2', filename = 'G:\SYSDATA\MSSQL\MSSQL$SERVERNAME\DATA\MTest2.ndf', size = 10MB)
with no errors. What am I misunderstanding?
Even given this, I also wonder why the stated requirement of a log backup before name reuse would be required? Any other structural operation I can think of doesn't prevent you re-using the same property until you've backed up the transaction log.
Happy new year
Matt
Viewing 0 posts
You must be logged in to reply to this topic. Login to reply