Shrink Database and Files in MSSQL
Posted by codingsense on December 22, 2008
To increase your MSSQL database performance use Shrink database and files.
Each MDF file will have its LDF (LOG) file with it. Any transaction made to the database will lead to increase of size in MDF as well as LDF files. Over a period of time you will notice that eventhough your database has less data, the file size of both MDF and LDF will be large which leads to slower processing of queries and lead to other maintainance problems.
So MSSQL has given an option to remove unused spaces.
To shrink the database and files through Management studio you can follow the below steps
- Open Object explorer
- Right click on the database that needs to be shrinked
- Shirnk Database
- Shrink Files
The following are the commands to shrink Database and Files through T-SQL.
Shrink Database :
The database name is the name that is seen in the object explorer of Management studio.
Shrink Files :
dbcc ShrinkFile(Logical Name)
The logical name of the database can be seen using the following query
Select name from sysfiles
Here 2 rows will be listed first one will represent the logical name of MDF file and other is for LDF file. Execute the DBCC query twice with both the names to shrink both MDF and LDF files, like
In my case i was working on a Email Project and the database would increase rapidly, so i make a stored procedure and gave user the option to shrink the database. In the button click event i called the stored procedure which was
Create PROCEDURE [dbo].[ShrinkEmail] AS BEGIN DBCC ShrinkDatabase(Emails) DBCC ShrinkFile(Emails) DBCC ShrinkFile(Emails_LOG) END
This reduced my LOG file from size 7GB to 504KB and MDF file changed from 2.8 GB to 2.3 GB. 🙂
Caution: There are many discussions going over the net whether the MSSQL performance increases or decrease by shrinking the databases and files. So please do refer the following links and take appropriate decision.
Happy Learning 🙂