Tuesday, February 14, 2017

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.
  1. 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. 
  1. 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.
  1. 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.
  1. 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.
  1. 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.
  1. 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.

  1. 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.
  1. 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.
  1. 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...

Wednesday, January 25, 2017

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 Phoenix SQL Backup Recovery tool. The SQL backup recovery software 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...

Tuesday, November 8, 2016

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 Phoenix SQL Database Repair 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...

Friday, September 30, 2016

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

  1. 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.
  1. 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.


  1. Using a third-party software: To easily recover your MS SQL Database objects, Stellar Phoenix SQL Database Repair 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...

    Sunday, May 22, 2016

    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.
    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...

    Friday, May 20, 2016

    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...

    Sunday, April 24, 2016

    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.



    • 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 thefollowing 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...

    Wednesday, April 13, 2016

    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 Phoenix Access Database Repair is able to repair corrupt .mdb and .accdb files. It is 100% secure software for MS Access database repair. It works on MS Access 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...

    Thursday, March 17, 2016

    SQL Server Filegroup Error – The filegroup is not empty

    SQL Server database consists of two types of filegroups, primary and user-defined. Primary filegroup includes primary files. System tables come under the primary filegroup. User-defined filegroups are those filegroups which created by the user (as it is clear from the name) and user can modify it. One file cannot be the member of more than one filegroup. Every database has a default filegroup and when a table or index is created without specifying the filegroup then they allotted to the default filegroup. If database owner or members do not specify the default filegroup then primary filegroup works as default filegroup.

    Now it’s time to talk about common error related to filegroups.

    Server: Msg 5042, Level 16, State 7, Line 1
    The filegroup 'fg' cannot be removed because it is not empty.

    First of all check the objects that belong to a filegroup. To check them, use this script:

     SELECT  
       
     fg.data_space_id, fg.name,  
       
     ObjectName = OBJECT_NAME(p.object_id), p.index_id  
       
     ,df.name, df.physical_name, [Size] = df.size*8/1024  
       
     FROM sys.filegroups fg  
       
     LEFT JOIN sys.database_files df  
       
     ON fg.data_space_id = df.data_space_id  
       
     LEFT JOIN sys.partitions p  
       
     ON fg.data_space_id = p.partition_number  
       
     WHERE (p.object_id>4096 or p.object_id IS NULL)  
       
    

    After checking the object by this script, you will be sure about the filegroup of the object.

    Solution 1:

    To delete a filegroup, first you have to delete the file associated with it. To do so, execute the following query:

     ALTER DATABASE database_name REMOVE FILE file_name  
     GO  
     ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name  
     GO  
       
    

    If you are still unable to remove the filegroup then, use the next solution to fix the issue.

    Solution 2:
    • Add a new file into the filegroup.
     ALTER DATABASE database_name  
     ADD FILE  
     (  
     NAME=second_data_file,  
     FILENAME = [C:\SQLData\file_name.ndf],  
     SIZE = 1MB,  
     FILEGROWTH = 10%  
     )TO FILEGROUP [filegroup_name];  
    • Now empty the first file
     DBCC SHRINKFILE (first_data_file, EMPTYFILE)  
    

    Note: The EMPTYFILE argument moves the file from the selected file to another file of the same filegroup.
    • Now delete first data file and second data file.
     ALTER DATABASE DBNAME REMOVE FILE first¬_data_file;  
     ALTER DATABASE DBNAME REMOVE FILE second_data_file;  
     ALTER DATABASE DBNAME REMOVE FILEGROUP filegroup_name;  
    

    Note: You cannot add the new file if filegroup is offline because offline filegroup is a filestream filegroup which doesn’t contain data files.
    »»  Read More...

    Friday, January 29, 2016

    DBCC CHECKDB, WITH CHECKSUM & RESTORE VERIFYONLY

    SQL Server database contains different commands for different operations but half knowledge about these commands can make a huge trouble for a DBA. So always take the complete knowledge of commands.

    Well, I was reading a question about SQL Server database commands: DBCC CHECKDB, WITH CHECKSUM and VERIFY ONLY and then I join these three strong commands together to make a new article for the readers. Some users believe that, they can use WITH CHECKSUM option to take the backup of the SQL Server database while this is again half knowledge. Now, we will see the use of these three commands.

    DBCC CHECKDB: It checks the physical and logical integrity of the database objects.

    Why we use DBCC CHECKDB: There are two types of tables in the SQL Server database:

    • Disk-based tables
    • Memory optimized tables

    User can perform the DBCC CHECKDB operation on the database which contains memory optimized tables but it works on disk based tables. Since DBCC option is not available for memory optimized tables then user should take the backup of the database regularly (depends on the work plan) to prevent database from memory optimized tables.

    If your database is corrupted and you are trying to take a backup of it then, you will also get a corrupted backup of your SQL Server database. In this case, you can check the database by WITH CHECKSUM option. When a user successfully create a backup of the database that means there is no corruption in your database. This is one more option to check the corruption.

    WITH CHECKSUM: First of all, the permission to perform read and write on the media (If you are not the member of sysadmin) to a user is must otherwise you will get the permission issue.

    WITH CHECKSUM option is used to test page checksums that exists on the data file pages and these pages backed up during the backup process. If a bad page checksum is found then the backup process will stop automatically. In an emergency, a user can override this by using WITH CONTINUE_AFTER_ERROR. Completion of the backup process is also indicating that, there are no broken page checksums.

    WITH CHECKSUM option during Backup Process: To enable the WITH CHECKSUM option during backup process use the following commends:

    BACKUP DATABASE AdventureWorks2012 
    TO DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
    WITH CHECKSUM;
    GO

    Note:  User can also enable this option by using trace flag 3023. If the trace flag 3023 is turned on that means the CHECKSUM option is automatically enable for the backup command. It is also possible by the SSMS. Go to Options page->Reliability-> Perform checksum before writing to media.

    If a user wants to disable it then, use WITH NO_CHECKSUM option.

    WITH CHECKSUM option during restore process: 

    RESTORE DATABASE AdventureWorks2012 
    FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'
    WITH CHECKSUM;
    GO

    To disable it, Use WITH NO_CHECKSUM option to disable it.

     RESTORE VERIFYONLY: Assume a situation, when a user takes the backup of SQL Server database successfully that means there is no corruption in the database. After taking its backup, if the backup got corrupted then what will a user do? Its answer is RESTORE VERIFYONLY will be useful for user. This command will check whether a database is corrupted or not.

    RESTORE VERIFYONLY FROM DISK = 'D:\SQLServerBackups\MyAdvWorksData.bak'

    Note: Before restoring the database from the backup, please check the database by using this command to save your time.

    Conclusion: We have seen the uses of important commands of the SQL Server database. I hope it will make your work easy and help you to make your database corruption free. 
    »»  Read More...