How much HDD space and an index possibly use?

All replies

• 

  

  1

  Sign in to vote

  Can you check the file growth once? Is it restricted by any chance?

  --Prashanth

  • Proposed as answer by pituachMVP Tuesday, December 9, 2014 12:48 AM
  • Marked as answer by Lydia ZhangMicrosoft contingent staff Tuesday, December 16, 2014 9:34 AM

  Monday, December 8, 2014 8:43 PM

  Answerer
• 

  

  1

  Sign in to vote

  I think that was it.  It was set to 1MB.  I just changed it to 10%.

  This may be helpful for others.

  http://www.mytechmantra.com/LearnSQLServer/How-to-Change-SQL-Server-Database-Auto-Growth-Settings.html

  All set now.  Thanks.

  ---

  Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

  • Marked as answer by Lydia ZhangMicrosoft contingent staff Tuesday, December 16, 2014 9:34 AM

  Monday, December 8, 2014 9:05 PM
• 

  

  0

  Sign in to vote

  What command are you running and what exactly is the error message you are getting?

  Monday, December 8, 2014 9:42 PM

  Answerer
• 

  

  0

  Sign in to vote

  I think that was it.  It was set to 1MB.  I just changed it to 10%

  Auto growth of 1 MB has less chance of causing space issue as compared to 10 % I would say change autogrowth to some value in MB dont keep it in percentage. Because when it would grow in % it would grow more unnecessarily.

  What is index rebuild script what is size of index.

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  
  My Technet Wiki Article
  MVP

  Monday, December 8, 2014 9:55 PM
• 

  

  0

  Sign in to vote

  1. If your database got to 5.4GB and it had file growth of 1mb, then I am guessing that this is the same with the Log file, and this mean that you you probably have MUCH Too Many Virtual Log Files (VLFs). Each time the log file got 1 more mb it got 4 new VLF! You should check this issue, on the way.

  Growth	Number of VLFs created
  <= 64Mb	4
  >64 but <=1Gb	8
  >1Gb	16

  2. I recommend NOT to use percentage for databases this size, but fix growth size, for most cases.

  3. What edition are you using? For example, If you use enterprise edition, then you can use Single Partition Online Index Rebuild. YOu can rebuild partition by partition and not all together.

  ---

  Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

  
  
  

  • Edited by pituachMVP Tuesday, December 9, 2014 12:44 AM
  • Marked as answer by Ed Price - MSFTMicrosoft employee Wednesday, December 31, 2014 3:23 AM

  Tuesday, December 9, 2014 12:43 AM
• 

  

  0

  Sign in to vote

  I came across this link, which I found useful.

  http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

  Thanks pituach.  I think I'm fine now.  
  

  ---

  Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

  Wednesday, December 10, 2014 2:59 PM
• 

  

  0

  Sign in to vote

  I came across this link, which I found useful.

  http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

  Thanks pituach.  I think I'm fine now.  
  

  ---

  Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

  That is old and might not give you correct value you must use DMV sys.dm_tran_database_transaction

  SELECT  database_transaction_log_record_count,
  database_transaction_log_bytes_used
      FROM    SYS.DM_TRAN_DATABASE_TRANSACTIONS
      WHERE   database_id = db_id('db_name');

  Plus if you want to read about logging in index rebuild below article would help you

  http://social.technet.microsoft.com/wiki/contents/articles/24420.curious-case-of-logging-in-online-and-offline-index-rebuild-in-full-recovery-model.aspx
  

  
  

  ---

  Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
  
  My Technet Wiki Article
  MVP

  
  

  • Edited by Shanky_621MVP Wednesday, December 10, 2014 3:24 PM
  • Marked as answer by Ed Price - MSFTMicrosoft employee Wednesday, December 31, 2014 3:23 AM

  Wednesday, December 10, 2014 3:23 PM
• 

  

  0

  Sign in to vote

  I came across this link, which I found useful.

  http://www.mssqltips.com/sqlservertip/1225/how-to-determine-sql-server-database-transaction-log-usage/

  Thanks pituach.  I think I'm fine now.  
  

  ---

  Knowledge is the only thing that I can give you, and still retain, and we are both better off for it.

  >> Thanks pituach.  I think I'm fine now.   

  You are most welcome :-)

  Please close the thread by marking the answer/s (as I understand Prashanth Jayaram's response lead you to the solution), and you can vote for useful response on the way :-)

  ---

  Ronen Ariely  [Personal Site]    [Blog]    [Facebook]

  Friday, December 12, 2014 4:23 PM