January 24, 2013 at 8:40 am
Before I do anything I'm going to regret, I wanted to get some advice. I've got an orphaned transaction log file on a production database that needs to be fixed. In fact, sys.sysfiles1 thinks the file exists twice in 2 different locations.
Logging in as DAC, I got the following information:
1> SELECT * FROM sys.sysaltfiles WHERE DBID = 26
2> GO
fileid groupid size maxsize growth status perf dbid
name
filename
------ ------- ----------- ----------- ----------- ----------- ----------- -----
- ------------------------------------------------------------------------------
-------------------------------------------------- -----------------------------
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
1 1 6060760 -1 12800 2 0 2
6 MyDB_Data E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Data.MDF
2 0 12992 -1 1280 66 0 2
6 MyDB_Log E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Log.LDF
4 0 10556648 268435456 10 1048642 0 2
6 MyDB_Log2 E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Log2.LDF
1> SELECT * FROM sys.sysfiles1
2> GO
status fileid name
filename
----------- ------ -------------------------------------------------------------
------------------------------------------------------------------- ------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------
2 1 MyDB_Data E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Data.MDF
66 2 MyDB_Log E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Log.LDF
1048582 3 MyDB_Log2 E:\SQL_Data\MyDB_Log2.ndf
1048642 4 MyDB_Log2 E:\MSSQL9Data\DU01\MSSQL.1\MSSQL\Data\MyDB_Log2.LDF
I've bolded the "bad" file. Now I need to fix it. But the instructions I found here on MS Connect are written for bad data files, not bad log files, as evidenced by the attempt to add a file to the new filegroup (It uses ADD FILE not ADD LOG FILE). So I'm wondering if these instructions are even relevant to what I'm trying to do.
I plan to backup the database, back up the log, then try to add / remove the bad file per the instructions on the link (or as close as I can manage). Does anyone see any problems with this?
January 24, 2013 at 8:49 am
DOH. I just realized the bad file thinks it's a secondary data file. Maybe the instructions I found will work.
January 24, 2013 at 9:27 am
I would use the sys.database_files catalog and compare that against sys.master_files
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 24, 2013 at 10:27 am
Perry Whittle (1/24/2013)
I would use the sys.database_files catalog and compare that against sys.master_files
That doesn't show me anything different from what I expect to see. I just see the data file, the first log file and the second long file. No orphaned file, like sys.sysfiles1 shows.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply