July 27, 2011 at 11:08 am
I have stale entries exist when executing the query below:
SELECT name, physical_name
FROM sys.master_files
WHERE database_id = DB_ID('tempdb');
Physical name of multiple ndf files for TempDB are displayed but do not exist in the path provided. How do I alter sys_master_files to clean up these entries?
July 27, 2011 at 11:22 am
You can't edit the system tables (and you shouldn't even consider it)
What do you mean by 'stale'? Has TempDB been changed since the last server restart?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2011 at 11:38 am
When I execute the above query, I get the results of the physical locations of the TempDB Files (below). At some point I believe we used secondary files, and soon after moved TempDB to Windows Mount points that were mapped to LUNS on the SAN but the entries for .ndf at a location that it not physically there is still referenced in sys_master_files. If I right click the DB in SSMS and look at the physical files, it only shows the 2 files (mdf/ldf)
How do I attempt to clean this up. Is there an easy way or if I cannot easily make modifications to system databases, would a call to MS support suffice at this point.
tempdevE:\mount\CSQL09A\SystemDB\MSSQL.1\MSSQL\Data\tempdb.mdf
templogE:\mount\CSQL09A\SystemLogs\TempDB\templog.ldf
tempdev2E:\Data\tempdb_data2.ndf
tempdev3E:\Data\tempdb_data3.ndf
tempdev4E:\Data\tempdb_data4.ndf
tempdev5E:\Data\tempdb_data5.ndf
tempdev6E:\Data\tempdb_data6.ndf
tempdev7E:\Data\tempdb_data7.ndf
tempdev8E:\Data\tempdb_data8.ndf
tempdev9E:\Data\tempdb_data9.ndf
tempdev10E:\Data\tempdb_data10.ndf
July 27, 2011 at 11:52 am
Gavin Heer (7/27/2011)
When I execute the above query, I get the results of the physical locations of the TempDB Files (below). At some point I believe we used secondary files, and soon after moved TempDB to Windows Mount points that were mapped to LUNS on the SAN but the entries for .ndf at a location that it not physically there is still referenced in sys_master_files.
Has SQL been restarted since then?
If so, try ALTER DATABASe DROP FILE and then restart SQL.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2011 at 12:15 pm
Perfect, that seems to work although doesn't seem like I need to restart SQL. I just issued the TSQL command (ALTER DATABASE tempdb REMOVE FILE tempdev3) and apply up to tempdev10 and re-issue sys.master_files query and it's now removed.....Thanks guys. This forum is the best.
Regards
July 27, 2011 at 12:30 pm
Glad to hear
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 27, 2011 at 12:39 pm
GilaMonster (7/27/2011)
Glad to hear
Is there a way to close this thread now that it's been answered. I cant find these icons that the FAQ's mention?
July 27, 2011 at 1:59 pm
No, and don't worry about it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply