Quick Jump Menu
Info Ask a Question
Advanced Search Search the Knowledgebase - We RECOMMEND the ADVANCED SEARCH option
Shrink SQL Databases
Author: Alex Francis Reference Number: AA-01823 Views: 718 Created: 26/07/2017 09:00 Last Updated: 26/07/2017 09:19 0 Rating/ Voters


Shrink SQL Databases



Overtime the data files of SQL databases will grow. If the backups do not run then the log file will also grow continuously until disk space runs out. 

Right click on the database, Tasks > Shrink > Files



You will see the option to select either the Data file (.mdf) or the log file (.ldf).  Below that, the available free space is displayed.


Shrinking the data file can take a long time and is best done out of hours for live databases. Shrinking the log file is fast however, for live databases, it will need to be done after a backup or alternatively while the database is in simple recovery model. If you have run out of disk space and cannot run a backup you will need to put the database in simple recovery model in order to shrink the log file.


Simple Recovery Model

Right click on the database and open Properties. Select Options on the left hand side.


You will see the option to change recovery model to Simple or Full. When in Simple there will be no hourly transactional backups. Under normal circumstances Live databases are in Full and Test are in Simple.