#CertifyMe

http://borntolearn.mslearn.net/certchallenge/

SQL Server will not start. There is not enough disk space for tempdb.

With a large tempdb data file located on C:\ the service would not start. The following message was in the log file.

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.

Unfortunately there was no way to free up the required space and the only way to start SQL would be to move the tempdb file location. Catch 22, we need to start SQL to move the files.

The way to accomplish this is to start SQL using trace flag 3608. This trace flag tells SQL not to start up any databases except master. From a command prompt:

net start MSSQLSERVER /f /t3608

                /f: Minimal configuration mode

                /t: Start SQL Server with the specified trace flag

Once SQL Server is running you can connect with SQLCMD using a dedicated admin connection (DAC) from the command promt.

sqlcmd -A -S MyServer -q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'tempdev', FILENAME = 'H:\MSSQL\DATA\tempdb.mdf', SIZE = 2048MB, FILEGROWTH = 2048MB);"

sqlcmd -A -S MyServer -q"ALTER DATABASE tempdb MODIFY FILE (NAME = 'templog', FILENAME = 'G:\MSSQL\DATA\templog.ldf', SIZE = 1024MB, FILEGROWTH = 1024MB);"

                -A: Use DAC

                -S: Server Name

                -q: Command

Now stop and restart SQL Server and Bob’s your aunty.