Shrinking DB Cause Increase In Fragmentation

Let's understand how shrinking Database can increase Index fregmentation.

When the DB is shrunk, the sql server db engine goes at the end of the data pages located in the database and adjust them in the size mentioned for shrink activity. This is the reason why shrink causes data pages unorganized and result in fragmentation.

Let's create a database name CitiesPopulation.
and some tables like
EMployeeInfo
EmployeeInfo1
EmployeeInfo2
PopulationInCaror

Get the DBId from the output of below query-
select * from sys.sysdatabases

For my database CitiesPopulation, the DBid is 7.

Now check for the index fregmentation status in your Database with the help of below query-
SELECT *
 FROM sys.dm_db_index_physical_stats(7, null, null, null, null)

You can also check the fregmentation for a perticular table by giving the object_Id in the above query e.g.
SELECT *
      FROM sys.dm_db_index_physical_stats(7, OBJECT_ID('Employeeinfo'), null, null, null)

Right now the fregmentation is very less in all the table, now I will shrink the DB and again will check the fregmentation status-

It has increased upto 99%.


Reader of This blog also showed their interest in following blogs:

1. SQL Server DBA “Interview Questions And Answers”
2. SQL Azure Interview Questions and Answers Part – 1
3. SQL Azure Interview Questions and Answers Part – 2
4. Powershell Interview Questions and Answers

Along with technical learning I would like to share some great articles for anyone interested in the betterment of his/her family life

• Quality time with kids: Let's have a Quality Audit
• Parental guidance how to control your kid in elementary school not obeying teacher
• Parental guidance for child development post terrible twos
• Parental guidance on how to handle child when they show their first infatuation at elementary school
• How to Handle fit of temper by fixing baby screen time
• Are Words Important