How To Repair Sql Server 2012 And Why This Might Be Necessary Coursehero

If yous are a professional SQL Server DBA, you must accept faced this issue at least one time in your career. Your junior admin calls you during vacation to inform yous that the production instance is non starting and something seems to be wrong. This piece addresses the top iii reasons that your SQL Server Service may non be working and offers some simple solutions to resolve potential roadblocks.

Hither is the error which yous would receive when we endeavour to beginning SQL Services from various places.

Windows could not commencement the SQL Server (MSSQLSERVER) on Local Computer. For more information, review the System Consequence Log.
OR
"The service failed to respond in a timely fashion".
OR
"The service failed to start".

We volition endeavour to investigate each of these reasons messages and possible resolution for the same.

Reason # 1: S ervice account password changed but not updated on the server where SQL Server instance is installed

This is one of the nearly common cause where service account password has been changed by domain admin or SQL Admin just this information is non updated in SQL Server Services.

Here is the error which we would run across if we attempt to first using Services.

---------------------------
Services
---------------------------
Windows could not first the SQL Server (MSSQLSERVER) service on Local Reckoner. Error 1069: The service did non start due to a logon failure. ---------------------------
OK
---------------------------

Organisation Event logs should show beneath

Log Proper name:      System

Source:        Service Control Manager

Date:          <Engagement Time>

Event ID:      7000

Task Category: None

Level:         Fault

Keywords:      Classic

User:          North/A

Calculator: MyServer

Clarification: The SQL Server (MSSQLSERVER) service failed to start due to the post-obit error:

The service did not start due to a logon failure.

Solution : We need to update the password in services. The correct way to do information technology is to use SQL Server Configuration Manager and type in new password (under Log On tab) every bit shown below

That was easy to say the least. Now allow united states of america move to the next message.

Reason # 2: S tartup parameters take incorrect file path locations.

This is another common cause of SQL Server Service startup failure. Let's assume that master database is located on a drive and files of the database ( main.mdf and/or mastlog.ldf ) are non available. Since master database is a system database, SQL Service would fail to start. If we endeavor to beginning SQL via services, we will get below error.

---------------------------

Services

---------------------------

Windows could not offset the SQL Server (MSSQLSERVER) on Local Figurer. For more than information, review the Arrangement Consequence Log. If this is a non-Microsoft service, contact the service vendor, and refer to service-specific fault lawmaking 17113.

---------------------------

OK

---------------------------

If nosotros try the aforementioned via configuration director, we go standard mistake which doesn't explain much.

---------------------------

SQL Server Configuration Manager

---------------------------

The asking failed or the service did not respond in a timely style. Consult the event log or other applicable error logs for details.

---------------------------

OK

---------------------------

Whenever we become such errors, nosotros should showtime looking at SQL Server ERRORLOGs are divers under starting time-upwardly parameters or application event log. Nosotros can look at SQL Server Configuration Manager and look for Startup parameter having proper noun -eastward every bit shown below (for SQL 2022) :

Nosotros can open ERRORLOG using notepad or whatever other text editor. Here is the snippet which shows the problem.

<Engagement Time> Server      Error: 17113, Severity: 16, Land: one.

<Date Time> Server Error two(The organization cannot observe the file specified.) occurred while opening file 'C :\ Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Information\ master.mdf ' to obtain configuration information at startup. An invalid startup option might have caused the error. Verify your startup options, and correct or remove them if necessary.

As highlighted above, we can see that SQL is not able to find the file chief.mdf .

Below is another case of mistake for model database.

<Date Time> spid9s Error: 17207, Severity: sixteen, State: i .
<Appointment Fourth dimension> spid9s FileMgr :: StartLogFiles : Operating system mistake ii(The organization cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Information\ modellog.ldf '. Diagnose and correct the operating system error, and retry the performance.
<Date Time> spid9s File activation failure. The physical file name "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Information\ modellog.ldf " may be wrong.
<Date Time> spid9s Error: 945, Severity: 14, State: 2.
<Date Time> spid9s Database 'model' cannot be opened due to inaccessible files or insufficient memory or disk space. Run across the SQL Server errorlog for details.
<Date Time> spid9s Could not create tempdb . You may not have enough deejay space available. Gratuitous additional disk space by deleting other files on the tempdb drive and and then restart SQL Server. Check for additional errors in the event log that may signal why the tempdb files could not be initialized.

Solution : Start we need to find out the correct location of the files. Once files are identified, either put them into the location where SQL Server wants or ALTER the database to signal to correct location. If t hither is an effect with model or tempdb database then we demand to beginning SQL Server using trace flag 3608 as explained in http://msdn.microsoft.com/en-the states/library/ms345408.aspx

Reason # iii : S ystem database files not available - accidental del etion or corruption due to deejay failures .

If files are missing or corrupted for system databases (master and/or model) SQL Server service would not first. ERRORLOG (mentioned earlier ) would contain the exact database proper name and file name which has the problem. Here are few snippets of error pointing to corruption of system databases.

<Date Time> spid5s Starting up database 'master'.

<Appointment Fourth dimension > spid5s Mistake: 9003, Severity: 20, State: ane.

<Date Time > spid5s The log scan number (216:72:one) passed to log scan in database 'master' is not valid. This error may signal data abuse or that the log file (. ldf ) does not lucifer the data file (. mdf ). If this fault occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

<Date Time> spid5s Cannot recover the chief database. SQL Server is unable to run. Restore main from a full fill-in, repair it, or rebuild it. For more information nearly how to rebuild the principal database, run across SQL Server Books Online.

Depending on which database file is corrupted , we need to have advisable activity . If master database files are corrupted (mistake above) and so we need to rebuild the master database and restore it from the fill-in. If issue exists with other system databases then SQL can be started via trace flag and they tin be restored. Books online has topic for each database. Refer http://msdn.microsoft.com/en-united states of america/library/ms190190.aspx

At that place can be many other reasons to service not starting which we volition cover in a carve up weblog in the hereafter .  Read the white newspaper "Blocking and Locking Troubleshooting in SQL Server for more than information."

Embarcadero DBArtisan® is a database assistants toolset that helps DBAs maximize availability, performance and security across multiple DBMSs. Watch this short video to learn more about DBArtisan. And try DBArtisan today.

Source: https://community.embarcadero.com/article/articles-database/16041-top-3-reasons-why-my-sql-server-service-is-not-sarting

Posted by: krierequadvance.blogspot.com

Share this post