November 7, 2011 at 5:43 am
Hi Everyone,
I have sql server 2008 sp1 instance. It is test standalone instance.
Scenario is , i am not able to bring the sql instance.
1. Someone has stopped sql server.
2. Went to physical data dir location and deleted some of test user db's
3. When i am trying to start the sql, it is not coming online.
How to bring my sql functional?
Seems like , those database entries are still in sys.databases in master db and it trying to look for mdf and ldf files which aren't there any more!!
How to come out of this situation? Is there any way to delete those entries in sys.databases so that sql ignores those missing db's?
Thanks in Advance.
November 7, 2011 at 5:46 am
We're going to need the exact error messages. Open up the SQL error log (or windows event viewer, check the errors, post them here)
SQL won't fail to start because some user databases are missing.
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
November 7, 2011 at 10:47 am
they more than likely deleted a system database by mistake, as Gail says error messages please!
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 7, 2011 at 4:42 pm
2011-11-02 20:03:34.43 Server Microsoft SQL Server 2005 - 9.00.4060.00 (X64)
Mar 17 2011 13:06:52
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.1 (Build 7600: )
2011-11-02 20:03:34.43 Server (c) 2005 Microsoft Corporation.
2011-11-02 20:03:34.43 Server All rights reserved.
2011-11-02 20:03:34.43 Server Server process ID is 7584.
2011-11-02 20:03:34.43 Server Authentication mode is MIXED.
2011-11-02 20:03:34.43 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2011-11-02 20:03:34.43 Server This instance of SQL Server last reported using a process ID of 2596 at 11/2/2011 7:50:48 PM (local) 11/2/2011 2:20:48 PM (UTC). This is an informational message only; no user action is required.
2011-11-02 20:03:34.43 Server Registry startup parameters:
2011-11-02 20:03:34.43 Server -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
2011-11-02 20:03:34.43 Server -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
2011-11-02 20:03:34.43 Server -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2011-11-02 20:03:34.44 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2011-11-02 20:03:34.44 Server Detected 2 CPUs. This is an informational message; no user action is required.
2011-11-02 20:03:34.51 Server Using locked pages for buffer pool.
2011-11-02 20:03:34.66 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-02 20:03:34.68 Server Attempting to initialize Microsoft Distributed Transaction Coordinator (MS DTC). This is an informational message only. No user action is required.
2011-11-02 20:03:34.69 Server QueryInterface failed for "ITransactionDispenser": 0x8004d01c(XACT_E_CONNECTION_DOWN).
2011-11-02 20:03:34.69 Server Database mirroring has been enabled on this instance of SQL Server.
2011-11-02 20:03:34.69 spid4s Starting up database 'master'.
2011-11-02 20:03:34.81 spid4s CHECKDB for database 'master' finished without errors on 2011-09-22 19:43:53.913 (local time). This is an informational message only; no user action is required.
2011-11-02 20:03:34.82 spid4s SQL Trace ID 1 was started by login "sa".
2011-11-02 20:03:34.84 spid4s Starting up database 'mssqlsystemresource'.
2011-11-02 20:03:34.86 spid4s The resource database build version is 9.00.4035. This is an informational message only. No user action is required.
2011-11-02 20:03:35.03 spid4s Server name is 'WINSERV\SQL2005'. This is an informational message only. No user action is required.
2011-11-02 20:03:35.04 spid9s Starting up database 'model'.
2011-11-02 20:03:35.21 Server A self-generated certificate was successfully loaded for encryption.
2011-11-02 20:03:35.21 Server Server is listening on [ 'any' <ipv6> 58931].
2011-11-02 20:03:35.21 Server Server is listening on [ 'any' <ipv4> 58931].
2011-11-02 20:03:35.21 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\SQL2005 ].
2011-11-02 20:03:35.21 Server Server named pipe provider is ready to accept connection on [ \\.\pipe\MSSQL$SQL2005\sql\query ].
2011-11-02 20:03:35.21 Server Server is listening on [ ::1 <ipv6> 58932].
2011-11-02 20:03:35.21 Server Server is listening on [ 127.0.0.1 <ipv4> 58932].
2011-11-02 20:03:35.21 Server Dedicated admin connection support was established for listening locally on port 58932.
2011-11-02 20:03:35.22 spid9s CHECKDB for database 'model' finished without errors on 2011-09-22 19:43:57.353 (local time). This is an informational message only; no user action is required.
2011-11-02 20:03:35.22 spid9s Clearing tempdb database.
2011-11-02 20:03:35.24 spid9s Error: 5123, Severity: 16, State: 1.
2011-11-02 20:03:35.24 spid9s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'c:\tempdata\tempdb.mdf'.
2011-11-02 20:03:35.24 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.
2011-11-02 20:03:35.25 spid12s Starting up database 'msdb'.
2011-11-02 20:03:35.25 spid14s Starting up database 'DB1'.
2011-11-02 20:03:35.25 spid13s Starting up database 'AIG-RADIUS'.
2011-11-02 20:03:35.25 spid15s Starting up database 'DB2'.
2011-11-02 20:03:35.25 spid16s Starting up database 'SRC'.
2011-11-02 20:03:35.33 spid14s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid14s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\DB1.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.33 spid15s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid15s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\DB2.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.33 spid13s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid13s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\AIG-RADIUS.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.33 spid16s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid16s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\SRC.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.33 spid17s Starting up database 'TRG'.
2011-11-02 20:03:35.33 spid15s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid15s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\DB2_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.33 spid13s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.33 spid13s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\AIG-RADIUS_log.LDF'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.34 spid14s Error: 17207, Severity: 16, State: 1.
2011-11-02 20:03:35.34 spid14s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\SQLDUMP\DB1_log.LDF'. Diagnose and correct the operating system error, and retry the operation.
2011-11-02 20:03:35.34 spid17s Error: 823, Severity: 24, State: 6.
2011-11-02 20:03:35.34 spid17s The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0000000000000000 in file 'D:\SQLDUMP\TRG.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
2011-11-02 20:03:35.34 spid16s Error: 17207, Severity: 16, State: 1.
2011-11-02 20:03:35.34 spid16s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\SQLDUMP\SRC_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
2011-11-02 20:03:35.34 spid15s File activation failure. The physical file name "D:\SQLDUMP\DB2_log.LDF" may be incorrect.
2011-11-02 20:03:35.34 spid13s File activation failure. The physical file name "D:\SQLDUMP\AIG-RADIUS_log.LDF" may be incorrect.
2011-11-02 20:03:35.34 spid14s File activation failure. The physical file name "D:\SQLDUMP\DB1_log.LDF" may be incorrect.
2011-11-02 20:03:35.34 spid17s Error: 17207, Severity: 16, State: 1.
2011-11-02 20:03:35.34 spid17s FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'D:\SQLDUMP\TRG_log.ldf'. Diagnose and correct the operating system error, and retry the operation.
2011-11-02 20:03:35.34 spid16s File activation failure. The physical file name "D:\SQLDUMP\SRC_log.ldf" may be incorrect.
2011-11-02 20:03:35.34 spid17s File activation failure. The physical file name "D:\SQLDUMP\TRG_log.ldf" may be incorrect.
2011-11-02 20:03:35.51 spid9s Error: 17207, Severity: 16, State: 1.
2011-11-02 20:03:35.51 spid9s FCB::Open: Operating system error 3(The system cannot find the path specified.) occurred while creating or opening file 'c:\tempdata\tempdb.mdf'. Diagnose and correct the operating system error, and retry the operation.
2011-11-02 20:03:35.51 spid9s Error: 17204, Severity: 16, State: 1.
2011-11-02 20:03:35.51 spid9s FCB::Open failed: Could not open file c:\tempdata\tempdb.mdf for file number 1. OS error: 3(The system cannot find the path specified.).
2011-11-02 20:03:35.51 spid9s Error: 5120, Severity: 16, State: 101.
2011-11-02 20:03:35.51 spid9s Unable to open the physical file "c:\tempdata\tempdb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
2011-11-02 20:03:35.51 spid9s Error: 1802, Severity: 16, State: 4.
2011-11-02 20:03:35.51 spid9s CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2011-11-02 20:03:35.51 spid9s Error: 5123, Severity: 16, State: 1.
2011-11-02 20:03:35.51 spid9s CREATE FILE encountered operating system error 3(The system cannot find the path specified.) while attempting to open or create the physical file 'c:\tempdata\tempdb.mdf'.
2011-11-02 20:03:35.51 spid9s 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-11-02 20:03:35.51 spid9s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
November 7, 2011 at 4:51 pm
I have observed that "'c:\tempdata" dir itself it is missing and failing to create the tempdb.
Once i created the folder 'c:\tempdata' , sql came online.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply