Think before you restart the SQL Server Service

When we face an issue with SQL Server or any other software, we simply think that either restart of the server or service would solve the issue, and we do it. The situation can be something like; No Response, Takes long time to produce the result, or does not allow to make new connections. Whatever the situation, you need to think twice before restarting the service.

Why we should not restart it immediately? One thing you need to remember is, SQL Server logs all possible information related its processes in the server logs. By default SQL Server maintains 6 archives along with the current file and files are getting replaced with every restart. For example, when you restart, it creates a new log file, makes the old-current-file as Archive #1, Old Archive #1 as Archive #2, and so on, deleting the last Archive file. If multiple restarts were done, useful information in log files would erased and will not be able to see what caused the issue.

Even though it maintains 6 files, I think it is better to maintain more than six files. A number like 10-15 would do better even though it consumes space unnecessary. Who knows, one day they will be very useful if something happens to the server and need to find reasons for it.

Considering the fact discussed, make sure that SQL Server is not getting restarted unnecessarily. If need, it is better to take a copy of all existing files and then do the restart.

What if that the required log has been over-written?

You may experience this when you troubleshoot an issue. There is another way to see what has happened with SQL Server though the container is not as rich as the log. It is the default trace (Thanks for the Data Platform MVP group who helped me to understand this). Generally, default trace records many events including errors. By default it is enabled but for some reasons if you need to enable or disable, it can be done with sp_configure.

By Querying sys.traces, the path for the trace files can be found and files can be opened either using TSQL or Profiler.

You can read more info on the Default Trace at: https://www.mssqltips.com/sqlservertip/1739/using-the-default-trace-in-sql-server-2005-and-sql-server-2008/

 

SQL Server 2008 R2 Error: Login failed for user ''. Reason: Server is in script upgrade mode. Only administrator can connect at this time. (Microsoft SQL Server, Error: 18401)

I started experiencing above error with my SQL Server 2008 R2 instance when trying to connect and spent about 2 hours for sorting it out. It said that the instance was being upgraded but I was 100% sure that no upgrades were added. I searched for this error and almost all who have experienced the same had accepted that this was due to an upgrade and almost all cases had automatically been resolved within few minutes.

As the next step, error log was checked and found the reason;

2013-10-06 18:30:15.89 spid7s      Error: 5133, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      Directory lookup for the file "D:\Databases\Relational\2008R2\temp_MS_AgentSigningCertificate_database.mdf" failed with the operating system error 3(The system cannot find the path specified.).
2013-10-06 18:30:15.89 spid7s      Error: 1802, Severity: 16, State: 1.
2013-10-06 18:30:15.89 spid7s      CREATE DATABASE failed. Some file names listed could not be created. Check related errors.
2013-10-06 18:30:15.89 spid7s      Error: 912, Severity: 21, State: 2.
2013-10-06 18:30:15.89 spid7s      Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 598, state 1, severity 25. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting.

As per the log, server still looks an old path for databases. I had changed the default database path recently but not today. However now it started looking the old path. Reason could be hibernating the machine without shutting down. Now how can I instruct SQL Server not to look for this path?

As per the search I made, there are two locations to be checked;

1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\Setup
• DefaultData string value
• DefaultLog string value
2. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.<<instance name>>\MSSQLServer
• SQLDataRoot string value

In my case, path in the first key was wrong. Issue was resolved once the string value is updated with the correct path. You may experience the same and this could be the solution ....