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

Comments

Popular posts from this blog

MSSQL Interview Question Clustering- Part2

Error 1067: The cluster service is stopped on passive node of my two node sql cluster. It throws following error when I try to start this:

Handle Sexual Harassment