SQLINFO – We play with data and technology

The Main tasks for Database Maintenance are,

• Shrinking a database
• Backing up a database
• Updating database statistics
• Verifying the integrity of a database
• Cleaning up leftover maintenance files
• Rebuilding an index
• Reorganizing an index
• Cleaning up database histories

1. Shrinking  a  Database:

This will shrink the database and improve the performance by reducing

the disk read.

DBCC SHRINKDATABASE
( database_name [, target_percent]
[, {NOTRUNCATE | TRUNCATEONLY}]
)

Arguments

database_name

Is the name of the database to be shrunk. Database names must conform to the rules for identifiers. For more information, see Using Identifiers.

target_percent

Is the desired percentage of free space left in the database file after the database has been shrunk.

NOTRUNCATE

Causes the freed file space to be retained in the database files. If not specified, the freed file space is released to the operating system.

TRUNCATEONLY

Causes any unused space in the data files to be released to the operating system and shrinks the file to the last allocated extent, reducing the file size without moving any data. No attempt is made to relocate rows to unallocated pages. target_percent is ignored when TRUNCATEONLY is used.

Note:

Before shrinking log file set the recovery model to simple.

DBCC  SHRINK FILE (dbname,truncateonly)
DBCC  SHRINKFILE(tngtest_log,256,truncateonly)

1. 2. Backing up a  Database:

General Scenario:

• Full Backup:

Full backup is the backup of  both transactional log and Data file.

Take a full backup every week-end.

BACKUP DATABASE varun_test to disk = ‘E:sqlserverbackupfullvarunfull.bak’

• Differential Backup:

Normally we are taking ever y 6 hours.

Differential Back up is the changes that happen after a full backup. So a recent differential backup is necessary for a disaster recovery.

BACKUP DATABASE varun_test to disk = ‘E:sqlserverbackupDifferentialvarundiff.bak’ with differential

• Transactional Log Backup:

Normally we are taking ever y 15 mins or 5 mins.

Transactional Log backup is necessary for point-in-time recovery.
BACKUP LOG varun_test to disk = ‘E:sqlserverbackuplogvarunlog.trn’

3.Updating database statistics

Statistics:

These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving data and performing INSERT, SELECT, DELETE or UPDATE queries. It also outlines how SQL Server default statistics creation and maintenance settings can be changed on different levels (index, table, and database).

To Display the Statistics information use the command,

DBCC SHOW_STATISTICS(‘DBName’,index_name)

We  can update the Statistics for optimal query execution plan. This can be achieved using the procedure

Exec sp_updatestats

4. Integrity of the Database:

• Checks the database for errors. You should verify the output of this step during each run, and also perform this check after any hard failure of the database server.This can be achieved through

DBCC CHECKDB(‘DB_NAME’)

5. CleanUp the Leftover files:

Remove the unwanted data from the Database.

6. Rebuilding  an index:

Index should be rebuild when index fragmentation is great than 40%. Index should be reorganized when index fragmentation is between 10% to 40%. Index rebuilding process uses more CPU and it locks the database resources. SQL Server development version and Enterprise version has option ONLINE, which can be turned on when Index is rebuilt. ONLINE option will keep index available during the rebuilding.

This process drops the existing Index and Recreates the index.

ALTER INDEX ALL ON Production.Product REBUILD

7.Reorganizing an index:

This process physically reorganizes the leaf nodes of the index.

ALTER INDEX ALL ON Production.Product REORGANIZE

8.Cleanup Histories:

The clean up history task include cleaning up the job histories and

Backup histories. We can achieve this by executing this procedures.

sp_purge_jobhistory

sp_delete_backuphistory statements.