Sometimes, an MSSQL database becomes inaccessible for executing queries while remaining accessible for viewing data.
Timing is crucial when a database is unavailable for querying. As long as this issue exists, application users cannot edit or update the database, which means operation disruption. In this case, it’s important to have a full backup available to restore the database to as close as possible to its previous state and reestablish querying functionality as soon as possible.
AimBetter recognizes this problem immediately when it happens, saving time and ensuring business continuity.
Symptoms
Users receive error messages when trying to work with the database’s tables. The system cannot accept new updates, inserts, deletions, and other commands.
Impact: Critical
Inaccessible databases imply working time and data loss, disrupting the business flow.
Expected behavior
Databases should not be in a situation where they are inaccessible for querying. This alert must be investigated, especially to check data integrity and functionality.
Find out how you can save hours of work when investigating the root cause of this issue.
Possible causes
1- Database corruption. Priority: Critical
Database corruption refers to errors within the database files or structures that prevent the database management system from accessing or manipulating data properly. Corruption can occur at various levels within the database, such as the database itself, indexes, tables, or data pages. A corrupted database might make it unavailable for running queries.
As a result, running queries with the corrupted database will cause failure, slowness, or time-outs. Transactions may be automatically rolled back. In severe cases of corruption, data loss may occur.
SQL Server may report that a table in the database cannot be accessed because it has become corrupted.
Problem identification:
Users might complain about immediate errors when trying to run processes. Several error messages of corrupted tables may be displayed during an operation.
- Use tracking tools such as SQL Server profiler or extended events to collect information about exceptions. Make sure to put appropriate filters to track the exact exception that needs to be investigated. You should look for query time-outs or table-corrupted categories. This task might take time, won’t be provided with historical data, and should be done by a professional DBA.
- Check error logs and event logs for indications of database corruption.
- Use built-in tools like DBCC CHECKDB (for MS SQL Server) to perform consistent database integrity checks on the database.
- Using all checks that were done, examine the error messages and try to see if there are standard details such as host, database, and query involved to ensure you are tracking the same issue.
Aimbetter provides complete insight into the database server. You will be notified when the database is unavailable for query and exceptions exist.
Data is collected 24/7, and a monthly history is available.
Each exception involving an SQL query is attached with database, host, user name, client, and process ID, enabling convenient issues tracking.
Recommended action :
If possible, use utilities that allow you to repair corrupted data pages or structures. If unsuccessful, restore the database from the most recent backup file taken before the corruption occurred. This ensures that data integrity is restored to a known good state, yet there is a chance for data loss.
Before then, you can rebuild and examine indexes.
If the backup chosen to restore wasn’t close enough to date, implement other backup mechanisms that run more frequently for important production databases. A skilled DBA should do this.
2- Locked tables or Deadlocks. Priority: High
A Database’s table won’t be available for query in cases where transactions are open in the database or a query is blocking other queries.
This case is less urgent than cases when the whole database is not operable since usually, only one or a few tables are locked.
A deadlock occurs when two or more processes compete for exclusive access to a resource but cannot obtain it because some other process prevents it. As a result, one of the processes is chosen as the “victim” to do a rollback and to be terminated.
You can read more about how to investigate these issues in the following articles: SQL – Blocking Queries, SQL – Deadlock.
Recommended action :
Terminate problematic queries after identifying them and/or improve performance by reviewing the execution plan of the query and making it simpler so the running time decreases.
Make sure the scheduling of processes doesn’t collapse each other in the meaning of source use.
Ensure the database settings fit the SQL version and the required business application.
3-Overloaded or inefficient hardware resources. Priority: Medium
Overloaded server resources such as CPU, memory, disk I/O, or network can slow down query execution. It might happen due to a specific application or many processes running simultaneously.
This may lead to query execution bottlenecks, causing queries to timeout or fail.
Problem identification:
Check if CPU, memory, disk I/O, or network resources are highly utilized. Identify the source of the load and whether it is recurrent. Check if it happens when the database cannot be accessed.
- Check the Task Manager in order to identify the overload of hardware resources.
- Use OS tracking tools such as Performance Monitor to identify which processes use hardware resources. For network activity, use a network monitoring tool to check how much traffic flows through your network and which applications or devices use the most bandwidth. Take into account that most tools help pinpoint when a problem starts, which you can’t compare to previous time frames.
- Check whether the long-running query wait time is for a specific hardware resource and which hardware resource is more utilized than others while the database cannot be queried. You should use tracing tools such as SQL Server Profiler for that. This step might be complicated and take time, and the result probably won’t be precise while checking only online statuses.
Recommended action :
Cancel unnecessary programs that cause high hardware utilization and schedule non-urgent processes at times of lower user demand.
Consider upgrading hardware if possible. Ensure proper network connectivity,
Monitoring would be useful for following up on this issue.
4-Maintenance tasks. Priority: Low
Maintenance tasks are essential for SQL server databases to operate well. They are important for the database’s functionality and integrity, performance optimization, and disaster recovery planning. These tasks might include index rebuilds, database backups and restores, statistics updates, integrity checks (DBCC CHECKDB commands), and more.
Problem identification:
Check if maintenance tasks are running when DB is unavailable for querying.
- In Microsoft SQL Server Management Studio, select SQL Server Agent. In the Job Activity Monitor, check for maintenance jobs running now or in the past. If you can’t find them with these manual checks, you can look for them by viewing event logs or routine activity tracked by the SQL Server Profiler. Both these options might be time-consuming and demand DBA skills.
- Track with SQL Server Profiler events when DB is unavailable to query and compare the time frame if it’s the same time when the maintenance tasks are running on this database. You should wait for events to accumulate and ask a DBA to perform this task.
Recommended action :
Maintenance tasks are important. You just have to make sure they’re done with enough system resources and outside business working hours so they won’t cause more harm than help.
5-Settings are not compatible with the required version of the SQL plan. Priority: Low
Incompatible versions in a database environment can cause queries not to run due to differences in features, syntax, or supported operations between different versions of the database software, client tools, or other components.
Problem identification:
Error messages about an unfit version should be raised when a query cannot be run because of an incompatible version.
- Users might complain about receiving the error message; however, when procedures are run automatically or at a scheduled time, users’ complaints won’t help you track this issue.
- In most cases, tracking tools will be needed to identify the problem. For Oracle, you can use error logs or trace files.
- Another tracking tool for Oracle is OEM (Oracle Enterprise Manager). It can be used to identify slow or failing queries, view error messages, or analyze SQL execution plans. Doing it might take time and be done by a DBA who knows how to utilize it wisely.
- Once you identify problematic queries, you should execute them using SQL*Plus to capture error messages returned during execution.
Recommended action :
Ensure that the database version and client tools are compatible and support the features required by your queries. Upgrade what’s needed to fit the database server’s version. Review the queries’ code. If needed, set the database’s version compatibility.
.
