SQL Server Troubleshooting

To diagnose an issue, you must first collect information on the issue. You should also go through the log files to determine the type of system error messages and the existing log records that could assist you in diagnosing the issue.

• The first step to solving an issue is to gather all the facts, like how and when the problem occurs. We need to find out whether the issue is localized to a specific application or its component. We must be aware of when the problem happened and if it is still continuing. Also, We must know the last time the system worked properly. We must determine whether any new changes have been made to the system or apps that could have caused the issue. With all this information, you can start searching for hints that may help us to find out the root cause of the issue.
• It makes sense to be tested in different environments if you have any of them. If the problem exists in only one environment while working correctly in other environments, then it may be a configuration issue. If not, then the problem may be with the data in the environment. In addition, we may try out different application servers or client machines. Sometimes you may find that the set-up or configuration is causing the application not to work. We have to find all the different options of configuration and document which ones work and which ones don't.
• A log file named "ERRORLOG" is generated by SQL Server. Whenever the SQL server starts, a new ERRORLOG file will be generated. By default, SQL Server retains six old error log files with an associated sequence number. By default, the ERROR LOG file is saved in the folder called "Log" in the "...Program FilesMicrosoft SQL Server..." standard folder structure. Locate the log file related to the time in which the issue occurred for the first time. Check for anomalies in the messages generated by SQL Server. If the SQL server finds any changes or issues, it will be logged in the file 'ERROR LOG'.

  Wish to make a career in the world of SQL Server DBA?  Then Start with HKR'S SQL Server DBA Online Training Course!   

SQL Server DBA Training

• Master Your Craft
• Lifetime LMS & Faculty Access
• 24/7 online expert support
• Real-world & Project Based Learning

Explore Curriculum

• To view different logs of the events, you need to use the Event Viewer. Both informative warnings and error events are recorded in the event log. We need to consider all the events that had occurred recently during or before the issue was detected. We must check the "Application", "System" and the "Security" events. 
• If the default option is enabled, on start, the SQL server starts automatically. By examining the default search information, we can find the type of database changes that could have occurred during the period in which the problem was found. In the log folder 'ERROR LOG', the default trace files can also be found. They are named as "log_xxx.trc", in which xxx is a sequential number. We may open such files using Profiler to view recorded events.
• Check the Change log of the organization. Change log is a centralized location that indicates all the changes that were made. If the organization does, it will assist you in determining recent changes. This may give us some hints on why that problem is occurring, particularly if the application which has the problem is the one which was recently modified. If the organization dont have a change log, then we may ask the programmer about the last change to the application.

                                                                                 Lets's get started with SQL Server DBA Tutorial online!

Subscribe to our YouTube channel to get new updates..!

Subscribe

Till now, we have collected the information. The next thing to do is to analyze. Examine the information gathered at every step. Search for anomalies that could support the issue identified by the client or programmer. Look at the situation identified in the first point above and try to figure out how a trace file or log can help us to find the reason for the problem. Examine the information available at every step to see if there is evidence to help us understand the cause of the issue. Once you have completed this analysis, we may be in luck to determine the cause of the issue. 

If these steps fail to provide us with the solution, we need to proceed with further testing and gathering information. We may need to look at the code and also perform a few other tests. The other stages described here represent just a starting point. They may help us to arrange our thoughts on how we could carry out additional tests and collect information that can assist us in solving the issue.

• Talk to the clients and the application programmers and note down the steps they follow, which causes the issue. While a lot of this information may have been collected already in Step 1, it is worth reviewing again. Find out if the problem can be repeated. If it is not repeatable, finding the cause of the problem may be difficult. This involves identifying how the application connects to SQL Server and the T-SQL code currently running. Depending on the issue, we need to develop a set of tests to perform and what information needs to enter during the process to identify what is happening.
   
• Prior to carrying out further tests, analyses and troubleshooting, it is wise to backup the problem database. This backup may be a complete, differential or log backup based on our present database backup strategy and the latest backup status. This will give us a recovery point if we wish to begin refining SQL Server as part of the troubleshooting steps for our diagnostics identified in step 6.
• Divide your test into little logical bits if possible. For test steps which connect to SQL Server, we may consider enabling SQL Server Profiler so that we can monitor the type of T-SQL statements and batches currently running. The Profiler will let us capture the code being executed, which is sometimes different from what the programmer is waiting for, Sometimes the Profiler and the extra steps are all that is needed to reduce what is at the root of the problems under consideration.

                                                                                 Lets's get started with Neoj4 Training online!

Final words:

DBA is the one who is responsible for assisting a programmer and the client in solving an issue. The more complex the problem, the more rewarding it becomes. Perhaps you will find that these steps are not necessarily what you need, which is acceptable. The most important thing is that we need to develop and follow a troubleshooting process for our environment. Possessing a set of questions, tools, and steps will enable us to be proactive in developing an approach that we may use to resolve issues in a timely and orderly manner.

Related Articles:

• SQL Server Interview Questions