8

Made a mistake and mistyped an alter database command for tempdb.

Now the instance won't start. I can't start in single user mode using -m as it states the tempdb could not be found. I tried using:

net start msqsqlserver /f /t3608

But then I can't actually connect to the instance at all using either sqlcmd or ssms.

  • What does the SQL server error log say? – Max Vernon Nov 27 '13 at 20:39
  • What change did you make? – Max Vernon Nov 27 '13 at 20:39
12

Start SQL Server as an application, not a service, in minimal config mode, and only recovering master, from a command prompt:

[path to this instance]\BINN\sqlservr.exe -c -f -T3608

(Add -s InstanceName if this is a named instance.)

Now, in a different command prompt, connect using SQLCMD:

sqlcmd -S InstanceName -E

And issue a correction (triple-check this!!!), then shut the application down:

> ALTER DATABASE tempdb MODIFY FILE ...
> SHUTDOWN WITH NOWAIT;
> GO
  • I did end up getting in via this method though I am unsure initially why it was not letting me connect. In the end I disabled all the services, bounced the box and ran the VM without a network connection and it seemed to work. Thanks for the pointers though. Hope this is useful for someone else in the future too! – Tim Alexander Nov 28 '13 at 8:35
0
  1. Start sql server in single user mode - Net Start MSSQLServer /mSQLCMD /f/t3608

  2. To be in single user mode sqlcmd -S. –E

  3. Run the commands

Alter database tempdb modify file (name = tempdev, filename = 'E:\SQLDATA\DEFAULT\tempdb.mdf')

Alter database tempdb modify file (name = templog, filename = 'E:\SQLDATA\DEFAULT\Templog.ldf')

  1. Stop sql server Net stop mssqlserver

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.