Easy Process to Repair SQL database Corruption

Summary: SQL Database corruption can be a nightmare for any organization, especially for the DBA who has to fix it and revive the data on the server. Understanding the database, knowing about corruption causes and staying aware of handy solutions is the only way to keep afloat.

If you are a database administrator, you know you are expected to detect database errors in time and fix them before they cause any loss of data. Database corruption errors might not occur all the time, but when they do, they take down a lot with them. Sadly, there is not much you can do to prevent database corruption altogether. It is one of those random things that just happen. However, the number of times database corruption occurs is quite a few. Therefore, one thing to keep in mind if you are observing these problems too often would be to start planning the migration of all your critical data to new hardware. After all, prevention is better than cure, isn't it? Additionally, keep yourself updated with knowledge of techniques to repair SQL database corruption. You might also want to keep out an open eye for server recovery products and services. That’s exactly what we’re covering in this post.

Understanding & Undertaking SQL Server Corruption Repair

The topological structure of the database is referred to as the logical consistency of the data within it. With logical consistency, the database is able to allocate spatial elements to data without any contradiction. The logical consistency is translated into rows and columns, with each cell in the table storing individual data entities. This translation refers to the physical consistency in a database. The harmonious relationship of logical and physical consistency of a database translates into data integrity: clean, uncorrupted data for an entire life cycle; a DBA's dream.

The crucial DBCC CHECKDB command

As a DBA, if you come across the unfortunate event of SQL database corruption, it is essential to know that there’s a lot you can do to try and revive the server database. The primary command that can help you with this is DBCC CHECKDB. The DBCC CHECKDB command paired with useful switches helps execute a number of in-built repair routines to identify and fix minor issues within the database. If the problem holding up your SQL server’s working is a minute one, DBCC CHECKDB can fix it with ease. And it usually achieves this through a combination of the below mentioned tasks:

1. Runs DBCC CHECKALLOC on the database
2. Runs DBCC CHECKTABLE on every table and view in the database
3. Runs DBCC CHECKCATALOG on the database
4. Validates the contents of every indexed view in the database
5. Validates link-level consistency between table metadata and file system directories and files when storing var-binary (max) data in the file system using FILESTREAM
6. Validates the Service Broker data in the database

DBCC CHECKDB Switches

Much of the DBCC CHECKDB command’s power comes from the switches that are used in conjunction with it. These switches amplify its power and help it repair SQL database corruption efficiently. Out of all DBCC CHECKDB switches, the following 3 play a vital role in SQL server corruption repair.

ALL_ERRORMSGS

This argument of the DBCC CHECKDB command lists all the error messages of each object in the database and sorts them by their object ID. You can use it to view all errors in the database and then eliminate those errors one by one. 

Note: The ALL_ERRORMSGS argument does not sort errors of objects in the tempdb database. 

EXTENDED_LOGICAL_CHECKS

If you are unable to pin down the error, use the EXTENDED_LOGICAL_CHECKS argument with the DBCC CHECKDB command. This command performs logical consistency checks on an indexed view, XML indexes, and spatial indexes, if the compatibility level is 100 ( SQL Server 2008) or higher. 

DATA_PURITY

There are some columns in your database that are different from the rest of the columns in terms of validity and range. While column-value integrity checks do not perform this data discrimination, keep in mind that if your database has been upgraded from an earlier version, it is best to run the DATA_PURITY argument with your DBCC_CHECKDB command. The good news is: you only have to run this command once. The next time you run the DBCC_CHECKDB command, all your data will be saved from discrimination.

In addition to the above three PHYSICAL_ONLY, REPAIR_REBUILD, and REPAIR_ALLOW_DATA_LOSS are also powerful switches that perform dedicated tasks and help DBCC CHECKDB command execute repair operations successfully.

What to do if DBCC CHECKDB can’t repair SQL Database Corruption?

In case running the DBCC CHECKDB command becomes futile, you can depend on reliable third party SQL database repair tool to fix SQL database corruption. This powerful software performs an excellent job of repairing not just minor, but also major corruption within SQL database and helps you recover all database components safely and accurately.

A few last words of advice

With these tips and tricks in mind, the next time your database faces a corruption issue, you will know how to protect as much of your data as you can. Better still, we hope that you never have to deal with a data corruption issue. To always be on the safer side, keep backing up your database regularly.

»»  Read More...

Query Optimization in SQL Server Database

In the last post, I have discussed 10 ways to prevent SQL database corruption and now, I am going to discuss query optimization in SQL Server database.

Microsoft SQL Server performs most of the activities by query execution. The database management system helps to get expected results by the query and this process called optimization. The most frequent queries used with a SELECT command for data retrieval and it required optimization. We should think that not only SELECT queries need the optimization but also other objects like index and view.

How to perform SQL Server query optimization

There are few techniques by which, you can improve the query performance, and these are:

• Improvement in Indexes
• Use of query optimizer
• Understand the response time

Let us discuss them one by one.

Improvement in Indexes

SQL database supports different types of indexes like Clustered index, Nonclustered index, Unique index, Columnstore index, Filtered index, XML index, Full-text index, Spatial Index, Index with computed columns, Index with included columns, etc. Now, user has to create a useful index to achieve the better query performance. To create the useful index, you should have the understanding the use of data. By useful index, you can get the expected result with fewer disk I/O operations.

Examine the query performance first and if, it is taking a long time to execute then add an index with the query and rerun it. If an index is not helping then, remove the index. Few queries strongly support indexes. For example, SELECT queries perform very well with most of the indexes. The DML (Data Manipulation Language) commands like INSERT, UPDATE and DELETE are slow because most of the indexes maintain the operation. In other words, if your most of the queries are SELECT then, you can improve your performance in good manner, and if, they are DML operations then, you should be conservative with the indexes.

Use of Query Optimizer

The two major component of SQL Server database engine is storage engine and the query processor. Storage engine reads the data between disk and memory to maintain the data integrity. The query processer receives all the queries and creates a plan to for the optimal execution. After that, complete the execution to deliver the result.

As SQL server uses the SQL language, which is a high-level language. It only works on what data to get from the database not the algorithm process to do it. Now, for each query, the query process create the plan for best and fast execution.

Understand the Response Time

As a SQL database user, you should be aware of the response time and total time of the result. Response time is the time in which the query returns the first result. Total time is the time to return all the records from the query. Look on the following image to check SQL server execution time.

 After executing the query: SELECT* from subtext_config, you can get the Total SQL server execution time and the number of records.

Suppose the query return 1000 records and you need first 50 records. In this case, you will not think about how long time the query will take to return 1000 records instead, you will think about the first 50 records quickly so that, you can settle the list.

Conclusion: These are the major point of using query optimisation techniques in SQL Server. Choose any techniques to make you maintain the database integrity. I hope this will help you to know about the Query Optimization in SQL database.

»»  Read More...

10 Ways to Prevent SQL Server Database Corruption

In this post, I want to walk through the 10 ways to prevent the SQL database corruption. I am so much aware of the corruption situation. It is a nightmare for every DBA. It will turn into permanent data loss if you do not take the proper action to fix it. Always check the errorlog first to check the corruption point and after using DBCC CHECKDB command, you can find the exact reason of it.
You can also protect your database by following tips.

1. Always maintain backup: backup is the key to the disaster plan strategy. A good DBA always takes care of the database backup and maintains it. Database backup should be working, and you can check it by restoring your database. It also helps to protect you from a database upgrade or other system problems.

 The organisation, which works on SQL Server 24/7, always take the backup of with the active backup agent. If you will try to take the backup of the running database, then there is a high chance of corruption in your database.

2. Query optimization in SQL Server: Query optimization becomes very essential when your upgrade your SQL Server. You need to tune the performance of the individual query. It is important as the other aspects of SQL Server installation. If your server is working on a powerful hardware then, it can be affected by queries. A bad query also called “Runway Query” can be the cause of database corruption. 

3. Proper shutdown and restart the services: I read on few forums where users were asking about the safe shutdown steps because, improper shutdown effects on the running services. There are the clean shutdown scenarios:
• Stop the SQL Server by using service console.
• Shutting down the SQL Server
• Shutdown command in SQL Server Management Studio.

There are the improper shutdown scenarios:

• Shutdown with NOWAIT
• Cable issue
• Killing the sqlserver.exe from task manager
• Drive failure where SQL database resides.

4. Hardware maintenance: Hardware fault is one of the common reason of database corruption. It would be related to Disk, controller, CPU or, memory modules. As SQL Server depends on the disk subsystem, so, the minor issue in the hardware can corrupt the database. If you ever found any issue in the hardware, then please call or engage your hardware vendor. In these type of cases, the vendor checks the drivers, firmware, BIOS version to check their performance with the current database version. They hold the hardware diagnostic tools to find the exact issue of the hardware.

5. Database normalization: Database Normalization is one of the most stronger parts of the database design. This is the technique to organise the data. Without normalizing the database, the database will be slow, inefficient and inaccurate. It is also possible that you will not get the data you expect if you ignore the normalization. The primary way to organise the data is a table. The database table is like Excel spreadsheet, where you can manage the data in the row and columns. In the data warehouse, multiple updates happen periodically if, data will be normalized so, the load time will be less, and it will help reduce the response time and prevent the database from the corruption.

In database normalization, we focused on ACID property. We maintain the atomicity, consistency, isolation and durability of the database. It plays a primary role in transaction operations.

6. Periodic check and repairs of DB: Keeping the database healthy is also DBA’s responsibility. Here is a list of few “must do” task which you may perform for a periodic check for your database:
• Manage auto-growth correctly.
• Avoid schedule shrink operations.
• Turn-on the instant file initialization. It skips the zero writing step and immediately allocates the space for the data file.
• Detect and remove the index fragmentation.
• Turn on the page checksum.
• Maintain a regular process to run DBCC CHECKDB.
• Always keep the full database backup with this, differential and log backup for point-in-time recovery.

7. Disk space management: Always pay attention to the disk space and manage it properly to avoid the corruption. I am just going to share a scenario, where user  created the table and got the following error message:

“Msg 1105, Level 17, State 2, Line 18
Could not allocate space for object ‘dbo.stutable’ in database ‘MySchool’ because the ‘PRIMARY’ filegroup is full. Create disk space by deleting unneeded file, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.”

After checking the local disk space, they found the 0 bytes free space.

In this case, you should check the auto growth setting for data and log files of the affected database. It is also possible that you find the setting turn-off, which means, either one or both files reached to their maximum size.

To face this kind of situation, some users already creates the placeholder file. These are the larger file, which keeps the store on the disk.

8. Update and optimize SQL server: Every update or version of SQL Server comes up with few new features, changes in the existing features and, strong fixing for a bug in the database. You should update the SQL Server to take the advantages of more features. It also improves the scalability and stability.

9. Scan the SQL Server for virus and malware:  Any virus or malware can be the reason of system crash and SQL Server performance degradation. Purchas the best anti-virus software for your system protection from any bug, viruses and malicious activity by Trojan. These viruses can also steal your personal information and downloads the other malware to the system.

10. SQL Server platform issue: One reason could be SQL Server platform issue. Commonly, the third-party driver or firmware creates a problem in performance. To fix it, you need to determine the where the corruption is it.

There are the 10 most important ways to prevent your database from corruption. Whenever you stuck in the corruption, then take the help of SQL database expert or use a secure third party SQL database repair software to repair the corrupt SQL database.

Before I conclude to this post, I would like to request all the readers to take care of these 10 points in mind.

Conclusion
I dealt with many time with SQL database corruption, and sometimes, the reason was very silly. These silly mistakes do not acceptable from any professional. So always, focus on these points to prevent your database from corruption. These steps will help to prevent your database from corruption or failure.

»»  Read More...

Solution for SQL Database is in Use Cannot Restore Error

Problem:
SQL Database is in use could not be restored error occurs when the user tries to restore the database from backup file. It can appear due to many reasons like; connection issue, SQL database service issue, user account problem and, database corruption.

Here is some sample for the error message that you may get while restoring your database from backup:

In T-SQL

Msg 3101, Level 16, State 1, Line 5
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 5
RESTORE DATABASE is terminating abnormally.

In SSMS

Solution:

• Close the existing connection of the database: You can use below code to close the existing connection.

USE master;
GO
ALTER DATABASE Your_Database_Name
SET SINGLE_USER
WITH ROLLBACK IMMEDIATE;

You can also check the existing connections in SSMS. Check the “Close existing connections to destination database” in Restore Database option.

After completion of it, don’t forget to set your database into the multiuser mode by following commands.
ALTER DATABASE YourDatabaseName
SET MULTI_USER;
GO

• Restart SQL Server Service: Restarting the SQL Service can also fix this problem because service controls the running process. You should maintain service properly to run the SQL Server successfully. See the steps to stop the SQL Server service:
• In the Start Menu, go to All Programs->Microsoft SQL Server->Configuration Tools and click on the SQL Server Configuration Manager.

• Now Expand the service section and select the instance of SQL Server as shown in the figure below:

• Right-click on the instance and stop the service or use the four buttons on the toolbar for Start, Pause, Stop and, Restart.

• Change the Default Database to Master Database: Sometimes, the user cannot login due to default database presence. There are many reasons of this, corruption in database, offline database, and user access problem after renaming the database, etc. to fix this problem. Open SQL Server Management Studio (SSMS) to change the default database to master database.
• Open SSMS, File->Connect Object Explorer

•  Now fill all the details and click on the Options>>

• In the Connection Properties section, select master at the place of default and click the connect button.

Users, who are using SQL Server 2008, has another option to change the default database to master database by sp_defaultdb

EXEC sp_defaultdb ‘sqldb’,’master’
Or
ALTER LOGIN sqldb
with DEFAULT_DATABASE = master

Where sqldb is my database name. This option won’t work on the later versions of SQL Server 2008.

• Take Another Backup: It's possible that, you have an issue with .BAK file. In this case, you can create another backup of your database and try to restore from the new backup. To create the new backup:
• Using SSMS:
• Open SSMS, right click on the database
• Select Task->Back up.
• Select backup type as “Full”
• Select “Disk” as the backup destination
• Click on “Add” button to add file and click “OK”.
• Click “OK” to create the backup
• Using T-SQL:

BACKUP DATABASE AdventureWorks TO DISK = 'C:\AdventureWorks.BAK'
GO

• One more solution for this Error: If the problem still exists and you are unable to restore the database from new backup then the problem should be in the backup file. In this case, you can download Stellar Toolkit for MS SQL. The toolkit consists three software: SQL repair, SQL backup recovery and, SQL password recovery. You can restore the SQL database from the corrupt .BAK file and supports MS SQL Server 2016 and all lower versions. Check the steps to repair the corrupt .BAK file:
• Download and install the software.
• Select .BAK file or search in the particular folder or search in the particular drive.
• Now click on the “Scan” button and start scanning process.

Advantages of the software:

• Preview facility is available in the demo version.
• Restores all database objects.
• To repair the file and see the preview, there is no requirement of SQL Server installation on the machine.
• Multiple saving options like XLS, HTML and, CSV.

Conclusion: These methods are very helpful and easy to understand. To fix this problem, you need to know the exact reason behind it.

Too many people stuck in this problem and ready to pay for it. Eventually, the tool, which I discussed above, is like a one-time investment for SQL database users with free support.

After reading this blog, you have the solutions to fix SQL database is in use could not be restored error and if, you know more about its fixes then share with me. I am always eager to learn new things. 

»»  Read More...

Understand Microsoft SQL Server Error 8946 and Fix it

SQL database error 8946 is related to the invalid page header. Malware infection, unexpected system shutdown, hardware issue are the main reasons for this error message. I read about this error on blogs and forums, and the user has different experience with it. A user complained that when he tried to run DBCC CHECKDB command then got the following error message:

Msg 8946, Level 16, State 12, Line 1
Table error: Allocation page (1:13280496) has invalid PFS_PAGE page header values.
Type is 0. Check type, alloc unit ID and page ID on the page.

Before moving to the error message, let’s talk about the PFS page.

PFS (Page Free Space) Page is the second page (Page Number 1) in the data file which is followed by the file header (Page Number 0). PFA page doesn’t have bit-map, it has byte-map. PFS interval is 8088 pages or about to 64MB.

DBCC CHECKDB uses PFS pages to determine the allocation status of pages. A User can repair SQL server error 8946 by two ways:

• Restore from the backup
• Repair PFS pages

Restore from the backup

Updated backup can save the work. Yes, this is first method that a user should try to fix this problem.

• Open SQL Server Management Studio.
• Click on Database folder and select Restore Database option

•  Under ‘Source for Restore’ section, select ‘From device’ option and press the ellipsis button.

• Set File as backup media and click on Add button.

• Select the .Bak file and press the OK button.

In database dialog box, select the database name which you want to restore. If database is already existing, it will replace data from backup otherwise create the new one. Select the restore point which you want to use.

By this approach, the database can restore from backup.

Repair PFS page

A user cannot delete PFS page because it has a fixed part in the database. PFS pages cannot be rebuilt because it is not possible to identify the which page is allocated and which is not. I read Paul Randal’s blog and he said; he experimented various algorithms to rebuild PFA page with the optimistic or pessimistic setting and re-run the various consistency checks, but all required a very long runtime. Read here

A third-party tool

One more solution to fix this error is an SQL database repair tool. Stellar Repair for MS SQL software has the capability to solve this error. Download and install the software. Select the corrupt MDF file, click the Repair button to start repairing process.

Conclusion
We discuss the solutions here to fix Microsoft SQL database error 8946. I hope users will fix it after reading these methods. Always main the backup of database because it is the best way to save important data. You can share your thoughts and ask questions related to this error and I will try to provide you the best answer. 

»»  Read More...

Methods to Recover SQL Database Object and Stored Procedure

A Database object is the database structure which is either used to store or reference data. Stored procedure is the collection of Structured Query Language (SQL) statements and is stored in the compiled form in the database. The stored procedure could be shared by more than one program in the SQL Server database. After exercising so many precautions such as database full recovery model, updated database backup, and log backup, the chances are that your data can be corrupt.

Recover SQL Database object: A full database backup contains all objects; however, it cannot restore the existing database. To fix the problem, check the following solutions:

1. Using SQL Server Management Studio

Follow the steps below:

• Restore the backup on the server (Try on Test server)
• In next step, User needs to create DDL script. Right click on the object which is present in SSMS object explorer.
• Go to Script Stored Procedure as->Create To->New Query Editor Window
• Run the script against the original database

2. Using cache: The recently executed object could be present in the cache so, there is a chance to recover it from the cache. Execute the following queries:

• Cached.refcounts
• Cached.usecounts
• Cached.objtype
• SQLText.dbid
• SQLText.objectid
• SQLText.text
• Query.query_plan

         From:

• sys.dm_exec_cached_plans Cached
• CROSS APPLY sys.dm_exec_sql_text(Cached.plan_handle)SQLText
• CROSS APPLY sys.dm_exec_query_plan(Cached.plan_handle)Query 

From the result, a user can select the object and copy the text column. The main advantage of this process is that the user would not require a full backup.

3. Using fn_dblog: fn_dblog () is an undocumented function which is used to view the transaction log record for the current database. This function accepts two parameters:

• LSN (Log Sequence Number) is the first parameter. The user can also write NULL at this place.
• Second parameter shows the ending of LSN. At this place, Null is also accepted by the SP.
• Execute the fn_dblog function to recover the object:
• SELECT * FROM sys.fn_dblog (NULL, NULL)

As a result, the user will get the complete database transaction log data. It is present in the 129 columns. To get more narrow result, execute the following;

• SELECT * FROM sys.fn_dblog (NULL, NULL)
• WHERE [transaction name] IN ('DROPOBJ');

It will be the untenable form. To recover the object, users need to be familiar with this format and characteristics.

4. Using a third-party software: To easily recover your MS SQL Database objects, Stellar Repair for MS SQL tool would serve the purpose. Know its salient features by simply downloading the trial version of the software, selecting the .MDF file and, clicking on the checkbox “Include Deleted Records” in the interface. Its preview section allows users to see the deleted objects, check, and recover them. To save your object, you need to register the software.

Conclusion: You may opt for any of the above methods to recover the SQL database object. However, as the first method requires a full database backup, users can opt for the second method which does not require a full database backup. I hope you find all the above methods useful. For queries, you can drop the comment.

»»  Read More...

How to detect and fix oracle database file corruption

You are enjoying holiday with family and your colleague start calling you about the data file corruption with no clear reason then, what you are doing to do next? You will handle the situation on call or may not.

Data file corruption comes under the media recovery. To start the recovery process, it is very important to determine actual reason of it.

Determine the actual reason of corruption: Determining Which Files Need Recovery

• View V$RECOVER_FILE to determine which data files need recovery. 
• View V$ARCHIVED_LOG for a list of all archived redo log files for the database. 
• View V$RECOVERY_LOG for a list of all archived redo log files which required for the recovery.

I am discussing data file corruption here so, use V$RECOVER_FILE command to find actual reason:

SQL> SELECT * FROM V$RECOVER_FILE;

The following query will display the status of files that needed media recovery with File ID, Status (ONLINE or OFFLINE), Error and Time etc.

Note: The Error filed shows NULL if a reason is unknown and OFFLINE NORMAL if recovery is needed.

Now, you get the corruption. Now, it’s time to take appropriate action to repair corrupt file.

• Restore backup of damaged data files: If one or more data files are damaged by media failure then you must restore the backup of damaged data files before starting the recovery process. If you are unable to restore the damaged files to its original location in the memory, then you have to assign new locations of these files to the control file of associated database. 

• Re-Creation of data files (Backup is unavailable): If backup is not available then, you can also recover the data file if:

• The Control file contains the name of the damaged data file.
• All archived log files are written the creation of data file.

To re-create the data file, run the following command. This command will create an empty file same as damaged file.

ALTER DATABASE CREATE DATAFILE ‘DISK1:FILE1’ AS ‘DISK2:FILE2’;
The old location of data file is DISK 1 and file name is FILE1 and new location of a data file is DISK2 and file name is FILE2. Now, you can perform the media recovery on the empty file.

RECOVER DATAFILE ‘DISK2:FILE1’
Note: CREATE DATABASE clause cannot recreate the data file of SYSTEM tablespace.

• Restore achieved redo log files: Use query V$LOG_HISTORY and V$RECOVERY_LOG to determine that which archived redo files you need. You need this information from time when the data file was added to the database if, there is no backup of the data file is available.

• A third party tool can be helpful: Yes, a third party tool like Stellar Phoenix Oracle Database Recovery is able to repair corrupt data file (.DBF) of oracle database 9i, 10g, and 11g.

Final Words
I hope there is no doubts on the steps that I have mentioned above to repair the oracle database. One advice to all the readers is that always maintain a backup copy of your database. It provides an easy way to restore the database.

»»  Read More...

SQL Anywhere Assertion Failed Error – Cause and Fixes

You are working on SQL Anywhere database and suddenly your database gets corrupt due to software related errors. An assertion failed error message comes up which reads as: *** ERROR *** Assertion failed: ###### (xx.x.x.xxxx). What are you going to do next?

Assertion failed errors are a commonplace these days when working on SQL Anywhere database. While you think that restarting the server would fix the error, however, this may not be the recommended solution. Let’s look at how we can fix SQL Anywhere assertion failed errors:

SQL Anywhere database can show the assertion failed error due to several reasons like hardware or software failure, unexpected operations etc. This error works as an indicator of issues in database. Let’s discuss more about this error.

What is an Assertions failed error?

Assertions are checked conditions in the SQL Anywhere database, which helps prevent database during corruption. when an assertion error occurs then database server stops the processing of client request and return an error message. It happens in SQL Anywhere 12 and lower versions.

In this error message, a six-digit number is present which is the assertion number, and every error message is associated with an assertion number. This number helps find the actual reason of the error. Some numbers following in the bracket to an assertion number indicates the version and build of the database server that asserted.

Given below are more details about the error (in text form):

The basic syntax of Assertion failed error is:

*** ERROR *** Assertion failed: ###### (xx.x.x.xxxx)
Cause of the assertion failed error

Some examples of assertion failed errors are given below:
*** ERROR *** Assertion failed: 101412 (xx.x.x.xxxx)
Page number on page does not match page requested

*** ERROR *** Assertion failed:100903 (xx.x.x.xxxx)
Unable to find table definition for table referenced in transaction log -- transaction rolled back

*** ERROR *** Assertion failed: 201116 (xx.x.x.xxxx)
Invalid free list index page found while processing checkpoint log -- Transaction rolled back

*** ERROR *** Assertion failed: 201819 (xx.x.x.xxxx)
Checkpoint log: invalid Bitmap Page - Transaction rolled Back


*** ERROR *** Assertion failed: 201819 (xx.x.x.xxxx)
Page Number ON Page Page requested does Not match - Transaction rolled Back


*** ERROR *** Assertion failed: 200502 (xx.x.x.xxxx)
ON Checksum failure Page 23 - Transaction rolled Back

How to deal with assertion failed errors?

1. Note down the assertion number from a log file or message window of the database server to determine the actual cause of the assertion.
2. In case of SQL Anywhere 16 database server assertion, shut down the database server immediately if server is still running. This, in turn, will protect other users who are working on the same server.
3. Always take a backup copy of your database (.db file) and transaction log (.log file) because you can restore the database from backup. It is also very helpful for analysis purpose to know the actual reason of the error.
4. Try to restart the database server with database files.

How to handle corruption in SQL Anywhere database?

• The best way to deal with database corruption is database backup. If you do not have log backup then, you can try to start the database without the transaction log file. 

Note: This option only works when; the database is not involved in replication or synchronization.

• If you have a log file, then without missing any details then there will be no data loss in the assertion failed situation. To recover the database, shut down the database, rename the old log file and restart your database by using the following command:

dbengX  -f  database.db

where X is the version of database.

• Automatic recovery process: When database server shutdown normally then the, database server performs a checkpoint operation so that all the information of database kept in the database file. it is called clean shutdown. When you start database then, database server checks the reason of the last shutdown. If it is not clean then, database server performs the following steps to recover the database from failure:
• Recover most recent checkpoint
• Apply changes made since the checkpoint
• Roll back the uncommitted transactions 

• Using a third party software: If your database file (.db) is corrupted and you tried all the options to repair it but, file id still corrupted then, you should try Stellar Phoenix Repair to SQL Anywhere software to fix the corruption of .db file. It worked on SQL Anywhere versions 9.x, 10.x, 11.x, 12.x, and 16.x.

The Final Word: The above are the recommended solutions for fixing the assertion error in SQL Anywhere database; however, it is essential to, know the exact reason of error and then using the correct steps to fix it. Always take a backup of your data as this serves as a contingency plan in case of data loss. recover the . I hope these methods have been helpful. In case you have any comments or suggestions then, you can share with me.

»»  Read More...

SQL Database States – Recovery Pending, Offline, and Emergency

SQL database has different recovery states such as Online, Offline, Restoring, Recovering, Recovery Pending, Suspect, and Emergency. SQL database is mostly in one specific state. State_desc command and DATABASEPROPERTYEX are easy ways to check the database state.

SELECT db_name () AS DatabaseName, DATABASEPROPERTYEX('master', 'Status') AS DBStatus

This is the output. In remaining section of this blog, I will discuss “SQL Database Recovery Pending” state, Offline state, and Emergency state of SQL database.

• SQL Server Recovery pending State

The SQL Server has faced resource-related error during the recovery process. Database is not damaged but files may be missing or limitation in system resources can be the cause which may be preventing database from starting. In this case, database needs an additional intervention by user or DBA to complete the recovery process.

How to fix: I created a database “Demo” to describe the solution. First, I ran DBCC CHECKDB command with NO_INFOMSGS

DBCC CHECKDB(Demo) WITH NO_INFOMSGS

My output was:
Msg 945, Level 14, State 2, Line 4
Database ‘Demo’ cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

In next step, I checked the error log of my database. To check the error log, click on SQL Server Agent->Error Logs. Click on Current error log. Select SQL Server and check its Current option. After clicking on it, a log file summary will be open. Apply filter to check information of the  particular database.

After applying filters, I was able to detect the actual reason for this error. Error log message was:

FileMgr::StartLogFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file ‘C:\Log\Demo_log.ldf’. Diagnose and correct the operating system error, ad retry the operation.

I checked the log file of my database.

I renamed it as Demo_log and restart SQL Server. I fixed this issue successfully.

Read More: SQL database in recovery pending 

• SQL Database in Offline State

In this state, database is unavailable and needs user action. Set database in the offline state when it is not required so as to make database accessible to other users. Users can create an SQL Server database in two ways, using SSMS and using T-SQL script. In SSMS, Right Click on database and go to Task option. Select Bring Online option from the list. Use the following T-SQL script to make database online:

Alter Database [database_name] set online

• SQL Database in Emergency State

Only a member of SYSADMIN can set the SQL database in EMERGENCY state. The database remains in single user mode and is able to repair and restore. This state is in read-only mode and nothing can be written on the transaction log file. REPAIR_ALLOW_DATA_LOSS is the only option which works in EMERGENCY state. By using it, damaged data or indexes may be removed from the database to make database physical consistent.

EMERGENCY state is useful when SQL database in SUSPECT mode. A database goes into SUSPECT mode due to the following reasons:

• Hardware failure
• Damaged or corrupted log file.
• System failure
• Virus in SQL Server system
• Lack of disk space
• Improper shutdown

Conclusion: These are few recovery states of SQL Server Database and their solutions. Sometimes, the database states related issues becomes very complex when user fails to recognize its reason.  I hope these solutions will help you.

»»  Read More...

MS Access database corruption – causes and fixes

Causes:

Hardware failure issue : It is the most common reason for database corruption even, I can say in 80-90% cases. It affects the database integrity which is related to networking and hard-drive.

Software clashes: sometime the 3rd party plug-ins and programs can be the reasons of MS Access database corruption. This kind of applications has their own rights and programming behaviour and they also run in the same memory space as the database.

 Access of multi user: MS access database works on JET database engine, which is a file-based system. When multi-users work on the same time then jet database engine uses lock file (.ldb file) to manage the synchronization between them. By doing this, the speed of the jet database engine becomes slow. The session disconnects due to time-out failure.

Fixes

Always take a backup: The necessary thing which can help you to get your database back is, it’s backup. Always take the backup of your database according to your schedule. If you do not have the database backup and suddenly corruption occurs then, take the backup immediately. This is the first thing which you should do and if you are a DBA then it is your duty.

In case, if you have the previous backup of your database then you can get back your database structure because corruption is the part of Access database structure rather than jet database format.

Delete the .LDB file: This file is used to synchronise multi-user file operation therefore, it doesn’t need to repair MS Access database. If a user won’t delete this file, then it is high chances that user or program will be logged into the database. With this, you should close all the open instances of MS Access database.

Compact & Repair: This command prevents your database by following problems and file growing larger is one of them. Access database file becomes larger after every operation and this command makes the file smaller by removing the unused space from it.

Note: It doesn’t compress the database.

If the database file is in the shared network and multi-users directly working on it then there is a small risk of the corruption. It will become riskier when users edit the data in the memo field.

Microsoft JET Compact utility: Jet.Comp.exe is a utility which is developed by Microsoft to correct minor corruption of the database. It is the most successful built-in feature of MS Access to repair Database corruption. You can read more about JET compact utility here: http://support.microsoft.com/kb/273956

Decompile the MS Access VBA Code: To decompile the VBA code, run the following command from the command line.
C:\Program Files\Microsoft Office\Office\MsAccess.exe /decompile C:\DatabasePath\FileName.mdb
Note: it is an example, please change them path according to your system.

 

Third Party Tool: Stellar Repair for Access is able to repair corrupt .mdb and .accdb files. It is 100% secure software for MS Access database repair. It works on MS Access 2016,  2013, 2010, 2007 and other older versions

Conclusion: These are the few points which you should remember. The database corruption can occur by a small mistake so, always take care of it.

»»  Read More...