April 12, 2011 at 1:19 pm
I think this will help you map them properly.
http://msdn.microsoft.com/en-us/library/ms143547(v=SQL.90).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
April 12, 2011 at 1:40 pm
Thank you for the Link.. I believe that will help!!!
Now I think I created a larger mess for myself. I now see the benefit of keeping all of the system database FILES in their original default locations.
For my default Instance, I attempted to moved them back. Starting with Model and MSDB.
I issued this:
alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
go
alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
go
USE master;
GO
alter database model MODIFY FILE (NAME = modeldev,FILENAME='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
go
alter database model MODIFY FILE (NAME = modellog,FILENAME='E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data');
go
Then I stopped the Instance, moved the files to the new location and attempted to restart the Instance. it started the master and mssqlsystemresource databases and is choking on MODEL. I am getting errors like this in the event log:
FCB::Open failed: Could not open file E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is denied.).
-
FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
-
It indicated permissions but the Domain account that is starting the service has administrative rights to the server. any ideas???
April 12, 2011 at 1:46 pm
is this windows 2008? could be UACL settings so talk to server admins.
Make sure permissions on the directory on E are the same as the one on F, for the service account and the local accounts that 2005 creates on install.
---------------------------------------------------------------------
April 12, 2011 at 1:53 pm
Check the permissions on the model files. A readonly attribute may be set, or your account permissions may have gotten fouled up during the move.
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
April 12, 2011 at 2:58 pm
I checked the files and they were set "checked for archive" I unchecked that. still no go.
I then gave the following user full permissions to were the data and log files reside:
Servername\SQLServer2005MSSQLUser$Servername$MSSQLSERVER
I am still getting the permissions issue in the errolog at startup
I can start the Instance like this: NET START MSSQLSERVER /c /m /T3608
I am thinking now of just restoring my Model and MSDB databases from a backup. Does anyone have an thoughts on this? at this point, I jsut do not want to mess it up further...
April 12, 2011 at 3:57 pm
Master and tempdb and msdb start just fine?
Are the commands you posted exactly how they were executed on your server?
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
April 12, 2011 at 4:02 pm
If you try and move the files by restore to the E drive I would expect you would still hit permission problems.
Is your resource database still not in the E drive location?
check permissions all the way back to the root of the E.
download a tool called process monitor from the microsoft site, might give you a bit more info on the permissons error. It seems to be referring to permissions on the data directory rather than the file within it.
run procmon.exe just before you start SQL and stop it as soon as you get the error, it traps a LOT of info and eats into the page file.
---------------------------------------------------------------------
April 12, 2011 at 4:07 pm
Also, the error that you are getting is on the log file and not the data file. Anything happen to that file in particular?
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
April 12, 2011 at 6:00 pm
I started the instance in single user mode.
I successfully moved my model and msdb database files back to their original locations
I issue this
SELECT name, physical_name AS CurrentLocation
FROM sys.master_files
WHERE database_id < 5
and it shows the files here:
masterF:\MSSQL\Servername\DataFiles\master.mdf
mastlogG:\MSSQL\Servername\Logfiles\mastlog.ldf
tempdevH:\MSSQL\Servername\TEMPDB\tempdb.mdf
templogH:\MSSQL\Servername\TEMPDB\templog.ldf
modeldevE:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
modellogE:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
MSDBDataE:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
MSDBLogE:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
the resource database files are here: E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data
when I try and start the instance normally, i get this error A service specific error occurred: 1814.
and this in the errorlog.
2011-04-12 19:54:16.47 Server Microsoft SQL Server 2005 - 9.00.4226.00 (X64)
May 26 2009 14:58:11
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 (Build 7600: )
2011-04-12 19:54:16.47 Server (c) 2005 Microsoft Corporation.
2011-04-12 19:54:16.47 Server All rights reserved.
2011-04-12 19:54:16.47 Server Server process ID is 11056.
2011-04-12 19:54:16.47 Server Authentication mode is MIXED.
2011-04-12 19:54:16.47 Server Logging SQL Server messages in file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-04-12 19:54:16.47 Server This instance of SQL Server last reported using a process ID of 2908 at 4/12/2011 7:54:00 PM (local) 4/12/2011 11:54:00 PM (UTC). This is an informational message only; no user action is required.
2011-04-12 19:54:16.47 Server Registry startup parameters:
2011-04-12 19:54:16.47 Server -d F:\MSSQL\Servername\DataFiles\master.mdf
2011-04-12 19:54:16.47 Server -e E:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-04-12 19:54:16.47 Server -l G:\MSSQL\Servername\Logfiles\mastlog.ldf
2011-04-12 19:54:16.47 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-04-12 19:54:16.47 Server Detected 24 CPUs. This is an informational message; no user action is required.
2011-04-12 19:54:16.58 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-04-12 19:54:16.58 Server Lock partitioning is enabled. This is an informational message only. No user action is required.
2011-04-12 19:54:16.59 Server Multinode configuration: node 0: CPU mask: 0x0000000000fff000 Active CPU mask: 0x0000000000fff000. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-04-12 19:54:16.59 Server Multinode configuration: node 1: CPU mask: 0x0000000000000fff Active CPU mask: 0x0000000000000fff. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2011-04-12 19:54:16.59 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-04-12 19:54:18.61 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-04-12 19:54:18.61 Server Database mirroring has been enabled on this instance of SQL Server.
2011-04-12 19:54:18.61 spid7s Starting up database 'master'.
2011-04-12 19:54:18.72 spid7s SQL Trace ID 1 was started by login "sa".
2011-04-12 19:54:18.73 spid7s Starting up database 'mssqlsystemresource'.
2011-04-12 19:54:18.74 spid7s The resource database build version is 9.00.4226. This is an informational message only. No user action is required.
2011-04-12 19:54:19.00 spid11s Starting up database 'model'.
2011-04-12 19:54:19.00 spid7s Server name is 'Servername'. This is an informational message only. No user action is required.
2011-04-12 19:54:19.01 spid11s Error: 17207, Severity: 16, State: 1.
2011-04-12 19:54:19.01 spid11s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2011-04-12 19:54:19.01 spid11s Error: 17204, Severity: 16, State: 1.
2011-04-12 19:54:19.01 spid11s FCB::Open failed: Could not open file E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 1. OS error: 5(Access is denied.).
2011-04-12 19:54:19.01 spid11s Error: 5120, Severity: 16, State: 101.
2011-04-12 19:54:19.01 spid11s Unable to open the physical file "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2011-04-12 19:54:19.01 spid11s Error: 17207, Severity: 16, State: 1.
2011-04-12 19:54:19.01 spid11s FCB::Open: Operating system error 5(Access is denied.) occurred while creating or opening file 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data'. Diagnose and correct the operating system error, and retry the operation.
2011-04-12 19:54:19.01 spid11s Error: 17204, Severity: 16, State: 1.
2011-04-12 19:54:19.01 spid11s FCB::Open failed: Could not open file E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data for file number 2. OS error: 5(Access is denied.).
2011-04-12 19:54:19.01 spid11s Error: 5120, Severity: 16, State: 101.
2011-04-12 19:54:19.01 spid11s Unable to open the physical file "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data". Operating system error 5: "5(Access is denied.)".
2011-04-12 19:54:19.01 spid11s File activation failure. The physical file name "E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data" may be incorrect.
2011-04-12 19:54:19.01 spid11s Error: 945, Severity: 14, State: 2.
2011-04-12 19:54:19.01 spid11s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2011-04-12 19:54:19.01 spid11s Could not create tempdb. You may not have enough disk space available. Free additional disk space by deleting other files on the tempdb drive and then restart SQL Server. Check for additional errors in the event log that may indicate why the tempdb files could not be initialized.
2011-04-12 19:54:19.01 spid11s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
I added the local user Servername\SQLServer2005MSSQLUser$Servername$MSSQLSERVER to have full permissions foreach drive at the root level. I'm stumped. I still would like to try just a restore of msdb and model but like I said.. i don't want to make it worse..
April 13, 2011 at 6:50 am
Looks like I had a typo in my final Alter Database commands. Forgot to specify filenames.. oops
I started up in single user mode and reissued my commands correctly like this and the instance started up just fine:
USE master;
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdbdata, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdbdata.mdf');
GO
ALTER DATABASE MSDB
MODIFY FILE (NAME = msdblog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\msdblog.ldf');
GO
USE master;
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modeldev, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Model.mdf');
GO
ALTER DATABASE MODEL
MODIFY FILE (NAME = modellog, FILENAME = 'E:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\modellog.ldf');
GO
Thank you all for your help!!!!!!
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply