Change the collation on a MySQL database via PhpMyAdmin

Change the collation on a MySQL database via PhpMyAdmin

MySQL supports different types of collation and characters sets. The following article will walk you through how to change the collation for one table or the entire database. 

Change collation for the entire database

1. Connect to the database using phpMyAdmin
2. Select your database and click on Operations from the top menu 
   
3. On the drop down menu under collation select the character or collation that you intend to use for the database. 
   
4. Click on Go and the change is effected for the entire database

Change the collation for one table

1. Connect to the database using phpMyAdmin
2. Select your database
3. Select your table and click on operations from the top menu.
   
4. Under the Table options menu select the collation/character for the table
   
5. Click on Go to make the change for the table

Database cannot be opened due to inaccessible files or insufficient memory or disk space

Msg 945, Level 14, State 2, Line 1
Database 'db' cannot be opened due to inaccessible files or insufficient memory or disk space. See the SQL Server errorlog for details.

One of my friends called me that their application is giving error message of disk space , and i asked him to increase the space of drive. later he responded me that he shrinked the files and space has been raised to 70GB, but the error is still there.
I asked him to access the Database through management studio
When he tried to access the database, through a query or by trying to look at the database properties in Management Studio, he got the following message:

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


Here is Solution/Fix/workaround of this problem.

1. check the DB status, most of the time , it will return 1

use master
select databaseproperty('dbname','isShutdown')

2. Change the database to offline to clear the db status

use master
alter database dbname set offline

3. Now change the database to online, at this step log file and data files will be verified by sql server


use master
alter database dbname set online


This solution solved the problem.

Windows Azure: How to create a SQL Azure Server

In this article, I will show you how to create a new SQL Azure Server in Windows Azure platform from the management portal.

1. Login to the Windows Azure Management Portal using your Live ID at http://windows.azure.com
2. Once you’re logged in, click the Database button at the lower left of the page.
3. If you have created a SQL Azure server, you can skip this step. Otherwise, click on Create a New SQL Server which is located at the center of the page.
4. A wizard will pop up. Select your subscription.
5. Select the region where your SQL Azure Server will be created.
6. Enter the Administrator username & password.
7. Add any Firewall rules. Check “Allow other Windows Azure services to access this server” to allow your Windows Azure app to access this database. Then click Finish.

Once you’re done, you can now see your SQL Azure Server at the panel at the left of your page. A default master database will also be created like any other Microsoft SQL Server.

Windows Azure: How to Create a SQL Azure Database

In this article, I will show you how to create a new SQL Azure Database. I assume that you already have created a SQL Azure Server.

1. Login to the Windows Azure Management Portal using your Live ID at http://windows.azure.com/
2. Once you’re logged in, click the Database button at the lower left of the page.
3. Click on the Create new SQL Azure Database located in the center of the page.
   Alternatively, if you’re at the Subscription Home, you can click on Create button at the ribbon at the top of the page.
   
4. Now in the pop-up wizard, enter your Database name, select the Edition, and the maximum size of the database. Click OK when you’re done.

Now, you should be able to see your newly created SQL Azure Database under your SQL Azure Server.