May 17, 2014 at 1:53 pm
Curious...
I have yet to run the update usage, but most of the names in sys.master_files for LOGS show a wrong name, but the physical name is correct.
This is SO odd... it seems there are a couple of names that have been used for most of them as you can see (only showing some), I truncated a seperate column for some.. what gives? Why would this update the name column for many databases using a specific database? Why wouldnt they all be wacky? I can easily update the values to be accurate, but shouldnt need to... Does update usage fix this, or another step so I dont have to try DAC to update these values?\
select [name], type_desc,physical_name,left(RIGHT(Physical_name, 19),15) from sys.master_files
where type_desc = 'LOG'
order by [name]
EDDS_logLOGG:\Logs\EDDS_1.ldfG:\Logs\EDDS_1.
EDDS1014823_logLOGG:\Logs\Edds1014823.ldfogs\Edds1014823
EDDS1014823_logLOGG:\Logs\EDDS1020004_log.ldfEDDS1020004_log
EDDS1014823_logLOGG:\Logs\EDDS1044738_log.ldfEDDS1044738_log
EDDS1014995_logLOGG:\Logs\EDDS1014995_1.ldfs\EDDS1014995_1
EDDS1015024_logLOGG:\Logs\EDDS1023929_log.ldfEDDS1023929_log
EDDS1015032_logLOGG:\Logs\EDDS1022039_log.ldfEDDS1022039_log
EDDS1015161_logLOGG:\logs\5161_1.ldfG:\logs\5161_1.
EDDS1015198_logLOGG:\logs\5198_1.ldfG:\logs\5198_1.
EDDS1016344_logLOGG:\Logs\EDDS1016540_log.ldfEDDS1016540_log
EDDS1016540_logLOGG:\Logs\EDDS1016548_log.ldfEDDS1016548_log
EDDS1016540_logLOGG:\Logs\EDDS1016555_log.ldfEDDS1016555_log
EDDS1016540_logLOGG:\Logs\EDDS1016721_log.ldfEDDS1016721_log
EDDS1016540_logLOGG:\Logs\EDDS1020769_log.ldfEDDS1020769_log
EDDS1016540_logLOGG:\Logs\EDDS1017209_log.ldfEDDS1017209_log
EDDS1016540_logLOGG:\Logs\EDDS1022289_log.ldfEDDS1022289_log
May 18, 2014 at 1:04 am
Quick question, what are you seeing in sys.database_files?
😎
select * from [database name].sys.database_files
June 3, 2014 at 2:55 pm
I have a similar problem when attaching databases from SQL 2008R2 to SQL 2012.
SELECT name, physical_name
FROM sys.master_files
shows incorrect logical names for the log files, but correct physical path/filenames.
USE myDb
select name, physical_name from sys.database_files
shows the correct logical name for all files (as well as physical path/filenames)
Can I simply UPDATE sys.master_files?
Thanks,
Scott
June 4, 2014 at 12:35 am
No you cannot. There is a hack if you Google it, but it won't let you. I have not fixed and have had no issues though that I believe are related. If you find a solution post it here. I only researched one evening
June 4, 2014 at 6:47 am
ok. I took a look at the T-SQL to attach and didn't see an option to specify the logical names, so I simply attached without the log file, allowing SQL Server to create a new one with an appropriate logical name. The db is in Simple recovery model, and only used for reporting, so nothing was lost dropping the old log file.
I saw a CU for 2008R2 to address this - wonder if some code has been left orphaned on a branch...
Scott
June 5, 2014 at 1:43 am
scotteb (6/4/2014)
ok. I took a look at the T-SQL to attach and didn't see an option to specify the logical names, so I simply attached without the log file, allowing SQL Server to create a new one with an appropriate logical name. The db is in Simple recovery model, and only used for reporting, so nothing was lost dropping the old log file.I saw a CU for 2008R2 to address this - wonder if some code has been left orphaned on a branch...
Scott
Check out ALTER DATABASE File and Filegroup Options , the MODIFY FILE allows changing of the logical file name.
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply