my 'master' database will not restart in my sql server 2008 instance

please look at the ERRORLOG file in the sql server folder, it will have more detailed information about the startup log of sql server...

When you find the error codes in ERRORLOG, check this site:

Cause and Resolution of Database Engine Errors: < http://msdn.microsoft.com/en-us/library/ms365262.aspx >

i am quite good with sql server but as a developer, writing queries, stored procedures and T-SQL.
i am not very good with admin things regarding sql server. wherre can i find the ERRORLOG? IN what folder?

The error is 3414.

one possible solution is here

http://msdn.microsoft.com/en-us/library/aa337445.aspx

but i cannot restore cause i have no backup.

on my computer, it was here:
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG

>but i cannot restore cause i have no backup.
bad news.

you will have to reinstall your sql server, and reattach the non-system databases (hopefully they are not inconsistent)...

thanks i did find the location of ERRORLOG. THIS IS THE ERROR LOG(look towards the end). I have an issue with the master database. well that is not good, is it?

"2008-09-22 09:38:05.66 Server      Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
      Jul  9 2008 14:43:34
      Copyright (c) 1988-2008 Microsoft Corporation
      Standard Edition on Windows NT 6.0 <X86> (Build 6001: Service Pack 1)

2008-09-22 09:38:05.66 Server      (c) 2005 Microsoft Corporation.
2008-09-22 09:38:05.66 Server      All rights reserved.
2008-09-22 09:38:05.66 Server      Server process ID is 6104.
2008-09-22 09:38:05.66 Server      System Manufacturer: 'Acer', System Model: 'Aspire 5680'.
2008-09-22 09:38:05.66 Server      Authentication mode is WINDOWS-ONLY.
2008-09-22 09:38:05.66 Server      Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG'.
2008-09-22 09:38:05.66 Server      This instance of SQL Server last reported using a process ID of 1560 at 22/9/2008 9:23:51 À¼ (local) 22/9/2008 6:23:51 À¼ (UTC). This is an informational message only; no user action is required.
2008-09-22 09:38:05.67 Server      Registry startup parameters:
       -d C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\master.mdf
       -e C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG
       -l C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\mastlog.ldf
2008-09-22 09:38:05.70 Server      SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.
2008-09-22 09:38:05.70 Server      Detected 2 CPUs. This is an informational message; no user action is required.
2008-09-22 09:38:05.76 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.
2008-09-22 09:38:05.83 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.
2008-09-22 09:38:05.87 spid7s      Starting up database 'master'.
2008-09-22 09:38:06.03 spid7s      WARNING: did not see LOP_CKPT_END.
2008-09-22 09:38:06.03 spid7s      Error: 3414, Severity: 21, State: 2.
2008-09-22 09:38:06.03 spid7s      An error occurred during recovery, preventing the database 'master' (database ID 1) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support.
2008-09-22 09:38:06.03 spid7s      Cannot recover the master database. SQL Server is unable to run. Restore master from a full backup, repair it, or rebuild it. For more information about how to rebuild the master database, see SQL Server Books Online."

you can rebuild the master database , check out this link

http://blogs.msdn.com/psssql/archive/2008/08/29/how-to-rebuild-system-databases-in-sql-server-2008.aspx

ok thanks julianva

but can you help me with that rebuilding?

what is setup.exe?

i am a msdn subscriber.

i did download sql server 2008 from msdn, installed it and then deleted the installation files and sql server was working fine till yesterday(when we had an unscheduled power cut which is propaly responsible for this).

can you walk me through this solution?

thanks

You would need the installation to rebuild master.

try to start sql server from command line in single user mode.
get dos prompt
default instance = sqlservr.exe -m
named instance = sqlservr.exe -m -s <instancename>

another soulution is to install another instance of SQL on your machine then attach all the other databases that you had.

i got a dos prompt  and when i tried to run the sqlservr.exe -m command i got the error

'sqlservr.exe' is not recognised as internal command....

so i need to download the software again from the msdn site.

is that what you are telling me?

and then try to rebuild the master database?

what about the repair tools?

you need to go to the folder that has the 'sqlservr.exe'  file
normally it is in the c:\program files\microsft sql server \bin

Yes , you can only use the repair tools like the rebuilddatabase if you have the installation.

to clear things.

to use the following command, do i need to have the installation files?

default instance = sqlservr.exe -m

or i need the installation files only if i want to use the repair tools?

i did run the above command(sql server 2008 is the default instace) from the

C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Binn command prompt

where the sqlservr.exe is located and i got many errors.

now i download the sql server 2008 developer edition again from the msdn site.

what i should i do then?

Before that ... What user is used to start the SQL Server Services?
Does that user have read-write access to the folder containing the Data Files.
Check that first...
-- Manu

you can uninstall the instance that you have at the moment , then reinstall the developer edition , make sure you know where your user databases is located  that is the MDF and LDF files ,
you would have to attach them to the new installed sql server.

yes the user has rights to every folder. i log in as administrator and i have access everywhere.


i have downloaded the developer edition sql server 2008 from the msdn site.

what i do now?

can you please help me out with the correct process?

i do not want to uninstall the whole server and reinstall it.

can i use the repair tools with the setup.exe that i have?

thanks

thanks a lot. i did it!!!! worked fine.

Did you rebuild master or reinstall developer edition?.

Please make backups of you system databases.