November 3, 2011 at 5:54 pm
Hi all,
So im moving our 2005 data and with all my might I have managed to change the file location of tempdb, model and msdb to z:/ instead of z:\
Looked into the reg and cant see a any reference. Any ideas :(????
November 3, 2011 at 6:09 pm
Try starting in minimal config (SQL Server), and then altering the paths to the correct paths.
sqlservr.exe -f
Here's a similar thread
http://www.sqlservercentral.com/Forums/Topic545301-146-1.aspx
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 6:26 pm
Hi SQLRNNR,
Thanks for the quick reply!
Id tried that command already. im looking at a long list of white text saying that each of those db have
error 17207, severity 16, state 1
open failed: could not open file y:/
diagnose and correct the operating system error. and retry the operation.
then i also end with
could not create tempdb, you may not have enough disk space available.free additional disk....
config manager still shows it as down. so not sure if im in or not 🙁
November 3, 2011 at 7:10 pm
Try this:
(a) From command prompt type sqlservr -c -f
(b) then try
C:\>SQLCMD -q"Alter database tempdb modify file (name = tempdev, filename = 'Y:\SQL\tempdb.mdf')"
GO
C:\>SQLCMD -q"Alter database tempdb modify file (name = templog, filename = 'Y:\SQL\templog.ldf')"
(c) Restart ...
or
(a) Go to Services Pane in wondows
(b) Go to properties of MSSQLSERVER service
(c) Start Parameter Text box enter -c -f
(d) Start the service
(e) Service is started successfuly
(f) go to command prompt
C:\>SQLCMD -q"Alter database tempdb modify file (name = tempdev, filename = 'Y:\SQL\tempdb.mdf')"
GO
C:\>SQLCMD -q"Alter database tempdb modify file (name = templog, filename = 'Y:\SQL\templog.ldf')"
(g) Repeat steps a-c to remove those params, stop and Restart the service...
Adding the c param may be the trick this time around
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 7:29 pm
When i run
SQLCMD -q"Alter database tempdb modify file (name = tempdev, filename = 'Y:\SQL\tempdb.mdf')"
about 10 seconds later i get an error saying:
could not open a connection to sql server [2]
an error has occurred while establishing a connection to the server. when connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server does not allow remote connections.
Im not sure where this setting is but if i try and open the surface area config i get the expected i guess total failure to load.
Ive also tried going config manager..
Properties of SQL Server Service
Advanced
Startup Parameters :
and added -c -T3508 like the other post suggested. BUt it still failed out with the same error.
Thanks for any advice suggestions! they are most welcome..
November 3, 2011 at 7:38 pm
Is that new path for tempdb valid?
Next option might be to try the DAC - dedicated administrator connection
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 7:55 pm
yeah,
to start i didn't copy it over (Didn't think i had to be honest with tempdb :X) Ive copied it over now (mdf and ldf) same result.. /cry
November 3, 2011 at 8:15 pm
Are the paths for the master database correct in the registry? I don't know where paths are in sql 2005, but my 2008R2 installation has them in HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQLServer\Parameters (also the base path for the error log file(s)) and I suspect that changing 10_50 to 90 may give the sql2005 key. If those are wrong, fixing them may help.
Tom
November 3, 2011 at 8:17 pm
Looking at SQL Error logs i get the following if it sheds light to you wise poeple out there!
Am i running the traceflag right? it seems like its pointing it to the log file not the database!?
2011-11-04 02:10:21.41 Server (c) 2005 Microsoft Corporation.
2011-11-04 02:10:21.43 Server All rights reserved.
2011-11-04 02:10:21.43 Server Server process ID is 5588.
2011-11-04 02:10:21.43 Server Authentication mode is MIXED.
2011-11-04 02:10:21.43 Server Logging SQL Server messages in file 'Z:\LOG\ERRORLOG'.
2011-11-04 02:10:21.43 Server This instance of SQL Server last reported using a process ID of 3876 at 04/11/2011 02:06:57 (local) 04/11/2011 02:06:57 (UTC). This is an informational message only; no user action is required.
2011-11-04 02:10:21.43 Server Registry startup parameters:
2011-11-04 02:10:21.43 Server -d Z:\DATA\master.mdf
2011-11-04 02:10:21.43 Server -e Z:\LOG\ERRORLOG
2011-11-04 02:10:21.43 Server -l Y:\DATA\mastlog.ldf -c -T3607
2011-11-04 02:10:21.43 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-11-04 02:10:21.43 Server Detected 4 CPUs. This is an informational message; no user action is required.
2011-11-04 02:10:21.43 Server Cannot use Large Page Extensions: lock memory privilege was not granted.
2011-11-04 02:10:21.56 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.
2011-11-04 02:10:21.57 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-11-04 02:10:21.65 spid1s A significant part of sql server process memory has been paged out. This may result in a performance degradation. Duration: 0 seconds. Working set (KB): 37180, committed (KB): 186808, memory utilization: 19%.
2011-11-04 02:10:22.73 Server Attempting to recover in-doubt distributed transactions involving Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-11-04 02:10:22.73 Server Database mirroring has been enabled on this instance of SQL Server.
2011-11-04 02:10:22.73 spid5s Starting up database 'master'.
2011-11-04 02:10:22.74 spid5s Error: 17207, Severity: 16, State: 1.
2011-11-04 02:10:22.74 spid5s FCB::Open: Operating system error 2(error not found) occurred while creating or opening file 'Y:\DATA\mastlog.ldf -c -T3607'. Diagnose and correct the operating system error, and retry the operation.
2011-11-04 02:10:22.74 spid5s Error: 17204, Severity: 16, State: 1.
2011-11-04 02:10:22.74 spid5s FCB::Open failed: Could not open file Y:\DATA\mastlog.ldf -c -T3607 for file number 2. OS error: 2(error not found).
2011-11-04 02:10:22.74 spid5s Error: 5120, Severity: 16, State: 101.
2011-11-04 02:10:22.74 spid5s Unable to open the physical file "Y:\DATA\mastlog.ldf -c -T3607". Operating system error 2: "2(error not found)".
November 3, 2011 at 8:26 pm
reg keys:
-dZ:\DATA\master.mdf
-eZ:\LOG\ERRORLOG
-lY\DATA\masterlog.ldf -c -T3607
looks right but again I query the traceflag location?
November 3, 2011 at 9:12 pm
Retry the above but try this
NET START MSSQLSERVER /c /f /T3608
Use t3608 instead of 3508
Then try the DAC connection (you may need to look up your dac port)
sqlcmd -S tcp:127.0.0.1,<DAC PORT>
Then try the sqlcmd alter table statements listed already.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 3, 2011 at 9:19 pm
really started to fear the worst here 🙁
Event log brings up:
FCB::Open: Operating system error 2(error not found) occurred while creating or opening file 'Y:\DATA\mastlog.ldf -c -T3607'. Diagnose and correct the operating system error, and retry the operation.
and :
FCB::Open failed: Could not open file Y:\DATA\mastlog.ldf -c -T3607 for file number 2. OS error: 2(error not found).
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
Errors ive found relating to OS errors are normally permissions? Ive changed the security (for now to try and get it working quickly!) Everyone Full Control, then just incase, The domain account that is used for starting the sql server has full control as well.
Ive given the service account local admin rights as well. Just to try and rid the error but so far no joy..
3 in the morning here and starting to think ill be having a nasty conversation with my boss shortly 🙁
November 3, 2011 at 9:28 pm
Ok..
not thinking. So i've taken out the trace flag and -c in the Server Config. Then using the command line. The service started.
When i tried the sqlcmd we have the same errorthough:
An error has occurred while establishing a connection to the server. When connecting to sql server 2005, this failure may be caused by the fact that under the default settings sql server does not allow remote connections.
Thanks again for all the help
November 3, 2011 at 9:55 pm
Update,
So the service is now started but i cant connect.
Ive tried SSMS from my work machine. which fails/times out
Ive tried sqlcmd on the server using my domain account and no joy
I cant find a SQL account. (I dont know the SA password)
Can someone confirm that domain names could work. Im
sqlcmd -S 127.0.0.1 -U domain\loginname
##any suggestions welcome!
November 3, 2011 at 9:59 pm
Try connecting with the DAC port like this
sqlcmd –S127.0.0.1,1434
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply