Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

sys.database_files is_name_reserved column usage Expand / Collapse
Posted Sunday, December 30, 2012 4:34 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, September 10, 2015 4:47 PM
Points: 91, Visits: 557

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

Post #1401274
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse