September 25, 2009 at 7:30 am
All,
When the tempdb files filled up, the server tried to open a non-existent temp file on a K: drive which no longer exists. We had a tempdb file on this K: drive months ago but I deleted all those files from the tempdb database. It looks like the sys.master_files for the tempdb on this one server did not get the message the file was deleted.
The tempdb database properties shows only files on drive E.
I tired alter database remove file and got the error file does not exist.
I tried delete from sys.master_files where name = ‘tempdev_2’ and got the error cannot delete from table.
How do I delete the K:\tempdev_2 drive file reference from the sys.master_files table?
September 25, 2009 at 8:01 am
LeeM (9/25/2009)
All,When the tempdb files filled up, the server tried to open a non-existent temp file on a K: drive which no longer exists. We had a tempdb file on this K: drive months ago but I deleted all those files from the tempdb database. It looks like the sys.master_files for the tempdb on this one server did not get the message the file was deleted.
The tempdb database properties shows only files on drive E.
I tired alter database remove file and got the error file does not exist.
I tried delete from sys.master_files where name = ‘tempdev_2’ and got the error cannot delete from table.
How do I delete the K:\tempdev_2 drive file reference from the sys.master_files table?
Well the sledgehammer approach could be to restart the sql services, if the file reference is invalid, when the tempdb database is recreated, it should in theory lose the old reference. you can actually delete from the system tables, but you have to change a setting to allow that and i wouldnt recommend that in this case.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 25, 2009 at 8:07 am
By simply altering the files in tempdb, the changes will not take effect until the services are restarted. Therefore if you deleted the invalid file via the SSMS GUI or an ALTER DATABASE command on tempdb you'll need to restart services. Sounds like you had not done so since you made the change.
- Tim Ford, SQL Server MVPhttp://www.sqlcruise.comhttp://www.thesqlagentman.com http://www.linkedin.com/in/timothyford
September 25, 2009 at 8:30 am
Actually, I had to restart (forced shutdown) the box the server was on because it stopped responding because it could not find drive K:. I will do a clean restart and see if the entry is removed. I do not think that will make a difference (clean vs forced) so if anyone has had this problem please post.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply