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

    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:


    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.

    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.

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

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

    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


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

    Wednesday, April 13, 2016

    MS Access database corruption – causes and fixes


    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.


    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:

    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.

    »» 

    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:

     ObjectName = OBJECT_NAME(p.object_id), p.index_id  
     ,, 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  
     ALTER DATABASE database_name REMOVE FILEGROUP filegroup_name  

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

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

    Friday, January 29, 2016


    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'

    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'

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

    Wednesday, January 27, 2016

    How to Manage SQL Server Service Account Error

    SQL database is full of extraordinary functions and these functions make user’s life easy and more comfortable. Sometimes it’s easy to find errors and corruption but sometimes it’s like a nightmare. Few errors come up with the starting process and greed the SQL server by their interruption. Here, I will discuss an issue related with the SQL Server.

    Incorrect password of the service account or account locked disabled

    I got the following error when I tried to start MS SQL Server:


    The request failed or the service did not respond in timely fashion. Consult the event log or other applicable error logs for details.

     Reasons for this error:

    • Changing the location of master database
    • Anti-virus can lock the SQL Server
    • Insufficient permission for SQL Server Service account
    • Server side protocols issue
    • Missing important files due to disk failure or accidental deletion
    • Changing the Password of the service account but not updated on the Server where SQL instance installed
    • SQL Server is disabled from the Service Control Manager
    • Incorrect location on the startup parameters etc

    So, these are the few responsible cases for this error.

    At the time of startup error, try to open the SQL Server by command prompt and I do so. Simply type,net startmssqlserver to start the SQL Server.One more error message that you may get after executing this command.

    “A service specific error occurred”

    Check the SQL server error log or the Windows Application Event Log to find the main issue for this problem. Personally, I prefer the SQL Server error log which is a text file and I can see all the error messages easily. To know the location of error log file, go to the SQL Server Service in the SQL Server configuration Manager and check the Starting Parameters.

    I highlighted the location of the error log in the above figure and it is default location of the error log file. With this, you can easily check the current location of the master.mdf and masterlog.ldf files.

    If the SQL Server starts at least once before failure then it is easy to find the reason of failure by the error log report. In some cases, the error log will not show any entry and these cases are:

    Invalid password of Service Account or Account locked
    Incorrect location of the ERRORLOG file or the permission issue of the SQL account

    In the first case, The SQL Server won’t start so there is no chance for any entry in the log file. In the second case, the SQL Server starts but you cannot find the log entry in the log file. To check this issue, examine the Windows Event Log. Go to administrative Tools-> Event Viewer and filter for errors.

    Check the main reason for this problem. I got the following reason:

    Logon failure: unknown user name or bad password.

    From the above error, it is clear that an incorrect password causing the error. I updated the password of SQL Server account and fix it.

    Other possible errors are:
    Logon failure: account currently disabled.
    Login failed: Account locked out.

    To enable the account, go to your database by using SSMS. Security->Logins->sa. Right click on the sa and go to the properties. Now select the Status from the left pane. Enable the login status and click on OK button.

    You can also use the script to enable sa account:

    1. Use [master]
    2. GO
    4. GO
    6. GO

    Note: enter the correct password. It is just an example.

    The best way to recover SQL server password is to contact with the concern person (Example: DBA). If you are unable to contact the related person so in this case, you can try a professional tool to recover SQL password. This is also a way to recover your database.

    Conclusion: So this is a very common problem with SQL Server database. If you are a DBA and have changed any password of the SQL account then always save on a safe place. If needed then share it with concern person. 
    »» 

    Wednesday, December 30, 2015

    Fix Error – SQL Server does not exist or access denied

    Being as an SQL Server user, I always try new methods to fix the SQL Server database errors. Sometimes I become successful and sometimes failed but, I never quit.I am discussing a scenario through this blog which is based on this error.

    I got an email from a guy and he shared his problem with me. He was using SQL Server 2008 getting the following error message on the screen:

    “SQL Server does not exist or access denied”.

    He searched the solution over the internet and checked the all the protocol which can be the reason of this error but he was unable to fix the error. Sometimes user gets the error even if the Named Pipes and TCP/IP protocols are enabled. To fix this error, stay on this blog.

    Step 1: Check the status of the protocol name.

    Then you need to create the alias on the client side.

    Step 2: Searching for cliconfig.exe which is present in the System32 folder and click on it.

    Step 3: To create TCP/IP alias, select TCP/IP from the list and click on the Enable button. 

    Now it will be add on the Enabled protocols by order section.

    Note: Make sure there is no Named Pipe in the list. If any Named Pipes is in the list then disable it.

    Step 4: Now click on the Alias section.

    Step 5: Click on the Add button.

    Step 6: In Server alias section, fill the Server name of SQL Server database.

    Step 7: Select the TCP/IP section which is the second option in the Network libraries.

    Now fill your SQL Server IP address in the Server name box as shown in the figure.

    Click on the OK button.

    Note: Default port number of the SQL Server is 1433.

    Step 8: Click on the Network Libraries to check the entry.

    Now you can connect to remote SQL Server using Server Name.

    Other Scenario 

    SQL Server 2005: In SQL Server 2005, server assigned the instance name to measure the connection information.

    Solution: Just remember one thing; the instance name is also included with the Server name. Take a look on the below image:

    You can see the Server Name is included with the instance name P6Instance.

    Note: When you select the default instance and the default instance is already present on your machine then, it will be automatically upgraded by the SQL Server setup. A single machine can host only a single default instance. 

    Conclusion: These are the two scenarios to fix this error. Always check the network protocol status and follow these steps to fix this error. 
    »» 

    Wednesday, December 16, 2015

    SQL Database Error 3417 – Steps to Solve This Error

    SQL server users may face errors in the SQL Server database whether; they are experts or newbie. If you are a DBA of your company then you have lots of responsibilities and a small error can mess up your whole work. Some SQL database error self-explanatory and if you understand the message correctly then, half of the problem will be solved. In this blog, we will discuss the SQL database error 3417 which is a very common error that appears when DBA starts the SQL Server database.I also got this error message earlier and I am discussing here the steps that I followed to fix it. The error message looks like:

    In the above error message, just read this sentence: For more information, review the System Event Log. I checked the event log and it was looking like this:

    The file “C:\path of .mdf file” is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

    Now, I follow the following steps to fix this issue:
    • Go to the data folder where .mdf file is located. Please confirm both the file (.mdf and .ldf) are not compressed.
    • Now, select the Properties.
    • Click on the Advanced button.

    • Un-check the Compress Contents to save disk spaces option.

    • Click on the OK button.
    • Start the SQL Server from the service manager.
    This process solved the issue. So always read the error message and error log carefully because it shows the solution.

    Other reasons: If you are unable to fix this problem by the above solution then please check the Network Service permission in the data folder.
    • Go to the Data folder and click on the Security/Permission setting.
    • In the Network Service Account, add the Network Service account.

    Note: Take the backup of the .mdf file and .ldf file and replace them. If the paths are different (like 32 bit vs 64 bit) then, in this case the SQL Server may give the error. 

    If still not able to fix this problem that means, there is a corruption in your .mdf file. In this case, you can try the Stellar Phoenix SQL Database Repair tool to fix thr error. This tool is able to repair corrupt .mdf files without any alteration in the original database.
    »»