SQL 2005 32bit Developer edition, all recent service packs
Windows Server 2003 Standard 64x, all recent updates
Fusion IO drive
We installed a wifty new 80GB SSD card on our development server, ran ALTER TABLE on tempdb (10GB starting size) to move those files to the new drive, stopped SQL Server, and then attempted to restart it… but now SQL will not start, with "A service specific error occured: 1814". Sys.Messages has 1814 as "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." Application event log has "FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\TempDB'. Diagnose and correct the operating system error, and retry the operation." The SQL service is configured to run as a domain account, and that account is local admin on the box.
My guess is that some stealth-bit needs to be configured somewhere that "enables" the new drive for use by SQL Server. Any ideas what this might be?
What I'd really like to do is tell SQL not to build the tempdb files on that drive… but to do this you have to issue an ALTER DATABASE to move the files, but we can't start the service to issue that command. How do you move tempdb files for a SQL Server 2005 (named) instance without actually running the instance? (It's SQL 2005, so I can't just hack the tables in the master db like I could in 2000 or 7.0.)
And no, we do not have backups of our system databases. Or would that have helped here?
Best Answer
READ. The error message.
It says:
You may want to try getting someone to explain you "Access is denied" ;)
Yeah, they are called security. NTFS has pretty detailed security on drives. The user running SQL Server (depends how you did install it) misses the required permissions on the drive to create the files. Nothing "special" about SQL Server - this is an issue with simple, primitive, normal file system permissions.
Well, besides being the local admin (irrelevant, STRONGLY not adviced - seriously strongly) - check whether this account can actually create the file in question (or a similarly named one). If not (file system permissions) that is the issue. My bet.
That said - I would in general question using an SSD for a tempdb file. Seriously waste money in 99% of the cases.