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  
 FILENAME = [C:\SQLData\file_name.ndf],  
 SIZE = 1MB,  
 )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.

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

Friday, December 11, 2015

MS Access Database File Formats: ACCDB vs MDB

MS Access 2007 releases with the new file format .accdb and the previous versions has .mdb file format. Every new version comes up with few changes and these changes were visible in MS Access 2007 and the later versions. In this blog, we will see the benefits of .accdb file over the .mdb file. Let’s discuss the benefits of the .accdb file over the .mdb file.

By using the .accdb file you will be able to:

  • Multivalued Fields: This field allows the user to select one or more options for the field values and this process is done by the checkbox. It is same as the different options on clothing like Small, Medium, Large and Extra Large etc.
  • Attachments: The ACCDB file format allows storing the file attachments and other BLOBs (Binary Large Objects) in database fields. 
  • Improved Encryption: By this feature, you can choose the database password and make the database content encrypted. MS Access database uses the Windows Crypto API for the encryption.
  • Safe and Secure Integration with Outlook and SharePoint: Outlook and SharePoint do not allow .mdb file databases due to the security issues. In.accdb file format, these issues are solved properly and allow the validation for the security. 

Disadvantage in .accdb file format:
  • Replication: The .accdb file format does not support the replication. 
  • User-Level Security: This feature is not supported by the .accdb file but, use the encryption option which makes your data unreadable by tools and other programs.
  • Design and Modification Work: All the versions which supports .accdb file format are not handy for the design purpose.
How can I convert the .mdb file to. accdb?

To convert the .mdb file to .accdb file, follow these steps:
  • Open the Database.
  • Click on the File tab.
  • Click on the Open option and select the database which you want to convert.
  • Again, click on the File tab.
  • Select the Save Database As option under File Types.
  • Select the .mdb file format and click on the Save As button.
  • Select the location to save the file.Click on the Save button.

How can I convert the .accdb file to .mdb?

This is the basic question which comes in the user’s mind. Well, to know this process just read the following steps:

  • First of all, close all the opened objects of the database.
  • Click on the File tab then go to the Save As option.
  • Now you can convert into Access 2002-2003 or Access 2000 database. Both support .mdb file format.
  • Now click on the Save As button.

Note:  You can change the name of the .mdb file and the location.

Conclusion: We have seen the important difference between .accdb and .mdb files and need of these formats. Well, this is about the difference but, users choose them according to their requirement.
»» 

Thursday, November 26, 2015

Key Points to Block SQL Server Attacks

I was solving the SQL Server errors through the forums then I saw a discussion about the SQL attacks. That discussion was very good and it has given me the idea to write a blog on it. Well, this blog is about the easy techniques to prevent the SQL database from the corruption.

When someone talks about the SQL attack then, the first thing that comes in the mind is “SQL injection” and this is very popular known code injection technique to destroy the database. This attack is based on 1=1 is always true principle.Read more about SQL injection here. So, get ready to know about the few simple techniques to make your SQL Server database safe and secure.

  • Do not use dynamic SQL: use parameterized queries or stored procedures, prepared statements instead the normal statement whenever possible.
  • Update and Patch: In the specious situation just apply the patches and updates on the SQL Server database. 
  • Firewall: Always on the firewall to prevent the data from the malicious attack. A WAF (Web Application Firewall) is useful to provide the security before patch file is available.
  • Minimize your attack surface: Some options like xp_cmdshell become helpful for the hackers so try to avoid these options.
  • Encrypt the data: Never try to store the data in the plain text format. Manage the database with the tough password so that the hacker cannot dump the database and steal the information. 
  • Password Security: Don’t forget the basics i.e. change the password of the application accounts into database regularly. If you are unable to do it on the daily basis then change on the alternate days.
  • Understand the application: Just understand the behavior of the web application so you can easily identify when the application is behaving abnormally like using unusual inputs and executing many more database lookups (Attempt to retrieve data). 
  • Use validations: Always use validations on the input fields to prevent the database from the attack.

Always take care of these points to prevent the data from the corruption. If the corruption level is major and hard to handle then use Stellar Phoenix SQL Database Repair tool to repair the corrupt database. 
»» 

Sunday, November 22, 2015

Tips for Using Triggers in SQL Server Database

Triggers are most important part of the SQL Server Database. Triggers are the special stored procedures which are used to perform actions on the database. These actions have different categories like: DDL, DML and logon etc.

Tip 1: Find the list of triggers using sp_MSforeachtable

If you want to see the list of the triggers along with the schema then, you can use the sp_MSforeachtable to get it. Use this stored procedure like this:

USE AdventureWorks2012
EXEC sp_MSforeachtable
@command1 = 'sp_helptrigger ''?''' ,@whereand = 'And Object_id In (Select parent_id From sys.triggers)';

After executing it, you will get the list of the triggers as the output. See the below image which shows the out of the above query:

In the above output, you can see the different columns with the details like: trigger name, owner, operations and schema.  
Tip 2: Find the list of enabled and disabled triggers

Every database has tables and if the user creates any trigger then it comes up under table section. A User has options to enable and disable triggers by clicking on it or by the query. Just right click on the trigger which you want to enable or disable it and it will look like this:

To enabled or disabled the trigger using query, just execute the below queries.

Disable the trigger

USE Database_name
DISABLE TRIGGER Trigger_Name ON Table_Name

Enable the trigger:

USE Database_name
ENABLE TRIGGER Trigger_Name ON Table_Name

To see the status of all the triggers, simply run the following query:
Select * from sys.triggers

In this case, the user will get the status of all triggers presented in the database.

In the above image, the red rectangular box showing the status of the trigger. In this section, the output always presents in the binary form (0 or 1). In the above image the trigger “Trg_InsertSalePerson” is disabled because its binary value is 1 in the “is_disable” column. To check its result, now enable the same trigger and you will get this message on the screen:

Now run the same query again
Select * from sys.triggers
At this time, the output will show zeros for both the triggers:

We have one more option to see the status of the triggers. Execute this query 

Select AS TableName,
Schema_name( AS Table_SchemaName, AS TriggerName,
WHEN TRG.is_disabled = 0 THEN ‘Enable’
ELSE ‘Disable’
END AS TRG_Status 
From sys.triggers TRG
INNER JOIN sys.tables TBL
ON TBL.OBJECT_ID = TRG.parent_id
AND is_disabled = 0

The output will be:

Now, the user can see the difference TRG_Status and both are enabled. So these are the simple tips about to find the status of triggers. 
»»