Ravi Khanal

Hey, Our Application is down!

This is the common thing you hear while you are working as a DBA. The reason for this is not always evident. Perhaps the number of transactions issued has increased or maybe the volume of the data has increased. But sometimes this might not be the cause of the problem. The problem might be due to disorganization of the database. Database disorganization occurs when a database’s logical and physical storage allocation contains many scattered areas of storage that are too small, not physically contiguous, or too disorganized to be used.

Database disorganization or fragmentation can occur when modifying data with INSERT, UPDATE, or DELETE statements, which over time cause gaps in each page. If a query search is based on a table scan or partial table scan, then it will create overhead for the SQL Server process with additional page reads, leading to high CPU activity and unresponsiveness.

To minimize fragmentation, database objects must be restructured on a regular basis.

Some of the steps that you need to perform for database reorganization is

• Take the object level backups using BCP, Import/Export Wizard. If the database has constraints disable them.
• Truncate the table.
• Import the data back to the table.
• Enable the constraints if you had disabled them.

Database reorganization is an offline process. So you should be aware of the downtime and do this only in off-peak hours. If the database is mission critical and no downtime is accepted then the split mirror backups can be used by completely using different set of disk. Then the database reorganization can be done on the database and replace the disk back again.