SQL Logical File names in GUI different than what's in the system databases
-
For some databases, I'm noticing discrepancies between the logical database file names that SQL Server is reporting in the GUI , than what is stored in the sys.master_files table. What causes this, and what would be the best approach to resolving?
Take for instance, when executing:
SELECT mf.name, mf.Physical_Name
FROM Master.sys.master_files mf
INNER JOIN Master.sys.databases db ON db.database_id = mf.database_id
WHERE db.name = 'bwprodapex'
ORDER BY mf.[file_id]
I get these results and the GUI does NOT match what's in the system tables:
The data from the GUI is on the left of the red line, whereas the values returned from the TSQL above is on the right of the red line.
It's not happening with all databases, just some...What causes this? And how best to resolve?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
What does the following return for that database?
SELECT df.name
, df.physical_name
FROM sys.database_files df;
I believe sys.master_files is updated asynchronously and sometimes the update doesn't happen. (I have witnessed this for sizes - not for names)
In this case after the restart of the service the data is up to date. -
It returns the correct names...
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
Any harm in updating the system tables?
In any case, I will change the code we are using to capture the logical files names to pull from the actualy database sys.database_files table insteads.
Many thanks
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
What is the output of this query?
😎SELECT
mf.database_id
,mf.name
,mf.Physical_Name
FROM Master.sys.master_files mf
INNER JOIN Master.sys.databases db ON db.database_id = mf.database_id
--WHERE db.name = 'bwprodapex'
ORDER BY mf.[file_id]Note that sys.database_files is in the scope of the current database, sys.master_files has the scope of the instance, if there are any files on the instance with the same logical name then one would not detect that using sys.database_files.
- MyDoggieJessie - Thursday, May 11, 2017 1:14 PMFor some databases, I'm noticing discrepancies between the logical database file names that SQL Server is reporting in the GUI , than what is stored in the sys.master_files table. What causes this, and what would be the best approach to resolving?
Take for instance, when executing:
SELECT mf.name, mf.Physical_Name
FROM Master.sys.master_files mf
INNER JOIN Master.sys.databases db ON db.database_id = mf.database_id
WHERE db.name = 'bwprodapex'
ORDER BY mf.[file_id]
I get these results and the GUI does NOT match what's in the system tables:
The data from the GUI is on the left of the red line, whereas the values returned from the TSQL above is on the right of the red line.
It's not happening with all databases, just some...What causes this? And how best to resolve?
Is this full recovery model this db
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
-
Perry, yes this is a database in full recovery
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
-
run a tlog backup 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply