sys.database_files is_name_reserved column usage

• cruci8

  SSC Veteran

  Points: 283

  More actions

  December 30, 2012 at 4:34 pm

  #287114

  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