October 15, 2008 at 1:30 pm
I'm trying to create a new tempdb. My server won't restart
and this syntax is a little crazy
can someone let me know what to fix.
Thanks
-WM
Create database tempdb
ON
(name = 'tempdev',
filename = 'E:\PRogram Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf'),
FILEGROUP default
(name = 'tempdev',
filename = 'E:\PRogram Files\Microsoft SQL Server\MSSQL\Data\tempdb.mdf'),
FOR ATTACH_REBUILD_LOG
October 15, 2008 at 1:34 pm
THe error I was getting is
The FOR ATTACH option requires that at least the primary file be specified.
thanks again
-WM
October 15, 2008 at 2:18 pm
Could you perhaps explain a bit more what you're trying to do and why?
TempDB can't be restored or attached. For that matter, it can't be backed up or detached. It's recreated every time SQL starts.
If your server won't start, can you find the error log (it's a text file called 'Errorlog', you can open with any text editor) and post any errors you see in it here so we can help you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 2:28 pm
sorry about that I'm alittle stressed be working on this all day
It was supposed to take 5 minutes.
But I was moving database from one drive to another. Everything was working out great until I got to tempDB
I think I acidentially named both the log and data with the MDF extention.
When I query sysaltfiles tempdev and templog have the same filename. I tried to update the file but was unable to execute ad hoc queries agains the sys catalogs.
Tried to run exec sp_configure 'allow updates', 1;
BUt I got
Server: Msg 913, Level 16, State 8, Procedure sp_configure, Line 20
Could not find database ID 2. Database may not be activated yet or may be in transition.
I know db ID 2 is temp db
so that's where I',m at
October 15, 2008 at 2:32 pm
You can't update the system tables on 2005, and you shouldn't try anyway. It's the quickest way to really stuff things up.
Can you post any errors from the error log please?
p.s. The way to move tempDB is to to an ALTER DATABASE statement and specify WITH MOVE. Tehn you just restart SQL. You don't actually have to touch the files themselves. SQL will recreate the DB when it starts.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 2:50 pm
can you give me a little help on the syntax
October 15, 2008 at 2:55 pm
What syntax?
1) Is the server running?
2) Are there any errors in the error log? (if you've not sure, post the entire thing)
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 2:58 pm
error log
08-10-15 16:44:06.22 server Copyright (C) 1988-2002 Microsoft Corporation.
2008-10-15 16:44:06.22 server All rights reserved.
2008-10-15 16:44:06.22 server Server Process ID is 1248.
2008-10-15 16:44:06.22 server Logging SQL Server messages in file 'd:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2008-10-15 16:44:06.22 server SQL Server is starting at priority class 'normal'(4 CPUs detected).
2008-10-15 16:44:06.32 server SQL Server configured for thread mode processing.
2008-10-15 16:44:06.33 server Using dynamic lock allocation. [2500] Lock Blocks, [5000] Lock Owner Blocks.
2008-10-15 16:44:06.39 server Attempting to initialize Distributed Transaction Coordinator.
2008-10-15 16:44:07.42 spid3 Starting up database 'master'.
2008-10-15 16:44:07.53 server Using 'SSNETLIB.DLL' version '8.0.2039'.
2008-10-15 16:44:07.53 spid5 Starting up database 'model'.
2008-10-15 16:44:07.53 spid3 Server name is 'LMCMPTEST01'.
2008-10-15 16:44:07.53 spid8 Starting up database 'msdb'.
2008-10-15 16:44:07.53 spid9 Starting up database 'pubs'.
2008-10-15 16:44:07.53 spid10 Starting up database 'Northwind'.
2008-10-15 16:44:07.53 spid11 Starting up database 'BMMS'.
2008-10-15 16:44:07.53 spid9 udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device d:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf.
2008-10-15 16:44:07.53 spid12 Starting up database 'FDB'.
2008-10-15 16:44:07.55 spid10 udopen: Operating system error 2(The system cannot find the file specified.) during the creation/opening of physical device d:\Program Files\Microsoft SQL Server\MSSQL\dataorthwnd.mdf.
2008-10-15 16:44:07.55 spid9 FCB::Open failed: Could not open device d:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf for virtual device number (VDN) 1.
2008-10-15 16:44:07.55 spid10 FCB::Open failed: Could not open device d:\Program Files\Microsoft SQL Server\MSSQL\dataorthwnd.mdf for virtual device number (VDN) 1.
2008-10-15 16:44:07.55 spid10 Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\dataorthwnd.mdf' may be incorrect.
2008-10-15 16:44:07.55 spid9 Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\pubs.mdf' may be incorrect.
2008-10-15 16:44:07.55 spid9 Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\data\pubs_log.ldf' may be incorrect.
2008-10-15 16:44:07.55 spid10 Device activation error. The physical file name 'd:\Program Files\Microsoft SQL Server\MSSQL\dataorthwnd.ldf' may be incorrect.
2008-10-15 16:44:07.55 server SQL server listening on 10.200.2.107: 1433.
2008-10-15 16:44:07.55 server SQL server listening on 127.0.0.1: 1433.
2008-10-15 16:44:07.60 spid5 Clearing tempdb database.
2008-10-15 16:44:07.69 server SQL server listening on TCP, Shared Memory, Named Pipes, Rpc.
2008-10-15 16:44:07.69 server SQL Server is ready for client connections
2008-10-15 16:44:15.19 spid5 E:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf is not a primary database file.
2008-10-15 16:44:15.19 spid5 CREATE DATABASE failed. Some file names listed could not be created. Check previous errors.
2008-10-15 16:44:15.19 spid5 WARNING: problem activating all tempdb files. See previous errors. Restart server with -f to correct the situation.
October 15, 2008 at 3:01 pm
the syntax for the alter database statement
October 15, 2008 at 3:07 pm
What does the following return?
select * from sys.master_files where database_id = 2
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 3:08 pm
This is a 2000 instance
sorry
-WM
October 15, 2008 at 3:11 pm
E:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf
E:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf
These are the file names from sysaltfiles
October 15, 2008 at 3:12 pm
Ok, so what does the 2000 equivalent return?
select * from master..sysaltfiles where dbid = 2
Please in future post SQL 2000 questions in the 2000 forums. If they're in the 2005 forums, people are going to assume it's 2005 and use 2005-specific features in their answers.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 3:15 pm
And what are the names of the tempDB data and log files in the E:\Program Files\Microsoft SQL Server\MSSQL\data\ directory?
Can you shut the service down and bring SQL up from the command line, using the -f switch?
sqlservr.exe -f
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 15, 2008 at 3:19 pm
1125272-110104857802tempdev E:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf
2064-110104864202templog E:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf
The files names are the same how can I change or create a new file for the log with an ldf extension
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply