johnpalmer: (Default)
If, for some reason, your SQL Server is not starting, due to an inability to create TempDB on a particular disk, here's what you should do.

Start SQL Server in minimal mode.

That's (from a command prompt):

sqlservr.exe -f

(Note: this is "minimal configuration mode", which includes putting the server in single user mode. -m is the switch for ordinary single user mode. But -f only allows one user to connect, so make sure that no one else even contemplates attaching to it. Use of the LART or, for hard cases, a cutlass, can help here.)

Then, open a new command window, connect via SQLCMD.EXE and key in

ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPDEV,filename='(a path and filename)')
GO

ALTER DATABASE TEMPDB MODIFY FILE(NAME=TEMPLOG,filename='(a path and filename)')
GO
-- the default filenames are tempdb.mdf and templog.ldf. Use them, or make sure everyone knows
-- you didn't, and knows what you used. It's easier just to use the defaults.

This will set SQL to create TEMPDB on the specified disk.

Cycle SQL. Everything should be fine... or, at least, TempDB should now be set to be created in a location where it can be created.

Don't do anything else.

(Unless, of course, you know of another method of setting the tempdb location safely. In which case, sure, you can do that, but you probably know darn well why I'm saying "don't do anything else".)
johnpalmer: (Default)
So, yesterday, I was at work, and got a call in about a restore of a database causing the SQL tools to stop working. This is, of course, impossible. So, I dug in a bit. We ran some tests, and found the restored database was responding, sometimes, but not always, and often sluggishly.

Then I asked him to send me the logs.

Ordinarily, when you start up SQL, the log shows a line for each database: "Starting database (x)". Now it showed four lines, "starting database (y)", for the database that was causing the problems.

I'd never seen this, but it made sense that it would be a database with the autoclose option set.

I checked with a senior engineer. Yes, that's exactly what that means, and it will cause serious performance issues.

And that's when I realized that it had to be the totality of our problems. The repeated opening/closing would account for the observed symptoms perfectly. The tools would respond funny because they were reading that database, requiring a full open of the database, which took time (because it was fairly large).

Even better, telling the customer to change the setting fixed the problem instantly.

I know I'm going to run into icky problems that I'll hate, but for now, I'm starting to like this job.
Page generated Sep. 26th, 2017 03:50 am
Powered by Dreamwidth Studios