July 7, 2012 at 12:55 pm
I was trying to learn the movement of system databases from one drive to another. i satarted with msdb,model and tempdb. But while tying the command alter database msdb modify file... i specified a path like F:/msdb.mdf. Tried to restart SQL server and it is not restarting... I tried to login with minimum configuration but i got the following error:
C:\Users\dev>net start MSSQLSERVER /f -T3608
The SQL Server (MSSQLSERVER) service is starting..
The SQL Server (MSSQLSERVER) service could not be started.
A service specific error occurred: 1814.
More help is available by typing NET HELPMSG 3547.
-------------------
I was trying this on my personal laptop having Windows Vista home basic and SQL server 2008 dev edittion. Please find the error log below and let me know how can i restart SQL server...
2012-07-07 23:57:28.14 Server Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
Jul 9 2008 14:43:34
Copyright (c) 1988-2008 Microsoft Corporation
Developer Edition on Windows NT 6.0 <X86> (Build 6000: )
2012-07-07 23:57:28.14 Server (c) 2005 Microsoft Corporation.
2012-07-07 23:57:28.14 Server All rights reserved.
2012-07-07 23:57:28.14 Server Server process ID is 5088.
2012-07-07 23:57:28.14 Server System Manufacturer: 'Hewlett-Packard', System Model: 'Compaq Presario C700 Notebook PC'.
2012-07-07 23:57:28.14 Server Authentication mode is MIXED.
2012-07-07 23:57:28.14 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG'.
2012-07-07 23:57:28.14 Server This instance of SQL Server last reported using a process ID of 2388 at 07-07-2012 23:55:38 (local) 07-07-2012 18:25:38 (UTC). This is an informational message only; no user action is required.
2012-07-07 23:57:28.14 Server Registry startup parameters:
-m
-d C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\mastlog.ldf
2012-07-07 23:57:28.16 Server Command Line Startup Parameters:
/f
2012-07-07 23:57:28.17 Server Warning: The server instance was started using minimal configuration startup option (-f). Starting an instance of SQL Server with minimal configuration places the server in single-user mode automatically. After the server has been started with minimal configuration, you should change the appropriate server option value or values, stop, and then restart the server.
2012-07-07 23:57:28.17 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2012-07-07 23:57:28.17 Server Detected 2 CPUs. This is an informational message; no user action is required.
2012-07-07 23:57:28.24 Server Perfmon counters for resource governor pools and groups failed to initialize and are disabled.
2012-07-07 23:57:28.25 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.
2012-07-07 23:57:28.36 Server Node configuration: node 0: CPU mask: 0x00000003 Active CPU mask: 0x00000003. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.
2012-07-07 23:57:28.39 Server Support for distributed transactions was not enabled for this instance of the Database Engine because it was started using the minimal configuration option. This is an informational message only. No user action is required.
2012-07-07 23:57:28.41 spid7s Warning ******************
2012-07-07 23:57:28.41 spid7s SQL Server started in single-user mode. This an informational message only. No user action is required.
2012-07-07 23:57:28.41 spid7s Starting up database 'master'.
2012-07-07 23:57:29.08 spid7s SQL Server was started using the -f flag. SQL Server Audit is disabled. This is an informational message. No user action is required.
<{39B75A24-0837-4CEC-AFDF-B960027AE07E}>RsFxNso initialized. InstanceId = 00000003
<{50080099-5EC4-4EAF-A2A2-63C3DA97F8EB}>FsAgent is initialized
<{09C4480B-DBA4-49B7-956F-68A8B8B2445D}>FsAgent is up and running
2012-07-07 23:57:29.09 spid7s FILESTREAM: effective level = 2, configured level = 2, file system access share name = 'MSSQLSERVER'.
2012-07-07 23:57:29.09 spid7s Server started with '-f' option. Auditing will not be started. This is an informational message only; no user action is required.
2012-07-07 23:57:29.09 spid7s Starting up database 'mssqlsystemresource'.
2012-07-07 23:57:29.14 spid7s The resource database build version is 10.00.1600. This is an informational message only. No user action is required.
2012-07-07 23:57:29.61 spid13s Starting up database 'model'.
2012-07-07 23:57:29.61 spid13s Error: 17204, Severity: 16, State: 1.
2012-07-07 23:57:29.61 spid13s FCB::Open failed: Could not open file F:/modeldev.mdf for file number 1. OS error: 2(failed to retrieve text for this error. Reason: 15100).
2012-07-07 23:57:29.61 spid13s Error: 5120, Severity: 16, State: 101.
2012-07-07 23:57:29.61 spid13s Unable to open the physical file "F:/modeldev.mdf". Operating system error 2: "2(failed to retrieve text for this error. Reason: 15105)".
2012-07-07 23:57:29.61 spid13s Error: 17207, Severity: 16, State: 1.
2012-07-07 23:57:29.61 spid13s FileMgr::StartLogFiles: Operating system error 2(failed to retrieve text for this error. Reason: 15105) occurred while creating or opening file 'F:/modellog.ldf'. Diagnose and correct the operating system error, and retry the operation.
2012-07-07 23:57:29.61 spid13s File activation failure. The physical file name "F:/modellog.ldf" may be incorrect.
2012-07-07 23:57:29.61 spid13s Error: 945, Severity: 14, State: 2.
2012-07-07 23:57:29.61 spid13s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.
2012-07-07 23:57:29.61 spid13s 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.
2012-07-07 23:57:29.64 spid7s Server name is 'DEV-PC'. This is an informational message only. No user action is required.
July 7, 2012 at 1:17 pm
Don't use NET Start. Stop the service, then start SQL as an application. From the command line (in the appropriate directory)
SQLServr.exe -m -f -T3608
Make sure that SQL agent is stopped/ Make sure there is noting else that could try to connect to SQL Server.
What you should see in the command window is the error log output, and it should contain something like this:
2012-07-07 21:12:04.83 Server Registry startup parameters: -d C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\master.mdf
-e C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\Log\ERRORLOG
-l C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\mastlog.ldf
2012-07-07 21:12:04.83 Server Command Line Startup Parameters:
-m
-f
-T 3608
That's paths for my server, not yours.
Note that your error log makes no mention of the traceflag being enabled, not sure if that's a side effect of using NET START, because of useing /f not -f or for some other reason. Hence while your SQL instance started in restricted mode, the trace flag was not enabled and it did try to recover the other DBs and hence failed and shut down again.
With SQL started like that, no database other than master is recovered. You will see no mention of any DB other than master in the error log.
It's model that you need to fix, SQL can actually start without MSDB, but without model it can't recreate TempDB. Fix model as a priority. Preferably use SQLCMD to connect, SSMS has a habit of making more than one connection and breaking (SQL is in single user mode)
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
July 8, 2012 at 8:05 pm
Thanks a lot... it worked fine.
February 20, 2013 at 8:14 pm
Hi,
Could you pls provide the steps to fix the model database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply