November 25, 2013 at 3:15 pm
I had an issue a few weeks back that we have gotten past, however I still do not know the reason for why it happened and I am hoping someone can help me shed some light on it....
I was in the process of migrating a server from one physical box to another. They are identical drive setups, same OS (2003), same SQL install (2005). Our server team did a 'PlateSpin', which copies the drives from one server to another, as long as the files are not in use. I did not reinstall SQL on the new box, i let the 'PlateSpin' tool copy everything over for me. I then stopped the SQL services on the old server and new server and copied over all of the system database (.mdf & .ldf) files. As soon as i started up the services on the new server, it looked great with one exception. The TempDB was only showing one datafile. When i queried sys.master_files, it was showing me 8 TempDB files. I tried restarting the services, but i still saw the same, only 1 file. I then tried to re-add TempDB files with the same name, but it would error saying they already existed. In turn, i could add new files with different names and they showed up fine. However, on a restart, they would not show up in the properties of the TempDB.
When i queried, sys.master_files again, i now had 16 Temp db files listed in the results. I deleted all but the original single file that was recognized out of the sys.master_files table and re-added the additional 7 files with he original names, restarted the service and then they all appeared.
In all, this is not really an issue I am having, I am just looking to see if anyone can give me a reason as to why this was happening. Hopefully this is understandable...
November 26, 2013 at 4:33 am
I had a similar issue on SQL 2008 R2 just recently when we lost the tempdb disk (well, actually, the drive letter changed). In our config we have 8 tempdb files. I had to start the instance in maintenance mode and change the paths of the tempdb files to the new drive letter but it only allowed me to change the main data and log file. When the instance started, tempdb.sys.database_files showed 2 files but the sys.master_files showed 8 files. I had to drop the additional 6 files (couldn't do anything else with them) and add them again.
I know it's not the same version as you but it is a similar scenario. I think SQL defaulted back to 2 tempdb files somehow when it restarted but the meta data about the files didn't get updated in the system views.
November 27, 2013 at 10:05 am
That actually sounds very similar. There must be a simple answer or explanation... Sounds like we both got around it, I was just curious for the cause.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply