I had a situation where I had miscalculated the disk I/O load of the TempDB data and log files and therefore needed to alter the RAID configuration and move the tempdb files to the new configuration.
I had originally placed the tempdb data files on a RAID 10 (E:) partition with one mirrored physical disk and the tempdb log file on a separate but identical RAID 10 (G:) partition. The disk queue length for the data files was far too large. The log file was not used nearly as much. I decided (since resources were tight) to merge the E and G partitions into one RAID 10 partition with two mirrored disks.
The steps that were needed to accomplish this were as follows:
- Move tempdb data and log files to spare partition
- Merge E: and G: partitions
- Move tempdb data and log files to newly merged partition
For those that don't have experience with moving database files the procedure is pretty straight forward:
Run the following SQL with the new destination for each data file
Then restart SQL Server service.
My problem ocurred once I wanted to move the the tempdb files to the newly created RAID 10 partition. I kept receiving an error message stating the tempdb log file was unavailable. I had neglected to set the proper permissions on the new folders of the new partition. However, configuring this did not resolve the problem.
I couldn't find anything referring to the problem I had and all the documentation I read stated that restarting the SQL Server service was sufficient.
In the end I restarted the machine and the problem was resolved. Though this option was not specified in any documentation. I can only assume that the database did not "recognize" the partition and/or security changes until after an OS reboot.
