After upgrade to SQL 2012 (from 2008 R2 SP2) sys.master_files shows most log files with wrong name

  • 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

  • Quick question, what are you seeing in sys.database_files?

    😎

    select * from [database name].sys.database_files

  • 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

  • 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

  • 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

  • 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