DB Size Growing after moving fields

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 7:30 am

  #276050

  Hi All,

  I am new here, but have read the site a lot.

  I have been doing some SQL maintenance on various servers that I administer.

  One of the scripts takes 20-30 fields or so, and moves the data from the old column to a new column, and once complete it drops the old column.

  After doing this, the DB file (MDF) grew from about 3.5GB to about 5GB.

  Why?

  I did a shrink on both the file and database.

  This particular box is SQL 2008.

  Let me know any questions.

  Thanks for the help!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 10, 2013 at 8:28 am

  #1622463

  scorchpc 96170 (6/10/2013)

  ---

  I have been doing some SQL maintenance on various servers that I administer.

  One of the scripts takes 20-30 fields or so, and moves the data from the old column to a new column, and once complete it drops the old column.

  After doing this, the DB file (MDF) grew from about 3.5GB to about 5GB.

  Why?

  I did a shrink on both the file and database.

  This particular box is SQL 2008.

  In one word, fragmentation.

  Adding, population columns then droppring column caused fragmentation.

  Shrink returns unused space but also usually adds more fragmentation.

  Solution would be to reorganize the affected tables as well as indexes pointing to them.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Mike John

  SSCertifiable

  Points: 7216

  More actions

  June 10, 2013 at 8:33 am

  #1622466

  Most likely is the space has not been reclaimed in the old table yet. If you have no clustered index on a table some operations can not free up the space completely. I would expect alter table could well be one of them. If that is correct you will effectively have the old columns and the new both stored within each row.

  Easiest way is to drop and recreate the clustered index - or use alter index with the REBUILD option to achieve the same thing.

  A check with sp_spaceused before and after would confirm the space reclaimed.

  Mike John

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 8:36 am

  #1622467

  Thanks for the fast responses guys.

  I suspected something like this. The next time no one is working, I will backup the DB then try the above advice.

  Thanks again!

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 9:02 am

  #1622477

  Hi Paul and Mike,

  Do you guys think I should rebuild all indexes on all tables? Or just indexes on the affected table?

  I am thinking all indexes on all tables really can't hurt. I found this script:

  DECLARE @TableName VARCHAR(255)

  DECLARE @sql NVARCHAR(500)

  DECLARE @fillfactor INT

  SET @fillfactor = 80

  DECLARE TableCursor CURSOR FOR

  SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

  FROM sys.tables

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

  EXEC (@sql)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor

  GO

  Source:

  http://blog.sqlauthority.com/2009/01/30/sql-server-2008-2005-rebuild-every-index-of-all-tables-of-database-rebuild-index-with-fillfactor/

  I appreciate your guys feedback.

  Thanks!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 10, 2013 at 9:06 am

  #1622483

  I would focus on the issue at hand, do it only on affected tables.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 9:08 am

  #1622484

  will do, I'll let you guys know the results

  Thanks again!

