SQL Server Day to Day issues- Part2


Here comes another issue that is being faced by DBAs frequently. (Read part 1 here)

Issue- SQL Server Instance fails to start

A number of problems can prevent the SQL Server service from starting. Let’s go through each of them.

1) Service account password incorrect or account locked or disabled

If someone changes the SQL Server service account password at AD level, but doesn’t update it at SQL level in configuration Manager then SQL Server runs fine until the next time we restart it.

Additionally, if a user repeatedly enters the password incorrectly, or a service (SQL, or any other service that may be using the same account) repeatedly attempts to submit the old password, after a password change, then the account will eventually be locked. As before, this will have no effect on a running SQL Server but the next time that the service attempts to start, it will be unable to do so. The Error Message that we get in system viewer is as shown in below Screenshot :

1722-1-cc6f515b-d19f-42d1-871d-6167aebab325

It clearly shows that an incorrect password caused the problem. Other possible messages are:

  • Logon failure: Account currently disabled.
  • Login failed: Account locked out

Solution

  • If the error is Password related then change the password that the SQL Service is using to match the account’s true password, or reset the account password.
  • If the account is disabled or locked, then enable or unlock it.

2) Unable to open the error log

SQL Server needs to locate and cycle the error log for successful start. It locates the error log using the –e startup parameter. Once it gets the location, it renames the existing error log files ( like Errorlog becomes Errorlog.1 and Errorlog.1 becomes Errorlog.2, and so on), creates a new Errorlog and starts logging the startup progress into this file. If the directory specified for the error log does not exist, startup will fail.

The Possible Error messages that we get are

  • Error 3 : Folder not found.
  • Error 5 : Permission denied
  • Error 32 : the Errorlog file was in use by another process ( may be Antivirus scanner)

Error_17058

Below are the main reasons for missing error log Directory:

  • Startup Parameter has been changed and due to this SQL Server looks for a location that doesn’t exist
  • SQL Server doesn’t have permission on the folder containing error log.
  • Drive or Error log folder missing

Solution

  • If the culprit is Antivirus Scanner which was reading the file when SQL tried to access it, then the resolution is to restart the service and fixing the anti-virus configuration to ensure that the problem does not reoccur.
  • If the SQL Server service does not have permission to access the folder, then we grant the necessary permission and restart the SQL server service.
  • If the problem is that the folder or drive is missing, then we can modify the value specified by the –e startup parameter so that it points to a location that exists, which should allow the SQL Server service to start.

3) Master Database files Inaccessible

When SQL Server starts, it recovers master database to load configuration settings and open other system and user Databases. Until Master Database is not accessible, SQL Server doesn’t start.

If someone modifies the startup parameters incorrectly, SQL Server could look in the wrong place for the master Database files. Alternatively, the location might be correct but inaccessible due, for example, to a drive failure, or the SQL Server service account not having permission to access the specified files or folders. In each case, the startup will fail.

The Possible Error messages we may get are :

  • Could not open file C:\Program Files\Microsoft SQL Server\ MSSQL10.MSSQLSERVER\MSSQL \DATA\mastlog.ldf
  • Error 2(failed to retrieve text for this error. Reason: 15100) occurred while opening file ‘C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\master.mdf’ to obtain configuration information at startup. An invalid startup option might have caused the error.

Solution

  • The First Error clearly indicates Permission issue that SQL Server doesn’t have permission to mastlog file. Here, we need to check that whether the Service account through which SQL Services are running has access on folders having Master Database files.
  • Second Error indicates that there has been made some change in startup Parameter. We need to check that whether startup parameter points to correct location or not.The –d parameter specifies the location of the data file, the –l specifies the location of the log file.
  • If the Files are not present at the location, perhaps because the drive failed or someone accidentally deleted them, then we’ll need to restore them from backup.In order to restore the backup of the Master database, we have to:
    1. Rebuild all the system databases
    2. Start SQL Server in single-user mode
    3. Restore the backup of Master, as well as backups of Model and MSDB.

Above three Points will be covered in next part. Till then, Stay Tuned !.

,

One response to “SQL Server Day to Day issues- Part2”

  1. Excellent, thanks for posting, I have an question, during antivirus scanning on error log file whether is there any interruption happens error to write on log files?