January 14, 2011 at 8:55 am
I know what I did.. just not sure how to fix it.
I issued this command to move TEMPDB
USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'H:\MSSQL\Servername\TEMPDB');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'H:\MSSQL\Servername\TEMPDB');
GOAs you can see.. I neglected to define the FILENAMES!!! DOH!!!!
now my instance will not start.. getting these errors:
CREATE FILE encountered operating system error 5(Access is denied.) while attempting to open or create the physical file 'H:\MSSQL\Servername\TEMPDB'.
Error: 17207, Severity: 16, State: 1.
FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'H:\MSSQL\Servername\TEMPDB'. Diagnose and correct the operating system error, and retry the operation.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file H:\MSSQL\Servername\TEMPDB for file number 1. OS error: 5(Access is denied.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "H:\MSSQL\Servername\TEMPDB". Operating system error 5: "5(Access is denied.)".
Error: 1802, Severity: 16, State: 4.
CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
Error: 5123, Severity: 16, State: 1.
Does anyone know how I can fix this?
January 14, 2011 at 9:05 am
Try starting the instance in single user mode, then reissue ALTER DATABASE commands.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2011 at 9:10 am
Start SQL from the command line with the flags -f -T3608
sqlservr.exe -f -T3608
That will start SQL in restricted mode, won't recover any databases. You'll be able to get one connection, you can then use that to reissue the alter database commands correctly for TempDB, then kill the instance running from the command line (ctrl-C) and restart normally
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
January 14, 2011 at 9:54 am
Thanks Guys!!
I did start the instance using this command from the command prompt
E:\Microsoft SQL Server\MSSQL.3\MSSQL\Binn>sqlservr.exe -f -T3608 Servername
2011-01-14 11:42:41.55 Server A self-generated certificate was successfully
loaded for encryption.
2011-01-14 11:42:41.55 Server Server is listening on [ 'any' <ipv6> 1433].
2011-01-14 11:42:41.55 Server Server is listening on [ 'any' <ipv4> 1433].
2011-01-14 11:42:41.56 Server Server local connection provider is ready to
accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].
2011-01-14 11:42:41.56 Server Server local connection provider is ready to
accept connection on [ \\.\pipe\sql\query ].
2011-01-14 11:42:41.56 Server Server is listening on [ ::1 <ipv6> 1434].
2011-01-14 11:42:41.56 Server Server is listening on [ 127.0.0.1 <ipv4> 143
4].
2011-01-14 11:42:41.56 Server Dedicated admin connection support was establ
ished for listening locally on port 1434.
2011-01-14 11:42:41.58 Server The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x20
98, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
2011-01-14 11:42:41.58 Server SQL Server is now ready for client connection
s. This is an informational message; no user action is required.
2011-01-14 11:42:41.59 spid5s Recovery is complete. This is an informational message only. No user action is required.
When I try and get a connection from Query Analyzer - no go. WHat did I do wrong??
January 14, 2011 at 10:42 am
sqlservr.exe -f means start mimimum configuration .
Shouldn't you start it without the -f parameter and the trace flag after you performed the procedure Gail pointed to?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2011 at 10:48 am
I tried Gail's suggestion:
Start SQL from the command line with the flags -f -T3608
sqlservr.exe -f -T3608
That will start SQL in restricted mode, won't recover any databases. You'll be able to get one connection, you can then use that to reissue the alter database commands correctly for TempDB, then kill the instance running from the command line (ctrl-C) and restart normally
but I am unable to get the 'one connection' that she talked about in order to reissue my alter database commands...
January 14, 2011 at 11:01 am
Are you connecting with SSMS? You might be using that one connection in the Object Explorer
Use SQLCMD for your one connection from a different command like window.
January 14, 2011 at 11:39 am
Not really sure what to do at this point..
C:\Users\>sqlcmd -s Servername
HResult 0x2, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [2].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
January 14, 2011 at 12:24 pm
Try using the -A for the DAC connection. (It is case sensitive.)
C:\Users\isjxr18>sqlcmd -s kocsql05\sharepoint -A
You could also try using osql util, that is what I usually end up using.
Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton
January 14, 2011 at 12:26 pm
There is one and only one connection available in that state. You have to be certain that you get it.
Make sure SQL Agent is stopped, sharepoint is stopped, anything that may be trying to connect to that instance is stopped. Remote into the server (if you aren't already) and use SQLCMD to get a connection.
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
January 14, 2011 at 12:36 pm
I am able to connect to the instance using SQLCMD and re-issue my commands.. and when I restart the instance.. it actually CREATES the new tempdb files in the new location.. but then I get this:
January 14, 2011 at 12:37 pm
IMO you did forget to grant the needed auth for SQLServer.
Whenever I need to move db files to a new disk or folder I use
this in command prompt
rem /T Creates directory structure, but does not copy files. Does not include empty directories or subdirectories.
rem /T /E includes empty directories and subdirectories.
rem /O Copies file ownership and ACL information
xcopy e:\MSSQL.1\MSSQL F:\MSSQL.1\MSSQL /E /T /O
It copies including the access control list !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2011 at 12:51 pm
What were the ALTER DATABASE statments that you issued?
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
January 14, 2011 at 1:14 pm
from the CMD window, I issued:
SQLCMD - s Server\sharepoint
USE master
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = tempdev, FILENAME = 'H:\MSSQL\Server\Sharepoint\TEMPDB')
GO
ALTER DATABASE tempdb MODIFY FILE (NAME = templog, FILENAME = 'H:\MSSQL\Server\Sharepoint\TEMPDB')
GO
January 14, 2011 at 1:17 pm
You're still missing the file names, same as you were when you first ran it and broke SQL.
Run the correct alter database, with the full filenames specified, then restart SQL.
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
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply