SQL MASTER DATABASE SHRINKING

With the growing technology the files size needed to store the information about these technology has been increased. Same is the case with SQL database as with technology size of databases is also becoming bulky so there is need to reduce the size of the database. As SQL support mdf, ndf and ldf files, but the main and major drawback is that SQL is prone to corruption so for the solution can use SQL Repair. At the time of corruption larger the database more trouble to recover it so for that shrinking must be done in SQL database. In SQL database shrinking is done to reduce the size of database file that allow utilizing the unused space in between the file. Database file shrinking can be done for both log files as well as master files and can be done in two ways automatic and manual.

Database file shrinking is done from end. So if u wants to shrink a 5GB file and for which the target size given by the user is 4GB in DBCC statement then the shrinking process of file will be done from end the database engine will shrink from end 1 GB of the file.

Automatic Shrinking

In this if the Automatic Shrink is enabled then the database engine routinely shrink the database file which posses free space. For this AUTO_SHRINK option has to be switched or can say set ON. This option will perform its function in the background which did not affect the user.

Manual Shirking

DBCC SHRINKDATABASE as well as DBCC SHRINKFILE statements can be used to shrink database files manually. This process of shrinking database files can be stopped at any time in between. DBCC SHRINKDATABASE statement can only shrink your database to size which is not smaller then it’s initial size, even if u delete all the data from the database. DBCC SHRINKFILE

can be usede to shrink the file and can shrink the file to smaller size than its initial size.

So finally can conclude that database or file shrinking should be done to utilize the unused space within the database or file that will reduce overall size of the database.