Home » Interview Q&A » Should we Shrink database – Sql Server

Should we Shrink database – Sql Server

Find Blocking queries in Sql Server
Find most expensive tables-Sql Server

Should we Shrink database – Sql Server

Lets try to understand with an example.

First we will create database and add some tables in that database with some sample data and then we will major fragmentation level as well size of database & log. After that we will compress database files and then we will major fragmentation level and then at last after rebuild and reorganize on indexes will major fragmentation level & database size.

So Run below script to create database, note blank database size:

Output:

Shrink-database-sql-server-technothirsty-1

 

After that run below script which will create tables and generate index on single table and try to analyse size of database :

Output:

Shrink-database-sql-server-technothirsty-2

 

Now please note that I will delete table “Student” and shrink database files and for each operations we will note database files size and fragmentation level of created index. You could find it will be grew by almost 100%.

Output:

Shrink-database-sql-server-technothirsty-3

 

To overcome from slowness issue we need to rebuild/reorganize index by using below script:

Output:

Shrink-database-sql-server-technothirsty-4

So now you can notice that After Rebuild size has been increased to 26336 from 25888 of DBMaintanace and 224 from 99 of Log file.

So from this article you could understand that we should not do shrink database frequently or on regular basis.

But I would recommend if your log file grows exponentially high and you think you could shrink that file to get space, you could shrink log file only(recommended).

 

About

Hello friends, My self Dhruv Sheth, I have more than 6 years of Industrial experience as a Software Developer. In my career I got chance to work with asp.net, c#,Web API, JavaScript, JQuery, WPF, WCF , Windows Application, PostScript, vb.net, GhostScript etc. So I have decided to share my knowledge with everyone and finally I have come up with TechnoThirsty.




DISCLAIMER:
The content is copyrighted to technothirsty.com and may not be reproduced on other websites without permission from the owner.You may contact us using the information below.