Monday, February 25, 2013

Help Me! I am unable to open my SQL server database

At times, when SQL server database administrator tries to open the database, he gets below error message on the screen.

Database cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server error log for details

Cause: As the result of above error message, database administrator is unable to open the database. There are various reasons for the above error message like less disk space, permission unavailability and many more.

It is one of the most generated error message during mounting the SQL sever database.

WorkAround: Database administrator can resolve above error message by following steps:
  1. Database administrator needs to add more hard drive space and it can be done by following methods:
    1. Remove the unnecessary files from the hard drive
    2. Add new hard drive.
  2. Check the Autogrow feature of the database, if it is ON then disable it.
  3. Have you enough permission to open the database? Check for the permission setting.
  4. Check for .mdf & .ldf files. If these files are mark as read only then database administrator is unable to open the file.

Recommendations for Changing the SQL server Recovery Models

A database administrator can change the recovery model of their database at any time as per the requirement. If you have planned to change the recovery model from the simple recovery model to the full recovery model during the bulk operation then be aware that logging of bulk operations will be change from minimal logging to full logging. Here I will tell you some recommendations that are very necessary before or after changing to Simple recovery model.

Recommendations for changing from Simple to Full recovery model: If you are planning to change the recovery model for your database from the simple recovery model to the full recovery model then take care of the followings:
  • Take the full or differential database backup of your database immediately after changing the recovery model from the simple to the full recovery model. The log chain will not start until the full or differential database backup.
  • Take regular backup of your transaction log and make a proper plan for backing up the log backup according to your database need.
Note: If you don't backup the transaction log on the regular interval then it can expand until it runs out of your disk.

Recommendations for changing from Full to Simple recovery model: If you are planning to change the recovery model for your database from the full recovery model to the simple recovery model then please attention for the following:
  • Changing the recovery model from full to simple will break the log backup chain so make a backup of your log backup before changing the recovery model. Log backup will allow you to recover the database up to that point.
  • After changing the recovery model, you will need to take the data backup on regular interval to avoid any data loss.
How to change the recovery model: After understanding the impact of backup and restore strategy that will happen after the changing the recovery model to simple, now you can change the recovery model of your database with the help of SSMS. Perform below steps to change the recovery model for your database:
  • Connect to the SQL server instance and expand the server tree.
  • Now expand the database tree, select user database, or system database that suits your requirement.
  • Press right clicks on the database and go-to the database properties.
  • Now go-to the select a page pane and click on the options.
  • Now go-to the recovery model section and see the current recovery model of your database.
  • To change the recovery model, click on the arrow next to the recovery model options and select your desired recovery model: simple, bulk-logged or full.

Wednesday, February 13, 2013

Choose the Best Method for SQL server Database Moving

At times, it may require to moving SQL server data or log files from one to other location on the same SQL server. There are two methods that help you in performing this task; Attach/Detach & ALTER Database. Both methods perform the same task but there are numerous reasons why prefer Alter Database over Attach/detach method.

Attach/Detach Method: This method is used to move SQL server data or log files from one location to another location as mentioned above. Now have a look on the syntax of both the operations.

EXEC sp_detach_db 'your-database-name

After the successful detach of the database from SQL server instance, you can move the data & log file to a new location then after attach the files to new SQL server instance by below syntax.

EXEC sp_attach_db 'your-database-name
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Data.mdf',
'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\your-database-name_Log.LDF'

Microsoft’s online book topic sp_attach_db command has mentioned the below warning message:

This feature will be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use CREATE DATABASE database_name FOR ATTACH instead. For more information, see CREATE DATABASE (Transact-SQL).”

Alter Database Method: You cannot use this method without identifying the filenames associated with the database. So first identify the filenames by below query:

Select name, physical name from sys.master_files where database_id = ('your-database-name')

Now you will be able to use Alter database with Modify command for moving SQL server database files from one SQL server instance to another.

Aleter Database your-database-name Modify File (Name = your-database-name_Data, Filename = 'D:\SQLData\your-database-name_Data.mdf');
Aleter Database your-database-name Modify File (Name = your-database-name_Log, Filename = 'D:\SQLData\your-database-name_Log.ldf');

Now set your database as offline to complete the moving process by below syntax.
Alter Database your-database-name Set Offline
Move the files to a new location & bring your database online by below syntax.
Alter database your-database-name Set online

It will disable the service broker on the database.
It will not disable the service broker in the database.
It will disable the Trustworthy setting of the database.
It will not disable the Trustworthy setting for the database, if enableed.

Thursday, February 7, 2013

Need to attach an mdf file without transaction log file in SQL server

If someone has deleted your transaction log file intentionally and you have only .mdf file of your database then you can still attach your mdf file with the database. No matter your database was cleanly shutdown or not. In this article I will thoroughly guide you methods & steps which are required to recover your database when you have only .mdf file of the database.

Database was cleanly shutdown: If your SQL server database was cleanly shutdown then you can attach your database with the help of SQL server management studio (SSMS).

Let’s discuss all the steps in details:

  • Connect to the SQL server instance using SSMS
  • From the Object Explorer, right click the Databases node and a drop-down menu will be appeared.
  • Now click on the Attach tab.

  • Click on the Add button from appeared dialog box.

  • Now a Locate Database Files dialog box will be appeared.

  • Click on the browse button to select your database MDF file after choosing .mdf file click on the OK button.
  • Now you will see your mdf file is selected but SQL server is unable to select your transaction log file because transaction log file was deleted. To attach .mdf file without transaction log file; select the log file and click on the remove button to remove it. Now click on the OK button to attach the database file. SQL server will create a transaction log file for you while attaching the database.
Database was not cleanly shutdown: Try below steps if your database was not cleanly shutdown.
  • Create a same size database
  • Now shutdown the SQL server
  • Change the old mdf file with new one
  • Start the SQL server, your database may go in suspect mode
  • Now change your database status from suspect mode to emergency mode by ALTER Database command
  • Run DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS. It will rebuild the log and run full repair
Note: You may loss some amount of data but your database will be available again.