• Jeffrey Williams

  SSC Guru

  Points: 90303

  More actions

  June 10, 2013 at 12:21 pm

  #1622571

  PaulB-TheOneAndOnly (6/10/2013)

  ---

  I would focus on the issue at hand, do it only on affected tables.

  He ran a shrink on the database - that affected all tables...

  Jeffrey Williams
  “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

  ― Charles R. Swindoll

  How to post questions to get better answers faster
  Managing Transaction Logs

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 10, 2013 at 12:25 pm

  #1622573

  Jeffrey Williams 3188 (6/10/2013)

  ---

  PaulB-TheOneAndOnly (6/10/2013)

  ---

  I would focus on the issue at hand, do it only on affected tables.

  He ran a shrink on the database - that affected all tables...

  We are talking about rebuilding indexes, Jeffrey.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 1:06 pm

  #1622596

  FYI

  I have a smaller server in China that wasn't as bad, but could still use this.

  I tried rebuilding the indexes on just the affected table only, it helped a bit, but not as much as I would have expected

  So, I am running this script to rebuild all of them

  I have a full backup in case things go wrong

  Here is the script, slightly modified because one of my tables is named "Order" so I had to make sure to surround it with brackets [Order]

  ________________________________________

  DECLARE @TableName VARCHAR(255)

  DECLARE @sql NVARCHAR(500)

  DECLARE @fillfactor INT

  SET @fillfactor = 80

  DECLARE TableCursor CURSOR FOR

  SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

  FROM sys.tables

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @TableName = replace(@TableName, '.', '.[')

  SET @sql = 'ALTER INDEX ALL ON ' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

  EXEC (@sql)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor

  GO

  ________________________________

  This took about 33 minutes.

  This did save about 150MB of space, but the indexes are still just as fragmented.

  The server does seem to perform a bit better.

  So, I may try this on the bigger server this weekend.

  ________________________________

  One reason I want to do this is because we backup these servers over the internet on a daily basis, so, I would like to keep the sizes smaller for less traffic

  Another reason is, I want to make sure these DBs keep performing well. Some are horrible; i.e. the same query takes 5 seconds on one server, and takes 15 minutes on another, even with similar records counts/sizes.

  Thanks for the help!

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 10, 2013 at 1:28 pm

  #1622611

  Couple of quick questions...

  Do all affected tables have a clustered index?

  In the affirmative case, was each one of such clustered indexes rebuilt?

  In case one or more affected table do not have a clustered index, these tables have to be reorganized to get rid of fragmentation... usually by exporting, truncate and importing back then... rebuild indexes.

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 10, 2013 at 1:33 pm

  #1622616

  Hi Paul,

  Q: Do all affected tables have a clustered index?

  A: No

  Q: In the affirmative case, was each one of such clustered indexes rebuilt?

  A: Assuming the script I posted works, then yes.

  Paul Comment: In case one or more affected table do not have a clustered index, these tables have to be reorganized to get rid of fragmentation... usually by exporting, truncate and importing back then... rebuild indexes.

  A: The tables that do not have clustered indexes are very small, mainly cross reference type tables

• PaulB-TheOneAndOnly

  SSC Guru

  Points: 66772

  More actions

  June 10, 2013 at 2:29 pm

  #1622627

  I would take a look at DBCC CLEANTABLE; please check this.. http://msdn.microsoft.com/en-us/library/ms174418.aspx

  _____________________________________
  Pablo (Paul) Berzukov

  Author of Understanding Database Administration available at Amazon and other bookstores.

  Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

• Jeff Moden

  SSC Guru

  Points: 1004290

  More actions

  June 10, 2013 at 2:36 pm

  #1622631

  scorchpc 96170 (6/10/2013)

  ---

  will do, I'll let you guys know the results

  Thanks again!

  Simplify the code by removing the FILLFACTOR part of the code. The indexes will rebuild with the original FILLFACTORs unless you specify otherwise.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• scorchpc 96170

  SSC Enthusiast

  Points: 101

  More actions

  June 30, 2013 at 9:11 am

  #1628545

  I tried, but still the DB just grew. Oh well.

  DECLARE @TableName VARCHAR(255)

  DECLARE @sql NVARCHAR(500)

  DECLARE @fillfactor INT

  SET @fillfactor = 80

  DECLARE TableCursor CURSOR FOR

  SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName

  FROM sys.tables

  OPEN TableCursor

  FETCH NEXT FROM TableCursor INTO @TableName

  WHILE @@FETCH_STATUS = 0

  BEGIN

  set @TableName = replace(@TableName, '.', '.[')

  SET @sql = 'ALTER INDEX ALL ON ' + @TableName + '] REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

  EXEC (@sql)

  FETCH NEXT FROM TableCursor INTO @TableName

  END

  CLOSE TableCursor

  DEALLOCATE TableCursor

  GO

  DBCC CLEANTABLE (tstracker, ata)

  go