Recently one of my friend sent me email that he is having some problem with his very small database. We talked for few minutes and we agreed that to further investigation I will need access to the whole database. As the database was very big he dropped it in common location (you can use livemesh or dropbox  or any other similar product) and I picked up from the location.

I was able to install the database successful. He informed me that he has created database diagram so I can easily understand his database tables. As soon as I tried to open the database diagram I faced following error. For a while I could not figure out how to resolve the error.

Error: Database diagram support objects cannot be installed because this database does not have a valid owner. To continue, first use the Files page of the Database Properties dialog box or the ALTER AUTHORIZATION statement to set the database owner to a valid login, then add the database diagram support objects.

Problem

An issue you will face at some point in your career is SQL Server database corruption. SQL Server database corruption causes issues where data cannot be accessed correctly and often queries are aborted and do not complete correctly. A feature in SQL Server Database Mirroring is the ability to automatically fix corrupted database pages and in this tip we will look at how this is done.

Solution

Automatic Page Repair is one of the features of SQL Server Database Mirroring which helps us replace a corrupt page by getting a good copy from a partner database. This functionality works bi-directionally (i.e. if the Mirrored Database page is corrupt or a page on the Principal Database is corrupt). In this tip we will demonstrate this feature.

Let's say we have a database configured for mirroring and we want to corrupt the database so we can test out this feature.

Note - Do NOT perform this test in a PRODUCTION environment. The example is intended to demonstrate the process of recovering a page with Database Mirroring.

A couple of days back at I got a call from my support team informing me that one of our database located on the Production Server went into Suspect Mode. The version used was SQL Server 2005 Service Pack 3. Being a Production Database server, it was a Priority 1 incident and the expected time of resolution was 4 hours..

Solution:

The first step was to identify why this incident occured and after investigation it was found that it was due to the corruption of the transactional log file of the database.

I connected to SSMS using the sa login credentials and located the SUSPECT database:

I then reset the status of the SUSPECT Database by executing the below T-SQL query against the master database.

EXEC sp_resetstatus 'test_dr';

It happens that MySQL data files get corrupted and require recovery. The easiest way of checking the integrity of table is by running the following command:

CHECK TABLE messages;

That phrase is going to check for errors in the messages table. If the output data look like in the picture below, it means that the table is fine and no recovery is required.

In some cases the Msg_text columns might display error messages. In the event of that you need to run REPAIR TABLE, and MySQL will try to fix the problem.