January 20, 2009 at 6:43 am
Despite there are another post similar to my one I decide to create this post because it has more detailed information about this issue.
I have seen some errors in my error log related to the tempdb. Basically I created some temporary files to perform some specific work and after that I deleted the files(in SQL and fisicaly in my HD), but after a SQL Server restart I found some errors like this below in ERROR LOG.
LogSQL Server (Current - 20/1/2009 02:37:00)
Sourcespid9s
Error: 5149, Severity: 16, State: 1.
Message
CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'N:\TEMP_DB\TEMP_DATA01.ndf'.
I Checked the tempdb from Management studio and it´s show only two archives like as I run
sp_helpdb tempdb:
tempdev1D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdfPRIMARY9216000 KBUnlimited0 KBdata only
templog2D:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldfNULL10240000 KBUnlimited0 KBlog only
I dug a little bit deeper and I found some rows in sysaltfiles that reference to the deleted files in tempdb.
Look at
select * from sys.sysaltfiles where [dbid] = 2
111152000-10202tempdevD:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\tempdb.mdf
201280000-106602templogD:\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\templog.ldf
316400001920000128202TEMP_DATA01N:\TEMP_DB\TEMP_DATA01.ndf
413840000-10202temptempZ:\TEMPDB\temptemp.ndf
511920000-10202Temp_tempY:\Temp_temp.ndf
612560000-164000202TEMPDATA_2Z:\TEMPDB\TEMPDATA_2.ndf
712560000-1128202Temp2Z:\Temp2.ndf
811920000-1128202Temp3O:\Temp3.ndf
911920000-1128202temp22O:\temp22.ndf
1011024000-1128202tempzZ:\tempz.ndf
!This command shows several files that does not exist!
For my surprise I found many orphaned files in sysaltfiles, looks like these files are generating the errors in error log. I tried to manual delete these rows in sysaltfiles but It´s not allowed manual deletes in catalogs, and restart the server in DAC mode its not a good option.
Furthermore none of files in sysaltfiles exist in servers disk, so it´s not exactly orphaned files, may be they are ghost files.
Have anybody already seen something like this?
Any suggestions?
Thanks
Pin
January 20, 2009 at 8:03 am
use this code and check if orphan records goes off
alter database tempdb
REMOVE file locigal_file_name
February 9, 2009 at 1:03 pm
nilmov (1/20/2009)
use this code and check if orphan records goes offalter database tempdb
REMOVE file locigal_file_name
Hi Nilmov...
Sorry to take so long to anwser...
It worked perfctly!!!
Thank you so much!
Eduardo
February 9, 2009 at 1:51 pm
Looks like someone ran the script to move tempdb from drives and never rebooted the service, which is required!
* Noel
February 10, 2009 at 1:34 pm
noeld (2/9/2009)
Looks like someone ran the script to move tempdb from drives and never rebooted the service, which is required!
I did the first SYSALTFILES select posted after a service restart.
Actually the service was rebooted several times, but it did not work, the rows in SYSALTFILES still there. Only the alter database statement worked, furthermore the alter database command looks more correct than a service restart...
Thanks All
Eduardo Pin
February 10, 2009 at 2:07 pm
eduardo.pin (2/10/2009)
noeld (2/9/2009)
Looks like someone ran the script to move tempdb from drives and never rebooted the service, which is required!I did the first SYSALTFILES select posted after a service restart.
Actually the service was rebooted several times, but it did not work, the rows in SYSALTFILES still there. Only the alter database statement worked, furthermore the alter database command looks more correct than a service restart...
Thanks All
Eduardo Pin
Right, "select" does not changes anything.
I am saying that someone did the ALTER to include those bogus extra files.
* Noel
February 10, 2009 at 8:26 pm
No Noel..
Take this scenario...
I have an instance with multiple log files and they are in the drive say F
now i moved the instance to a different drive..
I change the mdf and ldf path and database will me up and as well as the instance..
however, the other ndfs will be non existent ones and they will be orphan...
Only option is removing them using alter file command
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply