2

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?

Philip Kelley
  • 253
  • 2
  • 9

6 Answers6

1

READ. The error message.

It says:

Application event log has "FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'F:\TempDB'.

You may want to try getting someone to explain you "Access is denied" ;)

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?

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.

The SQL service is configured to run as a domain account, and that account is local admin on the box

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
1

It appears that the problem occurred when the admin (not myself, honest!) entered the wrong paths for the files, though we'll never know for sure. He managed to fix it somehow by starting SQL with the -c and -f switches, and re-altering the files to proper locations.

(Sorry for taking up your time. We get recurring if infrequent problems from the same source around here. You'd think by now I'd realize that basic issues are not basic to some folks.)

Philip Kelley
  • 253
  • 2
  • 9
  • Thanks for this, this helped me out. I did the same thing. In the alter database statement I accidentally left out the file name. Easy mistake but then you cant start the db to fix the problem. The -c -f switched allow you to start it and run the correct command. – thecaptain0220 Oct 08 '12 at 15:47
0

sounds like you did hose your system dbs. ALWAYS backup system databases......they can come in handy later for times like this.

http://support.microsoft.com/kb/943635

try that to get sql running again - then just do an attach with the db's.

then of course, make sure that the OS recognizes the new drive, and that it is all formatted and ready to go - and make sure that the ID you are running sql under can access that drive.

hope that helps, good luck!

MattrixHax
  • 141
  • 1
  • 3
0

Regarding backups of system databases: master, model, msdb yes. Tempdb is rebuilt everytime the database engine service is restarted so I would skip tempdb.

jl.
  • 1,076
  • 8
  • 10
0

This may be (its a big may be =) related to a known bug that when you move tempdb using ALTER DATABASE, SQL server looks for the specified free space(in your case 10GB) in the original drive not in your new SSD drive. So if there is no 10GB in the original drive it will fail. What you should do is move the database first to your new SSD drive using small size(like 1MB) then re-size it again to the size you want(10GB)

Brent Ozar explained it here better.

DaniSQL
  • 1,097
  • 7
  • 12
0

unsure if you have already resolved this, but here goes... looks like SQL is searching for the folder "F:\TempDB" to create the data files under when it restarts. Is F:\ drive the new Fusion-IO drive? If yes, have you already created a folder "TEMPDB" on the drive with the appropriate permissions for SQL to be able to access it?

SQL will directly try to access the folder and will not create the folder in case it doesn't exist... therein probably lies your problem! Hope this helps.

Regards Chirag

Chirag
  • 155
  • 8