Disk with tempdb failed - Urgent

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

Disk with tempdb failed - Urgent

Author

Topic

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 09:05:59

The disk tempdb is stored on failed. Now I cannot even start SQL server. Short term I want to move tempdb to another drive. How do I do this if I can't start sql server to run a script to alter the location of tempdb.

Thanks

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 09:12:27

Read this:
http://www.databasejournal.com/features/mssql/article.php/3379901
Thanks

Sanjeev Shrestha
12/17/1971

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 09:15:40

I cannot access Enterprise manager because sql server isn't started due to tempdb being corrupt. Any other suggestions?

Thanks,
Marcie

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 09:22:55

can you open query analyzer?

Sanjeev Shrestha
12/17/1971

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 09:24:01

No.

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 09:30:29

Then I think you need to delete tempdev and templog files manually and try to restart sql server again. SQL Server automatically build tempdb file as specified on master database. Thanks

Sanjeev Shrestha
12/17/1971

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 09:41:58

How do I find those files if I cannot access that drive? Tempdb is stored on the "B:" drive with the SQL data on the "E:" drive. When I access my computer or the command prompt, the B drive does not show up because it is bad.

Can you tell I'm not a system administrator?

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 09:58:15

Can you open query analyzer in single user mode
with this command sqlservr -m ?

Sanjeev Shrestha
12/17/1971

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 10:16:38

It gives me an error that cannot find the file specified when trying to locate b:\mssql\tempdbf.mdf. My guess(big guess) is that this path is listed somewhere in the master database and it is trying to access this bad drive.

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 10:39:37

I am trying to say if you can run sql server on single user mode by using sqlservr -m from command prompt you can run following command to change the path of tempdb:

use master
go
Alter database tempdb modify file (name = tempdev, filename = 'C:\tempdb.mdf')
go
Alter database tempdb modify file (name = templog, filename = 'C:\templog.ldf')
Go

Sanjeev Shrestha
12/17/1971

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 10:44:06

Running that command at the prompt does not start query analyzer.

sanjnep
Posting Yak Master

191 Posts

Posted - 2006-09-13 : 10:59:06

I think you need to give full path name of sqlservr.exe. For example if it is on C:\SQL\files\sqlservr.exe then you need to run on command prompt:

C:\SQL\files\sqlservr -m

After that try to connect sql server on query analyzer.

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-13 : 11:04:46

I've had to do this in the past, and here is the procedure that was followed:

1. start SQL server with the following parameters (you can set these in the server properties in Enterprise Manager)

-T3609 -f

The T3609 flag starts SQL up without a tempdb and the -f flag allows us to make changes to system tables.

2. Check sysaltfiles for the dbid for the tempdb

SELECT * FROM master..sysaltfiles

the dbid should be 2 btw, but you want to just double check that

3. Update sysaltfiles with the new location (assuming dbid is 2)

USE Master
GO

UPDATE sysaltfiles  SET filename='new tempdb location\tempdb.mdf' WHERE dbid=2 and fileid=1
UPDATE sysaltfiles  SET filename='new tempdb locations\templog.ldf' WHERE dbid=2 and fileid=2

4. Stop the SQL server

5. Clear the startup parameters we used earlier

6. Start SQL server

-ec

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 11:19:06

I can start sqlservr via the command line, however after the server is started I cannot connect to enterprise manager or query analyzer. I get the message when start query analyzer: Cannot open user default database.

Is there a way to alter the sysaltfiles via the command line to change the path of the tempdb?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-13 : 11:24:09

quote:
Originally posted by memarcie

I can start sqlservr via the command line, however after the server is started I cannot connect to enterprise manager or query analyzer. I get the message when start query analyzer: Cannot open user default database.

Is there a way to alter the sysaltfiles via the command line to change the path of the tempdb?

make sure that the sqlagent has not started

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-13 : 11:28:14

quote:
Originally posted by memarcie

Is there a way to alter the sysaltfiles via the command line to change the path of the tempdb?

yeah, there is actually.

you can start sqlserver from the command line like this:

sqlserver -f -c -T3609 -T4022

I googled around and found this blog entry that details the same steps. The author uses two additional startup parameters also: http://dis4ea.blogspot.com/2005/07/tempdb-and-missing-device.html

-ec

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 11:28:36

Just thought of this, I am using remote desktop to connect to the server. Does this matter?

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 11:32:35

I started sqlserver at the command prompt with the paramters listed in the blog.

Then it says:
'Then we updated sysdatabases and sysaltfiles (I know it's hardcore) with the correct file locations.' How do I do this?

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-13 : 11:33:08

quote:
Originally posted by memarcie

Just thought of this, I am using remote desktop to connect to the server. Does this matter?

no that shouldn't matter at all.

-ec

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2006-09-13 : 11:37:11

quote:
Originally posted by memarcie

I started sqlserver at the command prompt with the paramters listed in the blog.

Then it says:
'Then we updated sysdatabases and sysaltfiles (I know it's hardcore) with the correct file locations.' How do I do this?

Start Query analyzer and use the update syntax I showed you a couple messages back. Select * from both of those tables first, so you can see the format of the data.

-ec

memarcie
Yak Posting Veteran

91 Posts

Posted - 2006-09-13 : 11:40:32

quote:
Originally posted by memarcie

I get the message when start query analyzer: Cannot open user default database.

I'm expecting that I should run the update command in query analyzer. I still get the same error message

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources