Det finns faktiskt fler bloggar än denna som både är SQL Server relaterade och läsvärda.…
This is another blog post in the Death by series, but has nothing to do with performance. Well, the case that led to this blog post had to do with performance in the first place, as the drive where tempdb resided was super slow with average disk read time aboce 350ms. In this blog post I’ll cover the death of a SQL server instance by moving the tempdb to a new location, like in this case at the root of a mount point.
Last week a client of mine had to move the tempdb to a larger volume, and he sent me a script for verification and everything seemed fine.
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME=‘tempdev’, FILENAME=‘i:Temp_mountpointtempdb.mdf’);
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME=’templog‘, FILENAME=’ i:Temp_mountpointtempdb.ldf’);
GO
A couple of minutes later he ran the script and restarted the SQL server instance running on a 4 node cluster. After a couple of minutes I tried to connect to the instance to make sure everything worked as expected. To my surprise the instance was still not running, and after another couple of minutes I knew something was terribly wrong.
The SQL server instance was failing during the startup and in the eventlog there were a couple of error messages, like:
CREATE FILE encountered operating system error 5(failed to retrieve text for this error. Reason: 15100) while attempting to open or create the physical file ‘I:tempdb_mountpointtempdb.mdf’.
Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
Well there were plenty of space available in the new volume and the SQL service account was granted full access to the directory. The first error message is complaining about “access is denied” and the second about “not enough disk space”, what’s going on here?
I started the instance with minimal configuration by using:
Sqlserver.exe -s name_of_instance -f
Then I logged on as administrator in the instance, created a new file for tempdb data and one for the log. The files appeared in file explorer, so the SQL server account could create new files appearantly!
I created a sub directory beneath the root of the mount point, moved the files to the sub directory and restarted the instance without minimal configuration and everything worked just fine.
This behavior felt strange and I couldn’t came up with a logical explanation so after some searching the Internet, I found this connect item:
It turned out to be a bug in the Windows API when using mount points!
If you want to know more about SQL server, feel free to contact any of our SQL server experts.
/Håkan Winther